[Troubleshooting] MS Access /w linked sql table on network

Cadius

New member
Local time
Today, 08:24
Joined
Jun 16, 2014
Messages
2
In short, I have a MS Access DB using a single linked table from a SQL DB on our work server. I'm having trouble setting up the DNS correctly... I can open Access and view the table fine, however others on the network get some type of -obdc connection error. Both the SQL data table and the Access DB are on the network.

When I linked the table, I used External data -> OBDC DB -> link -> System Data Source.... perhaps this is why? Not sure how to do this properly.
 
You need to setup the exact same "System data source" on each seperate computer for it to work.

Potentially if you have a user/pw for the sql server you need to enter that as well.
 
Yes, Namliam is correct. Each workstation must have the ODBC set up the same to work. Ideally, it has to be the same version of ODBC. This is usually why the Network support people hate Access.
So, do you wear the Network hat, the Workstation hat or is it some other group's responsibility? That can determine the best way to approach the solution.

The solution I use (therefore recommend) is to download the free ODBC driver called SQL Server Native Client 11.0. It doesn't come with Access or a Windows. It is also one of the recommendations for Microsoft Azure (Web SQL Server).
Then use a DSN-Less connection using VBA code.

Then, as a user starts up a session, create the link or refresh tables.
My application has a DB (production) and DBT (test).

Here is an idea of some of the code that would run assuming the user's workstation had SQL Server Native Client 11.0 installed on it.
Code (not shown) looks at a local Access table with the names of each table to link.
Code:
Function ModifiedRefreshDNSLess2(TableName As String) As String ' temp test database
    Dim ConnectString As String
    Dim strConnectionString As String
    Dim scn As String
    Dim sLocalName As String
    Dim UID As String
    Dim pwd As String
    Dim ServerString As String
    ' "SERVER=MyServerl\MyInstance;DATABASE=" & DataBaseName & ";" & _
    ' 'DBMSSOCN  - showed up in SQL Server connect
        ServerString = "MyServerName\MyInstanceName"
    UID = "MyUserIDforallUsers"     ' temp SQL name change later  ' Same for DBT
    pwd = "MyPassword"
    sLocalName = TableName  ' "Const_Seed" ' <<<---manually add a table name here for one single table to show how a table can be added
    Dim DatabaseName As String
 
    If fTest = False Then
        DatabaseName = "DB"
    Else
        DatabaseName = "DBT"    ' <<<<<<<<<<------------  DBT or DB  User 
' starts up in production (DB) or test (DBT) your database name here
    End If
    ' example - DB with SQL Server Security UN and PW 
    strConnectionString = "ODBC;DRIVER=SQL Server Native Client 11.0;" & _
        "SERVER=MyServer\MyInstance;DATABASE=" & DatabaseName & ";" & _
        "UID=" & UID & ";" & _
        "PWD=" & pwd & ";" & _
        "Table=DBO." & sLocalName & ";Option=3;"
    strConnectionString = "ODBC;DRIVER=SQL Server Native Client 11.0;" & _
        "SERVER=MyServerl\MyInstance;DATABASE=" & DatabaseName & ";" & _
        "UID=" & UID & ";" & _
        "PWD=" & pwd & ";" & _
        "Table=DBO." & sLocalName & ";Option=3;"
 
    ModifiedRefreshDNSLess2 = strConnectionString
    'Debug.Print strConnectionString
End Function

You will find several post related to this on this site and on other sites.
This way, the odbc settings on each workstation can be avoided. Install SQL Server Native Client 11.0 once and then let code do the work.
 
Last edited:
Thank you very much for your responses, my apologies for the late reply but it has been a bit hectic at work. I very much like the thought of letting the code do the work and after reading various articles this is what I ended up with...

Code:
Public Function LinkTbl()

    Dim sConnect As String
    sConnect = "ODBC;DRIVER={SQL Server};Server=xxx;Database=xxx;Uid=;Pwd=;"
    
    Dim tdf As TableDef
    Dim dbs As Database
    
    If DCount("Name", "MSysObjects", "Name = 'dbo_PPRR_PPRR_00000100' and Type = 4") <> 0 Then 'The table exist
        DoCmd.DeleteObject acTable, "dbo_PPRR_PPRR_00000100"
    End If
    
    Set dbs = CurrentDb
    Set tdf = dbs.CreateTableDef("dbo_PPRR_PPRR_00000100")
    tdf.SourceTableName = "dbo.PPRR_PPRR_00000100"
    tdf.Connect = sConnect
    dbs.TableDefs.Append tdf
    dbs.TableDefs.Refresh
    
    Set tdf = Nothing
    Set dbs = Nothing

End Function

I took this code from another help forum and edited to our needs ( I x'ed out our info). At this point, I would like to link some more tables but I am not quite sure how to go about it in the context above. I am not very familar with VBA but I have done some various c++ / c# in the past so I can grasp some of the logic. How can I tweak the above code to pull in more than just one table?

Also to answer your question, it is 'someone else's' responsibility however no one like that works here lol. I have one of those "many hat" type jobs in metal fabrication, ranging from 3d modeling, cnc programming, to actually welding etc. This just seemed like a nice way to organize our data and make our lives a little easier. Thanks again for your help.
 
I've had trouble with this before. You yourself have access to connect / read from this connection. Others do not. SO you have to import it on your machine to the backend, then link them to that backend.

(MAYBE)
 
On the client (front-end) create a local table that has the name of each table on SQL Server that needs to be linked.

first, destroy all linked tables:
Code:
Public Function DropAllLinkedTables()
    Dim tdf As DAO.TableDef   
10        For Each tdf In CurrentDb.TableDefs    ' If the table has a connect string, it's a linked table.
              ' Drop all tables and rebuild again from local table SQL_Linked where names are stored
20            If Len(tdf.Connect) > 0 Then
30                DoCmd.DeleteObject acTable, tdf.Name
40            End If
50        Next tdf
End Function

Use the local table "SQL_Linked" with the name of the table to connect and a field with a checkbox showing that this table should be linked.
Basically, loop through the table, get a table name and use your connection funciton to link it. Line 290 takes those table names to call your connection function (pass the table name in as a parameter) and finalize the connection.

Have take out logging ane error trapping to get the idea across
Code:
Public Sub SQL_Linked_Process() ' Access 2010 SQL 2008 R2
      ' A local Table SQL_Linked with fields TableName, linked, relink (the last two are yes/no check boxes)
      ' A procedure populates SQL_Linked with all the Access Linked table names.
      ' Placing a check (yes) in Relink will delete the Access Linked Table, and append a new Linked Table with DSNLess connection
      ' It is necessary to have migrated (updated) the Access DB to SQL - assume they have the same name.
      ' For data migration purpose and 1 level testing, a user <user name> was created with sqlserver security in this case. 
      '
      ' ----- Removed code - Code exist to link to Production or Test DB
' Test DB is just a SQL copy of production to conduct development
 
      Dim rsSQLLinked As Recordset
      Dim RecordsCount As Integer
      Dim Counter As Integer
      Dim td As TableDef  ' for table SQL_Linked
      Dim tdLinked As TableDef ' for new linked table
10    On Error Resume Next
20    Set rsSQLLinked = CurrentDb.OpenRecordset("SQL_Linked", dbOpenDynaset, dbSeeChanges)
30    rsSQLLinked.MoveLast
40    RecordsCount = rsSQLLinked.RecordCount
50    rsSQLLinked.MoveFirst
60    Debug.Print "Number of Linked Tables " & RecordsCount
 
      '   Delete the linked tables that have a check in the Relink Column
70    If RecordsCount <> 0 Then
80        For Counter = 1 To RecordsCount
              'Debug.Print Counter & "/" & RecordsCount & " Field value " & rsSQLLinked.Fields(0).Value & "  " & rsSQLLinked.Fields(2).Value
90                    If rsSQLLinked.Fields(2).Value Then                 ' if Relink checkbox is true then
100                         For Each td In CurrentDb.TableDefs
110                               If td.Name = rsSQLLinked.Fields(0).Value Then
' insurance that existing linked tables are removed.
120                                     CurrentDb.TableDefs.Delete rsSQLLinked.Fields(0).Value
                'Debug.Print "Error Deleting old links " & Err.Description & Err.Number & " " & rsSQLLinked.Fields(0).Value
 
130                                     Err.Clear
140                               End If
150                         Next
160                   End If
170       rsSQLLinked.MoveNext
180       Next Counter
190   CurrentDb.TableDefs.Refresh
200   Else
210       MsgBox "There are no records in the table", vbOKOnly, "SQL_Linked_Process"
220       Exit Sub
230   End If
      ' ////////////// Relink to SQL Server ///////////
240   If RecordsCount <> 0 Then
250       rsSQLLinked.MoveFirst
260       For Counter = 1 To RecordsCount
              'Debug.Print Counter & "/" & RecordsCount & " Field value " & rsSQLLinked.Fields(0).Value & "  " & rsSQLLinked.Fields(2).Value
270                   If rsSQLLinked.Fields(2).Value Then                 ' if Relink checkbox is true then
                      ' Add new linked table here
'280                           Set tdLinked = CurrentDb.CreateTableDef(rsSQLLinked.Fields(0).Value)
280                            Set tdLinked = CurrentDb.CreateTableDef(rsSQLLinked.Fields(0).Value, dbAttachSavePWD) ' password persist now
 
290                           tdLinked.Connect = ModifiedRefreshDNSLess2(rsSQLLinked.Fields(0).Value)
300                                   tdLinked.SourceTableName = "dbo." & rsSQLLinked.Fields(0).Value
310                                   CurrentDb.TableDefs.Append tdLinked
320                                   CurrentDb.TableDefs(rsSQLLinked.Fields(0).Value).RefreshLink
330                                   CurrentDb.Containers("Tables").Documents.Refresh ' doesn't refresh table icon
340                   End If
350       rsSQLLinked.MoveNext
            Debug.Print "Error " & Err.Description & Err.Number & " " & rsSQLLinked.Fields(0).Value
 
360       Err.Clear
370       Next Counter
380   CurrentDb.TableDefs.Refresh
390   RerefreshLinkedTables
400   Else
410       MsgBox "There are no records in the table", vbOKOnly, "SQL_Linked_Process"
420       Exit Sub
430   End If
      ' ////////////////
440   Set rsSQLLinked = Nothing
450   Set tdLinked = Nothing
460   Set td = Nothing
470   Exit Sub
End Sub

The debug wiindow will display any error codes during linking.
There are many variation of this.
My SQLLinking table also has a comment field for developers.
It has a history of re-named tables or the purpose of the table.

SQL_Linked table def
TableName - Test
Linked - Yes/No ' if checked then should be a linked table in SQL
Relink - Yes/No ' if checked then re-link this table name
Remarks - Text (just for developers to comment)

Hope that helps
 

Users who are viewing this thread

Back
Top Bottom