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.