Can't create table links with ADO against SQL SERVER 1

[jearlcalkins]

Question ... Can ADO, under ACCESS VBA create a TABLE (linked) against a SQL SERVER DB?

I am unable to create a new ODBC link table under ACCESS 2000, using ADO objects connecting to a SQL SERVER 7 database. The application will not let me set properties on a new table. The source of the code below is a Microsoft KB "HOWTO: Link and Refresh Linked Jet Tables Using ADOX(Q230588)". When the application attempts to set properties for the table link, the following error occurs: Run-time error '3265': Item cannot be found in the collection corresponding to the requested name or ordinal.
..........................

Option Compare Database

Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim cnn As ADODB.Connection

Public Function Add_link_table()

result = CreateLinkedTable("dbo_Product&quot

End Sub

Public Function CreateLinkedTable(strLink As String)

Set cat = New ADOX.Catalog
Set tbl = New ADOX.Table
Set cnn = New ADODB.Connection

cnn.Open "PROVIDER=MSDASQL;DSN=DataSql;DATABASE=RepSql;User ID=sa;Password=;Trusted_Connection=yes;"
cat.ActiveConnection = cnn

tbl.Name = strLink ' Create the new table
Set tbl.ParentCatalog = cat

' Set the properties to create the link ... ERROR occurs next line
tbl.Properties("Jet OLEDB:Link Datasource&quot = strTransport
tbl.Properties("Jet OLEDB:Remote Table Name&quot = strLink
tbl.Properties("Jet OLEDB:Create Link&quot = True

' Append the table to the Tables collection
cat.Tables.Append tbl
Set cat = Nothing

cnn.Close
End Function

 

[cmmrfrds]

I ran this function and it linked the 'titles' table from the pubs database into my access mdb. I needed to add the ODBC sourec ADOPubs. I am using sql server 2000. Maybe try this simple example and expand from it.

Function ADOCreateAttachedODBCTable()
Dim cat As New ADOX.Catalog
Dim tbl As New ADOX.Table

' Open the catalog
cat.ActiveConnection = CurrentProject.Connection

' Set the name and target catalog for the table
tbl.Name = "Titles"
Set tbl.ParentCatalog = cat

' Set the properties to create the link
tbl.Properties("Jet OLEDB:Create Link&quot = True
tbl.Properties("Jet OLEDB:Link Provider String&quot = _
"ODBC;DSN=ADOPubs;UID=sa;PWD=;"
tbl.Properties("Jet OLEDB:Remote Table Name&quot = "titles"

' Append the table to the collection
cat.Tables.Append tbl

Set cat = Nothing

End Function

Microsoft link

http://msdn.microsoft.com/library/d...ry/en-us/dndao/html/daotoadoupdate_topic6.asp

 

[jearlcalkins]

Your addition of a "Link Provider String" property on the table was successful in getting the application to run. THANKS!!!

 

[jearlcalkins]

"This Recordset is not updateable" is the error I get when opening any form after linking my ODBC tables programmatically (see code below). Manually creating the odbc links in ACCESS by using: File-Get External Data-LinkTable, all the forms function well.
I was puzzled because I was unable to isolate one form to it's RECORDSOURCE and manually link only those tables. To get any form to run, I have to delete and manually link all tables.

Function ADOCreateAttachedODBCTable(strLink As String) ' this is from cmmrfrds ... tek-tips

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

On Error Resume Next ' if table exists ... just continue on

cat.ActiveConnection = CurrentProject.Connection ' Open the catalog

tbl.Name = "dbo_" & strLink ' Set the name
Set tbl.ParentCatalog = cat ' and target catalog for the table

tbl.Properties("Jet OLEDB:Create Link&quot = True ' Set the properties to create the link
tbl.Properties("Jet OLEDB:Link Provider String&quot = "ODBC;DSN=AeroDataSql;UID=sa;PWD=;"
tbl.Properties("Jet OLEDB:Remote Table Name&quot = strLink

cat.Tables.Append tbl ' Append the table to the collection

Set cat = Nothing

End Function

 

[cmmrfrds]

Check this link.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q252908

It shows how to add a primary key to the table, which is what is probably missing doing the relink through code since no primary key will cause the recordset to be nonupdateable.

 

[jearlcalkins]

This one is kind of weird ... I've inherited an OK db design with about 45 tables, all indexed, and about 30 forms sourcing the various tables and joins.

I can manually create odbc table links and all forms works.

I can focus on one form and manually create the source links for the one form and Access does NOT allow the form to update.

The only thing that comes to mind regarding your last note was; Access will complain about some of my SQL Server views as I manually create an odbc table link for the view. All the SQL Server tables are indexed. I would hope the view would use it's source table's index.

 

[cmmrfrds]

The error nonupdateable will happen if Access does not have or see the primary key (not index) on the linked table. When you relink try setting the primary key on the linked table.