Linking tables with ADO

[harvesterlily]

First attempt at using ADO instead of DAO. Currently I use dsn-less connections to create table links in code. I'd like to replicate that with ADO if possible. The tables uses 3 different ODBC drivers.(Wavecrest CView, MySQL, and SQL Server. Wanting to use universal data links(UDL) to save time on dsn work. Not sure it's possible, but worth a try.

Here's my code:
Public Function LinkTables(sConn, sTblx, sTbl)
'sConn = udl name
'sTblx = name of remote table
'sTbl = name table s/b in current db

Dim cn As ADODB.Connection
Dim Cat As ADOX.Catalog
Dim Tbl As ADOX.Table

Set cn = CreateObject("ADODB.Connection")
Set Cat = New Catalog
sConn = "File Name=H:\USERS\SHAREDDBs\UDLs\" & sConn


With cn
.Errors.Clear
.ConnectionTimeout = 60
.ConnectionString = sConn
.Open
End With

Cat.ActiveConnection = cn

'get table reference
Set Tbl = New ADOX.Table
Tbl.Name = sTbl

Set Tbl.ParentCatalog = Cat
'rename it
'Tbl.Name = sTbl
'******
'Appears to work until here
'Then I get an error message 3265 - not in collection

'Set the properties to create the link.
Tbl.Properties("JetOLEDB:Link Datasource") = sConn
Tbl.Properties("Jet OLEDB:Remote Table Name") = sTblx
Tbl.Properties("Jet OLEDB:Create Link") = True

' Append the table to the Tables collection.
Cat.Tables.Append Tbl

'cleanup
Set Tbl = Nothing
Set Cat = Nothing
cn.Close
Set cn = Nothing

End Function

I am using the Microsoft ADO Ext 2.8 reference.
I have tested cn and it returns this:
Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="DSN=MyDB;DBQ=Srvr2;CODEPAGE=1252;"

I want these tables to link and be visually represented as they are when you go thru the linked table manager. Can this be done? If so, am I headed in the right direction? Am under a bit of pressure to get this portion completed rather quickly. Any help would be greatly appreciated.

Thanks

 

[cmmrfrds]

I have used this method to link sql server tables. In this example I have the pubs database installed on a local copy of sql server.

Public Function SQLServerLinkedTable()
Dim oCat As ADOX.Catalog
Dim oTable As ADOX.Table
Dim sConnString As String

' Set SQL Server connection string used in linked table.
sConnString = "ODBC;" & _
"Driver={SQL Server};" & _
"Server={localhost};" & _
"Database=pubs;" & _
"Uid=sa;" & _
"Pwd=;"

' Create and open an ADOX connection to Access database
Set oCat = New ADOX.Catalog
oCat.ActiveConnection = CurrentProject.Connection

' Create a new Table object
Set oTable = New ADOX.Table

With oTable
.Name = "authors"
Set .ParentCatalog = oCat
.Properties("Jet OLEDB:Create Link") = True
.Properties("Jet OLEDB:Remote Table Name") = "authors"
.Properties("Jet OLEDB:Link Provider String") = sConnString
End With

' Add Table object to database
oCat.Tables.Append oTable
oCat.Tables.Refresh
Set oCat = Nothing

End Function

 

[harvesterlily]

Thanks cmmrfrds. I have seen this solution all over the place. If I am not going to be able use the UDL which has the connection string, I will just use the DSN-less DAO function that's already working.

One thing that I failed to mention is that I have opened a recordset on a form that is DAO and that information is passed to the ADO - LinkTables function. I read that using both is okay as long as the DAO reference is higher in the list. Further reading mentioned that UDLs behave funny on heirarchical type commands. (Seem to connect, but unable to retrieve info.) Not sure if this is one of those scenarios. It requires opening two connections. I'm going to give that a try and see if that works. Will post here if it works.

Thanks again.

 

[harvesterlily]

No luck so far. I will keep trying.

 

[harvesterlily]

A little further along, but now I've got a different error message.

Public Function LinkTables(sClt, sConn, sTblx, sTbl)

Dim cn As ADODB.Connection
Dim Cat As ADOX.Catalog
Dim Tbl As ADOX.Table

'create new connection
Set cn = CreateObject("ADODB.Connection")
sConn = "File Name=F:\USERDATABASES\INVOICING\UDLs\" & sConn

With cn
.Errors.Clear
.ConnectionTimeout = 60
.ConnectionString = sConn
.Open
End With

' Open a Catalog on the database in which to create the link.
Set Cat = New Catalog

Cat.ActiveConnection = CurrentProject.Connection '(I want to link the remote table to this db)


'get table reference
Set Tbl = New ADOX.Table
Tbl.Name = sTbl

Set Tbl.ParentCatalog = Cat

'rename it
'Tbl.Name = sTbl



'Set the properties to create the link.
Tbl.Properties("Jet OLEDB:Create Link") = True
Tbl.Properties("Jet OLEDB:Link Datasource") = cn
Tbl.Properties("Jet OLEDB:Remote Table Name") = sTblx

*********

works up til here and then i get this error message:
2147467259 - 80004005 Not a valid file name.

' Append the table to the Tables collection.
Cat.Tables.Append Tbl


'cleanup
Set Tbl = Nothing
Set Cat = Nothing
cn.Close
Set cn = Nothing

End Function

I'm doing searches on th error message.
ADO experts, I need your help.

 

[harvesterlily]

Got this working well sort of. I was able to link tables with ADO. I ended up parsing the UDL which contained the connection string. The connection string I have access to, details the dsn and such. Basically I ended up creating dsn-less connections with ado. No ado connection is really needed to do that, making the udl unnecessary. I guess for the udl to work without having dsn's on each machine you need the root path, which I don't have. Not a completely fruitless venture.