Comment by George Brown

August 23, 2007 @ 6:30 am

We have found at our organization, that it is much easier to support DSN-less linked tables in access that don’t prompt the user for a password. The upside is that you don’t have to copy the DSN to workstations where other employees need to use the database.

Below is the code we use in VBA to create the linked tables. Just thought I would share.

Public Sub CreateLinkedTable()

‘ Used to hold tabledef object and connection string
Dim td As TableDef
Dim ConnString As String

‘ Used in connection string
Dim LocalTableName As String
Dim SourceTableName As String
Dim SourceServerName As String
Dim SourceDatabaseName As String
Dim Login As String
Dim SourcePassword As String

‘ Sets all variable values for connection string
LocalTableName = “MySQLTableLinked”
SourceTableName = “MySQLTable”
SourceServerName = “SQLServerName”
SourceDatabaseName = “SQLServerDBName”
Login = “Login”
SourcePassword = “Password”

‘ Creates connection string for the linked table
ConnString = “ODBC;DRIVER=SQL Server;SERVER=” & SourceServerName & “;DATABASE=” & SourceDatabaseName & “;UID=” & Login & “;PWD=” & SourcePassword

‘ Sets the table definition object
Set td = CurrentDb.CreateTableDef(LocalTableName, dbAttachSavePWD, SourceTableName, ConnString)

‘ Creates the table by appending to the current list of table definitions.
CurrentDb.TableDefs.Append td

End Sub

Comment by Tom Miller

August 23, 2007 @ 9:17 am

You can also link SQL Tables into an Access database without a DSN by using a little bit of VBA code and DAO (yes, good old DAO):

Public Function AttachTables()
Dim tdTemp As TableDef

End Function


Comment by Tom Miller

August 23, 2007 @ 9:18 am

The code got wrapped in the posting above, but the tdTemp.Connect is all one line up through the closing quotes.

Comment by Bill

November 20, 2007 @ 3:04 pm

When I try to set up this code in MS Access, I get an error message stating that TableDef is not a defined type: —————————
Microsoft Visual Basic
—————————
Compile error:

User-defined type not defined
—————————
OK Help
—————————
We are using Access 2002. If it is in v 2.0, it should work here. What’s going on?

Comment by Ari Weil

December 2, 2007 @ 5:32 am

Bill, check this out, see if it helps you.

Comment by Derek

December 9, 2007 @ 4:43 pm

Bill:
I’m probably making a foolish assumption, but do you have the DAO 3.6 library included in your references?
Derek.

Comment by Tom

December 17, 2007 @ 10:18 am

I did not create a backend and bascically copied my db to a shared network since the users are only running reports. I have 4 tables that are linked to another DB! Does every user have to create a link to these other tables? I used the AttachTables function and seems to work fine but if I try to open the tables from another users PC, I get an ODBC connection error! So I’m assuming that all users will have to create the link! And my last question is - how do I check if the table name already exists?

Thanks,
Tom

Comment by Ari Weil

December 18, 2007 @ 5:09 am

If the users are using DSNs like Bryan recommends above, then each client PC will have to host the DSN.

Comment by Khuzaima

August 16, 2008 @ 11:53 pm

It worked excellently. I had 100s of tables to be linked and certain times only few of them were to be used.
I created an additional table containing the names and properties of the sql tables and used this table in a loop to connect and index them as and when required.

Comment by PistolPinoy

September 17, 2008 @ 3:25 am

i successfully linked the tables but it is read only. is there a way i can link it with write access?

Comment by Srinivas

October 27, 2008 @ 9:24 am

Thanks a lot dude…. its working..
Srinivas