ODBC call failed (runtime 3146) on another PC

[Thomasso]

Hello,



I've been developing an Access app for a while and I'm finally at the stage of deploying and testing on multiple devices. Front end is MS Access (obviously) and back and is Microsoft SQL Server 2019.

I use DSNless connection of linked tables, using this code:

Public Const CONNECTION_STRING_DAO = "ODBC;Driver={SQL Server Native Client 11.0};Server=SRV01;Database=DATYS_TESTING;Trusted_Connection=yes;"

Public Sub LinkSQLTables()
On Error GoTo ErrHandler

    Dim td As TableDef
    Dim rs As Recordset
    
    
    For Each td In CurrentDb.TableDefs
    
        If td.Name Like "v_*" Or td.Name Like "vDE_*" Or td.Name Like "v2_*" Then
            CurrentDb.TableDefs.Delete td.Name
        End If
        
    Next
    
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM SQL_Links", dbOpenSnapshot)
    
    Do While Not (rs.BOF Or rs.EOF)
    
        Set td = CurrentDb.CreateTableDef(rs!LinkName, dbAttachSavePWD, rs!LinkName, CONNECTION_STRING_DAO)
        CurrentDb.TableDefs.Append td

        CurrentDb.Execute "CREATE INDEX " & rs("LinkName") & "_PK ON " & rs("LinkName") & " (" & rs("PrimaryKeyField") & ") WITH PRIMARY"
        
        rs.MoveNext
    
    Loop
    
    rs.Close
    Set rs = Nothing
        
Exit Sub
ErrHandler:

    MsgBox "Během pokusu o spojení s databází nastala chyba. Aplikace bude ukončena." & vbNewLine & vbNewLine & _
            "Číslo chyby: " & Err.Number & vbNewLine & _
            "Popis chyby: " & Err.Description, vbCritical + vbOKOnly, "Chyba"
            
    rs.Close
    Set rs = Nothing
            
    Application.Quit
    
End Sub

This works just fine using my developer PC.

For deployment purposes, I installed Access Runtime on another PC within the same network, and saved the DB as ACCDE file. I am perfectly able to run this on my dev PC as well.

However, I get the "ODBC call failed runtime 3146" while executing the "LinkSQLTables" procedure (using the other PC).

At the same, I can open SQL Server Management Studio on the other PC and I'm perfectly able to connect to the database and browse through it, using Windows credentials, same as in the connection string.

Everything is in the same Active Directory environment, both PCs are Windows 10, I am logged with the same credential and it's the exact same ACCDE file. Both PCs can access the SQL server using the Management Studio.

Can you think of any reason why this might be happening?

Thank you very much.
Tomas

[Welshgasman]

Are the references the same?

[Thomasso]

What do you mean?

[Welshgasman]

What do you mean?

I mean that there might be a reference missing on the errant PC?
Compare that to yours.

[Thomasso]

Is this relevant for MS Access Runtime? I can't choose any references, I can just run a finished ACCDE application, or is that not the case?

[Minty]

What ODBC driver is installed on the runtime machine?

If it doesn't have SQL Server Native Client 11.0 installed, (Very old by the way I would recommend updating to Ver 17 at least, on all machines) it won't be able to connect.

[Thomasso]

Honestly I didn't think of this, figured it would be installed by default. If I install Ver 18 (by searching, this seems to be the newest one), do I need to update the connection string as well, or is there backwards compatibility?

What if there is a new driver available in the future? Will I need to also update the connection string and push new front end version to the users?

Thank you!

[Gicu]

Yes to both your questions, the connection string must match the installed driver. One way to do it is to have a custom system table in the front end (just use the prefix usys like in usysSettings) in which you store the connection string instead of hard-coding it as a constant in VBA. And have you considered using TableDef.RefreshLink method in your LinkSQLTables() procedure instead of deleting and recreating?
https://learn.microsoft.com/en-us/of...ink-method-dao

Cheers,

[Minty]

@Gicu has provided the answers, but to clarify they aren't backwards compatible, they are specific. Version 18 is the latest.

You can go one stage further if you need to, we have routines that search the registry and then set the driver/connection string for the latest one installed, but it not trivial to set up as you need to keep the lists up to date and have a way of updating them...

[Thomasso]

Thank you guys, indeed it helped to change the connection string to Driver 17 and to make sure it is installed on that PC. I will look into the other suggestions. Would it be quicker to use TableDef.RefreshLink? Because the current process is kind of slow.

[Minty]

I'm pretty sure the refresh works quicker from memory.

If you go into the linked table manager and click refresh links(after selecting all the tables) it it pretty rapid, as it won't be re-writing all the table info (Remote name, connection string, local name etc. etc.)