How to get unique Record Identifiers working OK when linking Access to SQL Server

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Dave (strategicdelivery_at_ihug.co.nz)
Date: 04/17/04 

Date: Sun, 18 Apr 2004 09:14:59 +1200

I wish to link an Access application to a SQLServer Database by VBA and have
managed to do this. The problem I have is that the linked tables are not
updateable. If I link to the same tables via the user interface (Tools>Link
Tables etc) they are.

I think the issue is that my code does not select a unique record identifier
for the table (which you are prompted to do in the user interface). Can I
do that in VBA.

Here is my code

Private Sub PopSqlServerTbls(strAccessTbl, strDBName, strTblName,
strDataSourceName, strUserID, strPword)

Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table

Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection

Set tbl = New ADOX.Table
tbl.Name = strAccessTbl
Set tbl.ParentCatalog = cat

tbl.Properties("Jet OLEDB:Create Link") = True
tbl.Properties("Jet OLEDB:Link Provider String") = "ODBC" & ";DATABASE=" &
strDBName & ";UID=" & strUserID & ";PWD=" & strPword & ";DSN=" &
strDataSourceName
tbl.Properties("Jet OLEDB:Remote Table Name") = strTblName

cat.Tables.Append tbl
End Sub

Relevant Pages

  • RE: Link to SQL Server via code
    ... > Dim cat As ADOX.Catalog ... > Dim tbl As ADOX.Table ... > End Sub ...
    (microsoft.public.access.formscoding)
  • Linking to Access table in read-only database using ADOX
    ... table into an Access database using ADOX. ... Dim cat As ADOX.Catalog ... Dim tbl As ADOX.Table ...
    (microsoft.public.data.ado)
  • Re: Link Oracle Tables to Access using VBA, ADO and ADOX
    ... ¤ Hi *, ... ¤ Dim cat As ADOX.Catalog ... Set tbl = New ADOX.Table ... Dim cat As New ADOX.Catalog ...
    (microsoft.public.vb.database.ado)
  • create ADOX table
    ... Dim tbl As New Table ... Dim IDx As New ADOX.Index ... Dim cat As New ADOX.Catalog ... ' Open the Catalog. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: User Defined Function
    ... regression coefficients (either in worksheet cells or in a VBA routine). ... You have to move the "Tbl" code to one line in vba. ... Tbl refers to a group of cells on a worksheet. ...
    (microsoft.public.excel.programming)