DSN-less Connection MS Access front end and SQL server backend

Question

I don't normally program in MS Access VBA so forgive my question if it's stupid.

So I'm using MS Access 2010 as a front end and SQL Server 2014 as a backend. (I don't have a choice in frontend interface so please no suggestions on alternate options).

I'd like to programatically link SQL server's backend to my MS Access frontend. I read here at DJ Steele's DSN-less connection page that I can use the code he provided here to make a DSN-less connection to SQL server as a backend.

So I copied that into a VBA Access module and opened another module and ran this code to run the DJ Steele code in an attempt to connect to a small SQL Server database I made:

Option Compare Database
Sub runThis()
FixConnections "AServerNameHere", "MS_Access_BackEnd_Test"
End Sub

As far as I can tell from the VBA debugger it gets to

Set dbCurrent = DBEngine.Workspaces(0).Databases(0)

then that value seems to be empty. I'm not sure how else to proceed with this since as far as I was able to find this is one of the few full examples of a DSN-less connection I could find.

I'd like to not use the DSN method of linking a SQL server to a database since that would require me to go visit people and their computers in order to make the links. (And who'd want to to that? LOL)

I've also looked at similar questions that were linked to me while writing this question and this was close to what I wanted, but it kept giving me "Compile error: Constant expression required" for input of:

LinkTable "MS_Access_BackEnd_Test", "Table_1"

and

LinkTable "MS_Access_BackEnd_Test", "Table_1", , "AServerNameHere"

Again I'm not familiar with MS Access VBA so forgive the question if it's lame.

I replaced Set dbCurrent = DBEngine.Workspaces(0).Databases(0) with Set dbCurrent = CurrentDb(), but I got "Run-time error '13': Type mismatch' when I stepped through the code and did ?dbCurrent and ? CurrentDb in the Immediate window. I also took out the parenthesis since https://msdn.microsoft.com/en-us/library/office/aa221178(v=office.11).aspx has examples without them. — user3003304, Feb 18 '15 at 15:54
Though also when I read that page it mentioned that this is for the currently open database in a microsoft access window. I'm attempting to make a DSN-less connection and link a MS Access front end with SQL server backend. So I won't have a currently opened database in MS Access... — user3003304, Feb 18 '15 at 15:55

Ben McIntyre · Answer 1 · 2015-02-25T00:04:54.057

Looking at DJ Steele's code, I got it working apart from the line

' Unfortunately, I'm current unable to test this code,
' but I've been told trying this line of code is failing for most people...
' If it doesn't work for you, just leave it out.
tdfCurrent.Attributes = typNewTables(intLoop).Attributes

which I had to comment out.

Using Set dbCurrent = CurrentDb() does essentially the same thing as Set dbCurrent = DBEngine.Workspaces(0).Databases(0) but the latter is meant to be a lot faster ... which these days means it takes 10 microseconds instead of 100 :-o

You still need dbCurrent as a reference to the current Access front end which is where the linked table objects live, even if the data is coming from elsewhere.

Edit: working for me

I added a debug.print line to monitor what's going on

' Build a list of all of the connected TableDefs and
' the tables to which they're connected.

For Each tdfCurrent In dbCurrent.TableDefs
    Debug.Print tdfCurrent.Name, tdfCurrent.Connect
    If Len(tdfCurrent.Connect) > 0 Then
      If UCase$(Left$(tdfCurrent.Connect, 5)) = "ODBC;" Then
          ...

and then

? currentdb().TableDefs("dbo_Person").Connect
ODBC;DSN=TacsData;APP=Microsoft Office 2003;WSID=TESTXP;DATABASE=TacsData;Trusted_Connection=Yes;QuotedId=No

FixConnections "TESTXP\SQLEXPRESS", "TacsData"

MSysAccessObjects           
MSysAccessXML 
...     
MSysRelationships           
Table1        
dbo_Person    ODBC;DSN=TacsData;APP=Microsoft Office 2003;WSID=TESTXP;DATABASE=TacsData;Trusted_Connection=Yes;QuotedId=No

? currentdb().TableDefs("dbo_Person").Connect
ODBC;DRIVER=sql server;SERVER=TESTXP\SQLEXPRESS;APP=Microsoft Office 2003;WSID=TESTXP;DATABASE=TacsData;Trusted_Connection=Yes

I did see that comment and I've already attempted to comment it out. When I go to run it with F5 I get no error messages or anything, but it doesn't make any table links. When I used F8 to step through it I did get "Run-time error '13': Type mismatch" when I entered ?dbCurrent on the Immediate window. (For both DBEngine.Workspaces(0).Databases(0) and CurrentDb(). — user3003304, Feb 23 '15 at 15:56
That error is because you can't print the database object. Try ```?dbCurrent.Name``` — Ben McIntyre, Feb 24 '15 at 23:29

DSN-less Connection MS Access front end and SQL server backend

1 Answers1