Odd ODBC Issue

[shaunsizen]

Hi
Got a brain teaser here, just upgrading a client app to use TLS so ODBC v18 driver, the code drops and relinks the DB from the client to SQL Server. Connection string works fine when its the base code but fails when I compile it!!



Any ideas?

This is my string (I will be taking TrustServerCertificate=True out for prod but wanted to make sure its not that)

strConnect = "ODBC;DRIVER=ODBC Driver 18 for SQL Server;Server=" & strServerName & ";UID=SAC_User;PWD=sac;DATABASE=" & strDatabase & ";Network=DBMSSOCN;PORT=1499"

strConnect = strConnect & ";iTrustServerCertificate=True;Trusted_Connection= Yes;Encrypt=True"

Any ideas? I am running 365 on 64Bit

[ranman256]

have you tried the generic drivers:
SQL Server
or
SQL Client

these always seem to work.

[shaunsizen]

have you tried the generic drivers:
SQL Server
or
SQL Client

these always seem to work.

They do but are not encrypted and that's now what the client needs, it works uncompiled just fails when compiled?

[Micron]

I use Option Explicit as a rule so this compiles for me

Sub Test()
Dim strConnect As String, strServerName As String, strDatabase As String

strConnect = "ODBC;DRIVER=ODBC Driver 18 for SQL Server;Server=" & strServerName & ";UID=SAC_User;PWD=sac;DATABASE=" & strDatabase & ";Network=DBMSSOCN;PORT=1499"
strConnect = strConnect & ";iTrustServerCertificate=True;Trusted_Connection= Yes;Encrypt=True"

End Sub

What is the compile error message?

[isladogs]

Just out of interest, which version of Access are you using including build number, bitness and update channel.
For example: Access 365 Monthly Enterprise Channel Version 2312 Build 16.0.17126.20190 64-bit

I ask as there have been a number of ODBC related bugs recently not all of which have been fixed in certain channels

[shaunsizen]

Hi,
It compiles but just fails when you run the accde with a generic error, uncompiled is fine

Running Microsoft® Access® for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20124) 64-bit with CurrentChannel updates and did a amanual update to be sure of latest version

[pbaldy]

Post 6 was moderated, I'm posting to trigger email notifications.

Images can't be pasted into the reply window, they need to be attached in the "Go Advanced" area.

[shaunsizen]

Ah apologies for that

[pbaldy]

Ah apologies for that

No worries! Welcome to the site by the way.

[isladogs]

The ODBC bugs I was referring to are all fixed in Current Channel version 2402.
See items 1,2 & 4 on Access Forever bugs, tools, events

[shaunsizen]

Thanks I will take a look
Shaun