Connect and relink the Access tables automatically to SQL Server

Question

I have an Access software as FE with SQL as BE. Every week I send an update file (Accde) to my users. The operators and users should call IT manager or assistant to open the updated file and relink the Access tables manually to SQL server. It is a difficult and frustrating job. How can I write a code to automatically relinks the table at program startup or by pressing a command button on splash screen?

Answer 1

Hi.

The way I do it is after I link the FE using the File DSN, I take the value from the Connect property of the linked table and assign it to a VBA Constant. In your case, since you work with multiple companies, you might use a table for it.

In your code, you can then look up the appropriate connection string from your table and then apply it to the Connect property of your linked tables.

Hope that helps...

Answer 2

Hi. There are lots of examples available for relinking back end tables to an Access front end. Try doing a search on the keywords "auto relink."

Here's one example for an Access BE. You should be able to modify it to work with SQL Server.

automatically-relink-microsoft-access-tables

Answer 3

Dear thedbguy,

Now, I create a DSN file for my clients (it is different in any company) and relink Access tables by referring to the DSN file in relink table manager. Now I want to automate this process. I want any user to be able to relink the new updated Access to the database in SQL server.
In the code in the article how can I specify the location of a SQL database in the server?
Can I use the DSN file that I create for linking clients to server?
I can put and save the connection path in a table in BE database (in any different company) and recall it in the code. But I don't know how and where I should use it.

Public Function RefreshTableLinks() As String
On Error GoTo ErrHandle

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim strCon As String
Dim strBackEnd As String
Dim strMsg As String
Dim intErrorCount As Integer

Set db = CurrentDb

‘Loop through the TableDefs Collection.
For Each tdf In db.TableDefs
‘Verify the table is a linked table.
If Left$(tdf.Connect, 10) = “;DATABASE=” Then
‘Get the existing Connection String.
strCon = Nz(tdf.Connect, “”)
‘Get the name of the back-end database using String Functions.
strBackEnd = Right$(strCon, (Len(strCon) – (InStrRev(strCon, “”) – 1)))
‘Verify we have a value for the back-end
If Len(strBackEnd & “”) > 0 Then
‘Set a reference to the TableDef Object.
Set tdf = db.TableDefs(tdf.Name)
‘Build the new Connection Property Value.
tdf.Connect = “;DATABASE=” & CurrentProject.Path & strBackEnd
‘Refresh the table link.
tdf.RefreshLink
Else
‘There was a problem getting the name of the back-end.
‘Add the information to the message to notify the user.
intErrorCount = intErrorCount + 1
strMsg = strMsg & “Error getting back-end database name.” & vbNewLine
strMsg = strMsg & “Table Name: ” & tdf.Name & vbNewLine
strMsg = strMsg & “Connect = ” & strCon & vbNewLine
End If
End If
Next tdf

ExitHere:
On Error Resume Next
If intErrorCount > 0 Then
strMsg = “There were errors refreshing the table links: ” _
& vbNewLine & strMsg & “In Procedure RefreshTableLinks”
RefreshTableLinks = strMsg
End If
Set tdf = Nothing
Set db = Nothing
Exit Function

ErrHandle:
intErrorCount = intErrorCount + 1
strMsg = strMsg & “Error ” & Err.Number & ” ” & Err.Description
strMsg = strMsg & vbNewLine & “Table Name: ” & tdf.Name & vbNewLine
strMsg = strMsg & “Connect = ” & strCon & vbNewLine
Resume ExitHere

End Function

Answer 4

Dear theDbguy,

What is the meaning and use of $ after LEFT and Right function in this code?
I think the code doesn't get the connection string correctly.

Answer 5

The Left() and Left$() functions do the same thing. The only difference is the Left() function returns a Variant String; whereas, the Left$() function returns a String value.