Connection string for opening linked table Access -> SQL server (vba)

Question

I've been looking for the answer as there are many similar questions, but haven´t found this particular case.

I have an SQL server, and an Access File (front-end). This Access file has linked tables, linked with the SQL server, and every time I try to open one table, a prompt appears asking for user and password.

OK, this is perfect, as it is (almost) the behaviour I want.

Also, there's a local table, that contains User/Pass with passwords encrypted. Every time the user opens the file, his/her pass is decrypted and I would like to open a connection, so linked tables won´t ask for pass.

Does anybody know how to code this connection?

Thanks in advance!

Solution

I think what you want is a "DSN-LESS connection". I've done something similar in the past. Basically, at user login you re-link the tables using a connection string that you build based on the user's credentials.

Take a look at this prior SO Question for more details.

Also, here are a few routines I've used in the past that you are free to use \ modify. Call these from your login process.

Public Function RelinkDSNTables( _
      ByVal ServerName As String, _
      ByVal DatabaseName As String, _
      ByVal UserName As String, _
      ByVal Password As String) As Boolean
On Error GoTo Err_Handler

Dim dsn As String
dsn = GetDSNLink(ServerName, DatabaseName, UserName, Password)
Dim td As TableDef
Dim db As DAO.Database
Set db = CurrentDb
Dim rst As ADODB.Recordset

'Get a list of tables that need to be relinked'
If GetLocalRecordSet(rst, "SELECT * FROM TableMapping") < 1 Then
   Err.Raise 1000, "Missing Tables!", "Missing Table Mappings!"
End If

Dim fNeedToRefresh As Boolean

'See if we actually need to relink the tables'
For Each td In db.TableDefs
   If td.Connect <> vbNullString Then
      If td.Connect <> dsn Then
         fNeedToRefresh = True
         Exit For
      End If
   End If
Next td

If fNeedToRefresh = False Then
   RelinkDSNTables = True
   GoTo Err_Handler
End If

'Drop linked table in Access'
For Each td In CurrentDb.TableDefs
   If td.Connect <> vbNullString Then
      CurrentDb.TableDefs.Delete td.Name
   End If
Next td

'Create new linked table using new DSN'
rst.MoveFirst
Do Until rst.EOF
   Set td = db.CreateTableDef(rst!LocalTableName, dbAttachSavePWD, rst!RemoteTableName, dsn)
   db.TableDefs.Append td
   rst.MoveNext
Loop

'Because I am paranoid, refresh the link'
db.TableDefs.Refresh
For Each td In db.TableDefs
   If td.Connect <> "" Then td.RefreshLink
Next td

RelinkDSNTables = True

Err_Handler:
   If Err.Number = 3011 Then
      'Happens if user does not have permission in SQL Server; Nothing to see here, move along'
      Err.Clear
      Resume Next
   ElseIf Err.Number = 3010 Then
      'already exists; should not occur, but if it does eat the exception and move on'
      Err.Clear
      Resume Next
   ElseIf Err.Number <> 0 Then
      Err.Raise Err.Number, Err.Source, Err.Description
   End If
End Function

Private Function GetDSNLink( _
      ByVal ServerName As String, _
      ByVal DatabaseName As String, _
      ByVal UserName As String, _
      ByVal Password As String) As String
On Error GoTo Err_Handler

If ServerName = "" Or DatabaseName = "" Then
   Err.Raise -1220, "Missing Server \ DB", _
      "Unable to refresh table links because you are missing the server or database name!"
End If

Dim dsnLink As String

If UserName = "" Then
   'trusted connection'
   dsnLink = "ODBC;DRIVER=SQL Server;SERVER=" & ServerName & _
            ";DATABASE=" & DatabaseName & ";Trusted_Connection=Yes"
Else
   'MixedMode connection'
     '//WARNING: This will save the username and the password with the linked table information.
   dsnLink = "ODBC;DRIVER=SQL Server;SERVER=" & ServerName & _
            ";DATABASE=" & DatabaseName & ";UID=" & UserName & ";PWD=" & Password
End If

GetDSNLink = dsnLink

Err_Handler:
   If Err.Number <> 0 Then
      Err.Raise Err.Number, Err.Source, Err.Description
   End If
End Function

Edit: Added sample GetLocalRecordSet Function

Public Function GetLocalRecordSet(ByRef rs As ADODB.Recordset, ByVal sql As String) As Long
On Error GoTo Err_Handler

If sql = vbNullString Then
   Err.Raise vbObjectError + 1001, _
      "Empty SQL String", "Empty SQL String Passed to GetLocalRecordset Function!"
End If

Set rs = New ADODB.Recordset

rs.Open sql, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

If rs Is Nothing Then
   GetLocalRecordSet = -1
   GoTo Err_Handler
End If

If rs.State = adStateOpen Then
   If Not rs.EOF Then
      rs.MoveLast
      GetLocalRecordSet = rs.RecordCount                 'store number of records
      rs.MoveFirst
   Else
      GetLocalRecordSet = 0
   End If
Else
   GetLocalRecordSet = -2
End If

Err_Handler:
   If Err.Number <> 0 Then
      Err.Raise Err.Number, Err.Source, Err.Description
   End If
End Function

Comments

[HK1]

You posted your answer while I was busy typing mine. Your answer looks good, maybe even better than mine. One of the only differences I see is that I do have code in mine to create an index called "__UniqueIndex" for each linked table. I can't remember why I did this - maybe it's not needed for SQL Server ODBC linked tables.

[Tim Lentine]

@HK1 - Great point about the unique index. I skipped this as my tables in SQL Server always have a unique index. However, if the table does not have a unique index in SQL Server you won't be able to perform an update in MS Access against the table WITHOUT first creating the index within Access. Feel free to update my answer if you like : )

[HK1]

I'd be happy to update your answer with some free code but it requires you to store the primary key field(s) name(s) in order to create the unique index. So you'd need another field in your tablemapping table.

[Pete]

So what you are doing is relinking all the tables each time, right? As the linked tables include also User and Pass you have to delete and create them again. I would like to just establish a connection at the opening, but I will try this solution. GetLocalRecordSet is a function that you have defined?

[Tim Lentine]

@Pete GetLocalRecordset is just a wrapper function I use so I don't have the same code to open a recordset littered throughout the program.

[Tim Lentine]

@Pete - You can invoke this (or a similar) routine when the user logs in and only call it once - in fact, that would be preferable.

[HK1]

@Pete - I use a function much like Tim's and I just relink all the tables when the database opens. Those connections seem to stay in place without a problem, until the database closes. You shouldn't have to relink or refresh the tables while the database is open unless you are making lots of table changes that need to take affect immediately.

[Pete]

Thanks Tim and HK1! Silly question, could you show me how are you coding "getLocalRecordset"? Because I'm trying with something like: Dim sqlStr As String sqlStr = "SELECT * FROM TableMapping" rst.Open sqlStr, dsn and it throws me an error 91: "Object variable or With block not established".

[Pete]

Maybe the problem is I can´t find "TableMapping"... is it created automatically once a table is linked?

[Tim Lentine]

@Pete Sorry, should have been more clear. I created a table called "TableMapping" and placed the names of the linked tables into the table. GetLocalRecordset is just a function that returns a recordset (I pass the recordset variable byRef into the function along with the SQL statement to execute). The function itself returns the count of the records returned by the statement.

[Pete]

@Tim Ahh, ok, I have defined a local table at my Access FE called TableMapping that contains one entry for each linked table, with linked's table name. Could you show me the way you coded getLocalRecordset? I can´t get it to work properly :s Thanks for your hints!

[Pete]

Well, this is what I'm trying: Private Function getLocalRecordset( _ ByRef RecordS As Recordset, _ ByVal CadenaQuery As String) As Integer '''''''''' Set RecordS = CurrentDb.OpenRecordset("TableMapping") Dim i As Integer i = 0 While Not RecordS.EOF RecordS.MoveNext i = i + 1 Wend getLocalRecordset = i Debug.Print getLocalRecordset '''''''''' Debug.Print "Fin getLocalRecordset" End Function But it throws an error "Type mismatches"

[Tim Lentine]

@Pete Not sure about the type mismatch error, but I edited my answer to include a sample GetLocalRecordset function.

[Pete]

Thanks Tim! I have used the function you included at it works; just changed TableMapping to have both columns: LocalTableName and RemoteTablename, and tables are created correctly every time database is opened. <br/> One last question: tables are created, but can´t be modified (although if I create the tables manually using "Access-> External data -> ODBC db -> Create linked table" I can add new information), is it something that you didn´t use? I mean, you only read and didn´t write, or it should work also writing?

[Pete]

My bad, I was working with some tables that I created quickly for testing purpose and didn´t define a primary key. Now it works perfect!

[Pete]

Thanks for your help Tim! I was wondering now, do you know how to change table's permissions after a table is created? I would like to give everybody "delete" permission on these tables, so if an user opens the DB, and leaves some tables linked; if next time other user logs in, tables can be deleted and relinked.