Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Linking ODBCs with ADO

Status
Not open for further replies.

harvesterlily

Programmer
Jun 22, 2004
27
US
I posted this Access Tables and Relationships. Thought it might be better suited here. Sorry for the cross posting.

Working in Access 2000

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
'not renaming table as I am trying to link to target table
'rename it
'Tbl.Name = sTbl
'******
'Appears to work until here
'Then I get an error message 3265 - not in collection
'Unable to link table, so it's not in collection
'Do I need to Change the Jet OLEDB below? to what?

'Set the properties to create the link.
Tbl.Properties("Jet OLEDB: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 :)


 
I just read your post and having the same problem, I wondered if you resolved it? I also noticed that there may be an error in your posting - a bad reference to sConn, which refers to itself without a prior definition.

sConn = "File Name=H:\USERS\SHAREDDBs\UDLs\" & sConn

If you resolved the connection, would you kindly post the sample code?

Many thanks,

ineuw
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top