DataTrain
asked on
How can I use VBA to automatically link via ODBC to a Database and Table from Access 2007?
I am using Access 2007. I have created an ODBC connection. Using the External Data tab I can manually select my ODBC connection and link to a Table in an external database. Is it possible to do the same in VBA so that I can AUTOMATICALLY open the ODBC connection and AUTOMATICALLY link to the Table, or more than one Tables? If you can help with the code and briefly explain what the code does/how it works, that would be very helpful! Thanks. David.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You need to know what your ODBC coinnect string would be, once you know that then you can manipulate the Connect String of a TableDef or even create a new tabledef.
To find the ODNC connect string design the attached table and take a look at the DESCRIPTION property, the DESCRIPTION property will show you the CONNECTION and SOURCETABLENAME properties, the CONNECTION is everything beore ;Tablename=
Cheers, Andrew
To find the ODNC connect string design the attached table and take a look at the DESCRIPTION property, the DESCRIPTION property will show you the CONNECTION and SOURCETABLENAME properties, the CONNECTION is everything beore ;Tablename=
Cheers, Andrew
Private Sub cmdDAOConnectString_Click()
Dim db As DAO.Database
Dim td As DAO.TableDef
Set db = CurrentDb
' Change Existing Table
Set td = db.TableDefs("Example_AttachedTableName")
td.Connect = "ODBC;DSN=COMPAQ;Trusted_Connection=Yes;APP=2007 Microsoft Office system;DATABASE=EETest"
td.RefreshLink
' Create New Table
Set td = db.CreateTableDef("Example_AttachedTableName1")
td.Connect = "ODBC;DSN=COMPAQ;Trusted_Connection=Yes;APP=2007 Microsoft Office system;DATABASE=EETest"
td.SourceTableName = "tblB"
db.TableDefs.Append td
RefreshDatabaseWindow
End Sub
ASKER
Thanks for your quick response. My backend database is SQL Server 2005. I will need a little time to work out my ODBC connect string and try a few things out.
My example is using a DSN to SQL Server with Trusted connections.
Cheers, Andrew
Cheers, Andrew
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you - I have got this working now using DoCmd.TransferDatabase. I appreciate your help. David.
Here is an example, depending on your db, it may end up creating schema owners in the tablename.
I think this should do it though. Using ADOX, we make a connection to your ODBC then list tables then connect
Dim adoConn As adodb.Connection
Dim adoRecSet As adodb.Recordset
Dim sSql As String
Set adoConn = New adodb.Connection
adoConn.Open "ODBCDSNName", "USERID", "PASSWORD"
Set adoCat = CreateObject("ADOX.Catalog
Set adoCat.ActiveConnection = adoConn
For Each adoTbl In adoCat.Tables
DoCmd.TransferDatabase acLink, "ODBC Database", "CONNECTIONSTRING", acTable, adoTbl.Name, adoTbl.Name, False
Next
Set adoCat = Nothing
adoConn.Close