How to create a DNS less linked server in Access to SQL Server.
Sometimes you may want to use Access as a front end for SQL Server.
When doing this you typically have linked tables to the SQL Server and you have the .mdb on a shared drive for users to access.
Normally you create a DSN and use that for the linked server. All works well when you are querying the linked tables from the machine that hosts the .mdb.
However, when a user opens the .mdb from the remote share, querying the linked tables fails with:
"ODBC--connection to '<The DSN name>' failed."
Basically this means that the client, i.e. the machine that you open the .mdb file on, not the machine that hosts the .mdb, does not have the ODBC DSN in question.
This is no problem; just create an ODBC DSN with the appropriate name that points to the server on that client machine and you should be good to go.
However, if you have many clients, then maintaining the OBDC DSNs could be a bit tiresome. I.e. if you change the database, then you need to update all clients DSNs.
There is one way to get around this, there may be more ways than this, but I’ve found this one to work.
In short, you create a DSN less ODBC connection via a macro. This way the information will be in the .mdb, and when you change the database, you update
the connection string and rerun the script. Now all clients will ‘automagically’ work.
Example setup:
1. SQL Server Machine
2. Access front end machine (ie. host the .mdb)
3. Access client machine (ie. opens the .mdb from machine 2)
On machine 2, create an ODBC DSN (I've used sqlsrv32.dll) called “ToNorthwind”, pointing to the Northwind database on the SQL Server on Machine 1.
On Machine 2, create two Access databases; WithDSN.mdb and WithoutDSN.mdb
In WithDSN.mdb;
File -> Get External Data -> Link Tables...
Select "ODBC Databases", select "Machine Data Source", select "ToNorthwind" and select (for example) the Employees table.
Double click the Employees table to verify that it works.
- In WithoutDSN.mdb;
Tools -> Macro -> Visual Basic Editor (or simply ALT+F11). This should open the editor.
Tools -> References.
Uncheck any "Microsoft ActiveX Data Objects 2.x Library" unless it is "Microsoft ActiveX Data Objects 2.8 Library"
Select "Microsoft ActiveX Data Objects 2.8 Library" and "Microsoft ADO Ext. 2.8 for DDL and Security"
*** or if on Windows 2008/Vista ***
Select "Microsoft ActiveX Data Objects 6.0 Library" and "Microsoft ADO Ext. 6.0 for DDL and Security"
Tools -> Macros..., enter a name for the macro, for example “DnsLessLinkTable”, and Create.
Enter the following code:
---------------------------------------------------------------------------------------------------------------------------------
Sub DnsLessLinkTable()
Dim con As ADODB.Connection
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Set con = New ADODB.Connection
Set cat = New ADOX.Catalog
Set tbl = New ADOX.Table
cat.ActiveConnection = CurrentProject.Connection
' Create the new (linked) table. Give it an appropriate name,
tbl.Name = "NoDnsEmployees"
Set tbl.ParentCatalog = cat
' Set the properties to create the linked table, Make sure you have a working connection string
tbl.Properties("Jet OLEDB:Link Provider String") = "ODBC;Driver={SQL Server};Server=servername\instancename;Database=Northwind;Uid=user;Pwd=password;"
' Select table in remote database, in this example, Employees table in Northwind Database
tbl.Properties("Jet OLEDB:Remote Table Name") = "Employees"
tbl.Properties("Jet OLEDB:Create Link") = True
' Append the table to the Tables collection.
cat.Tables.Append tbl
Set cat = Nothing
End Sub
---------------------------------------------------------------------------------------------------------------------------------
And Run it (F5)
Now you should have a table in the table list called "NoDnsEmployees". You may have to refresh list to see it.
Double click the “NoDnsEmployees” table to verify that it works.
On Machine 3,
Open the remote database called "WithDSN.mdb". You should see the “Employees” table, double click it. This should give:
"ODBC--connection to 'ToNorthwind' failed."
Open the remote database called "WithoutDSN.mdb". You should see the “NoDnsEmployees” table, double click it.
This should give your employees rows without having a 'ToNorthwind' DSN on Machine 3.
If adding the 'ToNorthwind' DNS to Machine 3, both Access database linked tables work.
Comments
Anonymous
November 23, 2009
Hello I really have to say thank you on this one, i have been trying to figure it out this for some time now and this post has been very handy for me. You really have saved lots of time ! Thanks a lot ! RalphAnonymous
November 23, 2009
Very handy. Thanks for posting. Lee EverestAnonymous
December 03, 2009
Thank you very much, Maspeng! I spent a lot of time googling for this. Found several links for older versions, all of which gave some errors with Access 2007 & SQL2005. Your solution worked like a charm! SebastianAnonymous
November 14, 2011
Than you Thank you thank you. I've been searching for 2 days to find an example I can follow. all the others I found were so complex. this is perfectAnonymous
January 21, 2013
I haven't used Access since 2003, but why wouldn't a File datasource on a drive w/ UNC mapping work?Anonymous
November 30, 2014
I am getting a Compile error: Method or data member not found. Please helpAnonymous
February 03, 2015
Created the link but it would not allow me to append a record to the link fileAnonymous
November 05, 2015
Link Table created, but I can't write to SQL Server with it as I can with different link table using ODBC dsn. Any ideas?