Creating an UPDATABLE linked table

[ZanyJanie]

Within Access, I'm using the code shown below to create a link to a SQL Server table. My code works; but I need to make the linked table updatable.

If I create the link manually, Access prompts me to choose the "Unique Record Identifier" field(s); but I can't figure out how to provide this information from within my code.

Can anyone out there help me with this problem?


Thanks in Advance,
Jane

-----

strConnect = "ODBC;DRIVER={SQL Server}" _
& ";SERVER=" & strServer _
& ";DATABASE=" & strDB _
& ";UID=" & strUID _
& ";PWD=" & strPwd & ";"

Set strTblDef = db.CreateTableDef(strTblName)

strTblDef.SourceTableName = strTblName

strTblDef.Connect = strConnect

db.TableDefs.Append strTblDef
db.TableDefs.Refresh

 

[SimonC]

I don't know the answer to your query but...

Access normally only prompts for this if the data source doesn't have a primary key defined. Is that the case here? If so, do you have any control over the way this table is set-up? Can you get a primary key index added to the table?

Simon

 

[ZanyJanie]

Thanks for your reply Simon. The SQL Server table has a primary key field; so that isn't the problem. My guess is that I need to pass a table definition or connection string parameter to tells Access which field(s) are the PK. I just don't know what that parameter is.

Still looking for the anwser!

Jane

 

[Pat Hartman]

If Access is prompting you for a unique identifier when you link the table manually, the table DOES NOT have a primary key. Access will ONLY allow updates to linked tables that have unique primary keys. You also of course need a userid/password with update permissions to ALL tables involved in any query.

It is possible for you to create a psuedo key for Access to use but this is dangerous and it still requires that the table at least contain a column or set of up to 10 columns that can be used to uniquely identify a row. Look up CREATE INDEX in help to see sample DDL that you can use in a pass-through query to create the psuedo index. Be WARNED though. If you create a psuedo index and it does not actually provide a unique identifier for a row, you will CORRUPT your SQL table.

 

[ZanyJanie]

Pat

Thanks for your help! You were right about my table not having a PK. It has an identity field that was originally defined as the PK. Recently, our DBA upgraded our SQL Server from version 7 to 2000. Somehow during the upgraed all of my database's PK's and indexes disappeared. After defining the PK again, my original code worked fine.

Thanks again!
Jane

 

[David R]

Don'tcha love upgrades? They're so helpful.

David R