Primary Key Help

Hi am fairly new to VS and very new to Access. I am managing somewhat. I have access to the database, I get inside my tables and I can change what needs to be changed. However I want to know how to work with primary keys. As of right now I am using 4 tables in my app. If i run my program all of the tables get filled in just fine. However if I run it again, none of the tables get updated because I have a primary key on a few of the tables columns.

Dim ADOConn As ADODB.Connection

Dim recSet As ADODB.Recordset

ADOConn = New ADODB.Connection

recSet = New ADODB.Recordset

ADOConn.Provider = "Microsoft.Jet.OLEDB.4.0"

ADOConn.ConnectionString = "C:\Documents and Settings\christopher.cornell\Desktop\MyDB.mdb"

ADOConn.Open()

recSet.Open("select * from Table1", ADOConn, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic)

recSet.AddNew("Column1", textbox1.text) 'Primary key on this. When i try to add the same thing over

'no other tables will get updated.

recSet.Update()

recSet.Close()

The first time through will be just fine, however if I run it again it wont update the rest of my tables. It seems to run everything (stepping through the debugger) but it is not reflected in the tables. I hope what I am trying to do makes sense.

Hi Chris,

The issue here is that a primary key is what is referred to as a unique constraint. There can not be any duplicate values for the primary key column. The primary key is defined in this manner so that the database engine knows how to locate the record you are looking for. Think of this as the row name for a table. Very similar to an excel spreadsheet. If I were to give you a spreadsheet with no row numbers on it and asked you to find a specific row you would have to scan all the rows in the sheet to find what I am asking you for. If you have a primary key (usually a number) and I ask you for row #456 and it is garunteed unique you can find it and once you found it you can stop looking because you know there is no other row #456.

So, you need to make sure your values you insert for your primary key are unique. Often times you will need to define multiple columns as your primary key if a combination of values makes a row or record unique.

Hope this helps you.

-David Sandor

|||

OK, that makes sense. But for instance lets say that I only want to have this one specific entry in my table. I am parsing a file to get the info for my table. So if I come to this value again and try to insert it, it obviously will not be allowed. However after it trys that, nothing else will be inserted into any of the other tables. So for table1 I have 2 columns. The first column has a primary key. Then I have table 2 with 4 columns, I do not have a primary key because I do not mind that records are the same. But when I parse my file for insertion into the tables, it will try the first table and since the entry is already in there it will not put anything else in the second table. It seems to finish executing the code however. I was thinking I may have needed an if that checks if its alredy in there, but I could not get that to work either.

I am guess this problem is caused by the primary key thing, because when I take it off, everything works fine. Thank you very much for the reply.

Chris

I still need help with the above issue. However I think that If I get help with this problem I will be able to figure it out myself. I am trying to find something in the database. This is what I am doing.

recSet.Open("select * from Table", ADOConn, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic)

recSet.Find("[Name] = '" & temp(i) & "'", 0, ADODB.SearchDirectionEnum.adSearchForward, 1)

If Not recSet.Fields("Name").Value = temp(i) Then

recSet.AddNew("Name", Apps(i))

recSet.Update()

recSet.Close()

End If

This is how I have seen to do things on other sites however I can not get this to work. When I try to do the find, all I am getting is the very first entry so I assume I am doing something wrong. Any help would be greatly appreciated. Thanks.