UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Odbc Connection String Driver, Any Versions    
 
   
BruceM
post Nov 6 2017, 11:37 AM
Post#1


UtterAccess VIP
Posts: 7,022
Joined: 24-May 10
From: Downeast Maine


SQL Server 2016.

I have been working with changing over to SQL Server as the BE for some Access databases. I am using Windows authentication.

When I set up the connection I initially use a DSN connection. After the link has been made I use code Doug Steele provided here to loop through the TableDefs and set up a DSN-less connection.

This works well, with one caveat, which is that one of the databases seems sometimes to drop the connection. I'm sorry I did not note the exact error message, but it is something to the effect that the ODBC connection is not valid. I imported all objects except linked tables into a new blank database, then linked to the tables anew. So far the connections are stable, so there is no error message to quote, but the problem was inconsistent before, so I'm not sure it's fixed now.

In searching for information about this I came across some discussion that suggests the SQL Server driver selected may have some bearing on the problem. Here is the connection string I am using (the actual string has the real server and database name):

ODBC;DRIVER=SQL Server;SERVER=ServerName;APP=Microsoft Office 2010;Trusted_Connection=YES;DATABASE=SQL_ServerDatabaseName

I have noticed when making the initial DSN connection that the options for a driver include ODBC Driver 13 for SQL Server, SQL Server (the one I am using in the current connection string), and SQL Server Native Client 11.0. Different things I have read variously describe using any of those three, so I'm not sure if there is a reason to prefer one over the other. All three seem to work when setting up the DSN connection (although with somewhat different dialog boxes when stepping through the different processes), and I haven't found anything to describe why or when to prefer one over the other.
Go to the top of the page
 
GroverParkGeorge
post Nov 8 2017, 07:04 AM
Post#2


UA Admin
Posts: 31,239
Joined: 20-June 02
From: Newcastle, WA


All else being equal, I recommend the newer drivers, i.e, ODBC Driver 13 for SQL Server. It is designed to work with current datatypes, such as Datetime2, in SQL Server, whereas as the venerable SQL Server driver doesn't.

I've seen suggestions that this one is also more stable, but have no direct experience to confirm it one way or the other.

--------------------
Go to the top of the page
 
BruceM
post Nov 8 2017, 07:53 AM
Post#3


UtterAccess VIP
Posts: 7,022
Joined: 24-May 10
From: Downeast Maine


OK, thanks George, I'll put that one into the connection string and give it a try. I could have tried that one without asking the question, but I wouldn't have much way to judge its effectiveness other than waiting to see if it fails, so I'm glad to get a specific recommendation from somebody with experience in these matters.
Go to the top of the page
 
GroverParkGeorge
post Nov 8 2017, 07:57 AM
Post#4


UA Admin
Posts: 31,239
Joined: 20-June 02
From: Newcastle, WA


You'll need to download and install it first.

--------------------
Go to the top of the page
 
BruceM
post Nov 8 2017, 08:49 AM
Post#5


UtterAccess VIP
Posts: 7,022
Joined: 24-May 10
From: Downeast Maine


When I set up a DSN connection to create the initial link to the SQL Server table or view I tried selecting ODBC Driver 13 for SQL Server. The connection information dialog that appeared showed a specific version number (13.00 and some more numbers that I didn't specifically note at the time).

I also tried using it in the connection string for Doug Steele's DSN-less procedure referenced in my first posting in this thread, and it worked there also.

It appears I have it installed, although I didn't specifically do so that I can recall. Will it need to be installed on each computer where the Access front end is installed?
Go to the top of the page
 
GroverParkGeorge
post Nov 8 2017, 09:02 AM
Post#6


UA Admin
Posts: 31,239
Joined: 20-June 02
From: Newcastle, WA


yes, that's one of the downsides. SQL Server driver is almost always installed with the OS, whereas the newer drivers are not.

--------------------
Go to the top of the page
 
BruceM
post Nov 8 2017, 11:12 AM
Post#7


UtterAccess VIP
Posts: 7,022
Joined: 24-May 10
From: Downeast Maine


Thanks. I have spent the past hour trying to find a way to determine whether that driver is installed. I seem to have it installed, but since working with IT on something like this tends to be hideously complex I would like to find a way, other than waiting for the connection to fail, to figure it out in advance. I have tried driverquery at a command prompt, but it is not listed there, leading me to wonder if it is installed on the server where SQL Server resides rather than on each local machine.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    17th December 2017 - 03:18 AM