Linking tables in SQL via DAO

[adefries]

I am trying to come up with a "hands free" and "DSN-less" way for MS Access 2010 to dynamically link to six tables on SQL server 2010. I found the below code on WiseOwl.co.uk(Posted by David Wakefield on 26 June 2012) but I'm embarrassed to say I don't know where to put this code. Does it go inside a module as a function and then get called in a macro as "runcode"? If it gets called as a function, how do I reference the function in the RunCode? I need some help with the "missing" pieces I need to make this work, as I am not very familiar with VBA or modules, but I think VBA is the only way I can get this dynamic linking to work without a DSN. Also, I don't know how to get this code to dynamically loop through a list of tables and keys, to automatically link all six tables without user intervention.

David's post says...

Using DAO

The following code is slightly more long winded, but crucially it allows you to set the key field(s) in code:

' we will need to create this table using DAO
Dim tdf As DAO.TableDef
 
' Some variable to make the code more generic
Dim strConnectionString As String
Dim strNameInAccess As String
Dim strNameInSQLServer As String
Dim strKey As String
 
 
' set the connection string
strConnectionString = _
   "ODBC;DRIVER=SQL Server; " & _
   "SERVER=.\SQLExpress;DATABASE=MyDatabase;Trusted_Connection=Yes"
 
 
' specify the tables you want to link. The table can be
' known by a different name in Access than the name in SQL server
 
strNameInAccess = "tblYacht"
strNameInSQLServer = "tblSailingBoat"
 
' specify the key field
strKey = "SailingBoatID"
 
' Delete the table from the local database if it exists
On Error Resume Next
 
DoCmd.RunSQL "drop table " & strNameInAccess
 
On Error GoTo 0
 
' Create a table using DAO give it a name in Access.
' Connect it to the SQL Server database.
' Say which table it links to in SQL Server.
Set tdf = CurrentDb.CreateTableDef(strNameInAccess)
 
tdf.Connect = strConnectionString
tdf.SourceTableName = strNameInSQLServer
 
' Add this table Definition to the collection
' of Access tables
CurrentDb.TableDefs.Append tdf
 
' Now create a unique key for this table by
' running this SQL
 
On Error Resume Next
 
DoCmd.RunSQL _
   "CREATE UNIQUE INDEX UniqueIndex ON " _
   & strNameInAccess & " (" & strKey & ")"
 
On Error GoTo 0

[zmbd]

This is a more robust explanation of the method...

Using DSN-Less Connections (by Doug Steele (Access MVP))

You really MUST have a firm Coding background before you get into the deep-end with this integration. There are still a lot things that I run into with between the servers and my frontend that without a good core-understanding I'd be lost.