ODBC gone mad? (1 Viewer)

CedarTree

Registered User.
Local time
Today, 16:38
Joined
Mar 2, 2018
Messages
404
I use connection strings to connect to SQL server and attach tables. For a while I used "SQL Server" such as:
Code:
ODBC;Driver={SQL Server};Server=SERVER-NAME;Database=DATABASE-NAME;Uid=USER-NAME;Pwd=PASSWORD;Encrypt=No;Trusted_Connection=No

This worked just fine. I could refer to the tables in code, and even just double click an attached table and see it.
Then it started giving me fatal connection errors. So I switched to using "SQL Server Native Client 11.0". That seemed to be better, but now when I double-click on a table, I need to manually click on "Trusted Connection". This happens whether or not I use "Yes" in the string above.

Help please!
 

bastanu

AWF VIP
Local time
Today, 13:38
Joined
Apr 13, 2010
Messages
1,401
When you switch drivers you need to relink the tables. When the wizard comes up you will see a check box on the lower left to "Save the password". Answer yes to the warning and unfortunately you will get a prompt for every linked table. Make sure you answer Yes to all and you should be fine.

Cheers,
 

CedarTree

Registered User.
Local time
Today, 16:38
Joined
Mar 2, 2018
Messages
404
Yeah for end users I'm trying to avoid that. I re-tried putting Trusted_Connection = Yes in the string and so far seems to be working, so maybe just a fluke.
 

bastanu

AWF VIP
Local time
Today, 13:38
Joined
Apr 13, 2010
Messages
1,401
Keep in mind that each end user will need to have the new driver installed. If you use SQL Server authentication (using UID\PWD) you do not need the Trusted_Connection in the Connection string, that is used with Windows Authentication.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:38
Joined
Feb 19, 2002
Messages
42,975
YOU need to relink the BE using the new driver before the user gets the new copy of the FE.
 

Isaac

Lifelong Learner
Local time
Today, 13:38
Joined
Mar 14, 2017
Messages
8,738
In recent times I've begun using File DSN's more, they seem to simplify the situation - if only (in some occasions) conceptually.

DSN's is one of the only times I use Access tables, if I can help it. Everything is on SQL Server, except certain config stuff - and 'certain config stuff' includes the Access back end table on a shared network location that records whether or not that particular user, on that particular machine, has run my "CreateDSN" code - now that was more before I started liking File DSN's more.

ODBC connectivity is one of those things that has a LOT of ways to handle the situation, if you take the combinations of all the different elements.

I got pretty excited when Doug came out with DSNLess connections code years back, but if I remember right there were so many And's If's and But's (at least pertaining to my environments, it seemed), that I mostly abandoned it in favor of using DSN's.
But more power to you if it works for you - portability is cool ✌️
 

Users who are viewing this thread

Top Bottom