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:
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