ODBC gone mad?

[CedarTree]

I use connection strings to connect to SQL server and attach tables. For a while I used "SQL Server" such as:

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]

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]

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]

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.

 

[Isaac]

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