harvesterlily
Programmer
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
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