Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-03-2018, 01:31 PM   #1
mjdemaris
Working on it...
 
Join Date: Jul 2015
Location: Washington State, USA
Posts: 362
Thanks: 80
Thanked 7 Times in 7 Posts
mjdemaris is on a distinguished road
ODBC SQL Server driver

Do I need to have a driver installed on each machine that will use the Access front end linked to the SQL back end?

If so, is there a way I can programmatically install the driver? If this is not an option, is there a way the IT admin can install the driver on all machines desired quickly?

Thanks.

__________________
Life is not a problem to be solved, but a reality to experience.

Soren Kierkegaard
mjdemaris is offline   Reply With Quote
Old 07-03-2018, 02:10 PM   #2
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,133
Thanks: 40
Thanked 3,612 Times in 3,487 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: ODBC SQL Server driver

Quote:
Do I need to have a driver installed on each machine
yes
Quote:
If so, is there a way I can programmatically install the driver?
possibly, but depends on user rights to install things like drivers, not enough info to provide a suggestion as to how
Quote:
is there a way the IT admin can install the driver on all machines desired quickly?
you'll need to ask your IT admin
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
mjdemaris (07-03-2018)
Old 07-03-2018, 02:27 PM   #3
mjdemaris
Working on it...
 
Join Date: Jul 2015
Location: Washington State, USA
Posts: 362
Thanks: 80
Thanked 7 Times in 7 Posts
mjdemaris is on a distinguished road
Re: ODBC SQL Server driver

Well...that certainly complicates things a little. To the IT guy I go, lol.

__________________
Life is not a problem to be solved, but a reality to experience.

Soren Kierkegaard
mjdemaris is offline   Reply With Quote
Old 07-03-2018, 02:37 PM   #4
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,081
Thanks: 110
Thanked 2,738 Times in 2,500 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: ODBC SQL Server driver

I haven't done this for a while but from memory if you use a DSN-less connections, you don't need a driver on each workstation

This link seems to confirm my memory is correct:
https://stackoverflow.com/questions/...erver-back-end
See the response by Albert Kallal
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 07-03-2018, 02:52 PM   #5
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,133
Thanks: 40
Thanked 3,612 Times in 3,487 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: ODBC SQL Server driver

perhaps I stand corrected but I think you still need the driver. Easy enough for the OP to test out
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 07-03-2018, 03:03 PM   #6
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,081
Thanks: 110
Thanked 2,738 Times in 2,500 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: ODBC SQL Server driver

I have only ever connected Access FEs to SQL Server BEs using DSN less connections. One of the main advantages is that you don't need to setup each workstation individually. Just distribute the FE and it should 'work out of the box' on each workstation.

My only reason for hesitating is that I haven't had to do do this for a couple of years with clients. But that certainly is still the case on my home network
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 07-03-2018, 03:05 PM   #7
mjdemaris
Working on it...
 
Join Date: Jul 2015
Location: Washington State, USA
Posts: 362
Thanks: 80
Thanked 7 Times in 7 Posts
mjdemaris is on a distinguished road
Re: ODBC SQL Server driver

Colin: you are stating that you did not install the 32 or 64 bit SQL Server client driver?

__________________
Life is not a problem to be solved, but a reality to experience.

Soren Kierkegaard
mjdemaris is offline   Reply With Quote
Old 07-03-2018, 03:26 PM   #8
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,081
Thanks: 110
Thanked 2,738 Times in 2,500 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: ODBC SQL Server driver

Not on each workstation.
It must get installed on the machine running SQL Server Management Studio as the driver is used as part of the DSN-less connection string
For example, this connection string is used for one of my apps on my home PC
Quote:
ODBC;DRIVER={SQL Server};SERVER=.\SQLEXPRESS;DATABASE=SDABE;APP=Sch oolDataAnalyser;UID=SDAuser;PWD=SDApassword
SQL Server is installed on 1 PC which acts as the server. This has SQL Native Server 11.0 as part of the SSMS installation.

I even have a Windows tablet running Access FEs linked to SQL Server BEs. I know for certain that I've never installed any ODBC drivers on that

But I'm no expert on this topic and suggest you read up on the issue

This site is excellent for connection string examples:https://www.connectionstrings.com

Good luck
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
mjdemaris (07-03-2018)
Old 07-03-2018, 05:17 PM   #9
mjdemaris
Working on it...
 
Join Date: Jul 2015
Location: Washington State, USA
Posts: 362
Thanks: 80
Thanked 7 Times in 7 Posts
mjdemaris is on a distinguished road
Re: ODBC SQL Server driver

Do you have a coded function in Access that creates a connection?
Just reading about DBEngine.RegisterDatabase and also using the CreateTabelDef method to basically replace the DSN file.
Using the CreateTableDef looks like it could be a lot of work, since the code given takes each table name, local and remote, as arguments...so if I have 30 tables, plus views...
Here is the article: https://support.microsoft.com/en-us/...nked-tables-in

Thoughts on the usefulness or reliability of these two methods?
If I did use one of these, I would most likely use the AutoExec macro, since that is what I currently use.

But, I am still not sure that this negates the need for a driver, since it seems the Driver Manager is involved.
I may be able to work on this later this week.
__________________
Life is not a problem to be solved, but a reality to experience.

Soren Kierkegaard
mjdemaris is offline   Reply With Quote
Old 07-03-2018, 05:40 PM   #10
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,578
Thanks: 88
Thanked 1,486 Times in 1,402 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: ODBC SQL Server driver

It has been many years since I messed about with ODBC drivers but I think this is the story.

Some SQL Server ODBC drivers are included in Windows install. It depends of the version of Windows. You can see what is installed on the machine in Administrative Tools > DataSources(ODBC) > Drivers tab.

More specific drivers can be installed by downloading them at the Microsoft site.

If you want to use another specific driver version that is not already on the clients you need to install it on them. Easier to just use something that is already there.

To use DNSless connections simply create a FileDSN on your machine then use it to link the tables in Access. There is a lot of complex code on the net to convert table links to a DSNless connection. It isn't necessary if you use a FileDSN in the first place.
Galaxiom is offline   Reply With Quote
The Following User Says Thank You to Galaxiom For This Useful Post:
mjdemaris (07-03-2018)
Old 07-03-2018, 05:49 PM   #11
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,953
Thanks: 13
Thanked 1,534 Times in 1,460 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: ODBC SQL Server driver

Colin,
The DRIVER is required. A DSN is not required if you use a DSNless connection to link to the BE.

Access installs with the default "SQL Server" driver. This driver is circa 2000 and any data type added to SQL server since that time will NOT be supported so newer date data types, BigInt and a number of other data types will simply not work. Therefore, if you have dates other than "DateTime" or other unsupported data types, then you will need to install a special driver.

I have always had the Desktop support staff do the driver push. I download the driver I need and they use the user's login procedure to install the driver if it is needed. Don't ask me how but they all seemed to understand what was required and accomplish it with very little effort.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
The Following User Says Thank You to Pat Hartman For This Useful Post:
mjdemaris (07-03-2018)
Old 07-03-2018, 06:00 PM   #12
mjdemaris
Working on it...
 
Join Date: Jul 2015
Location: Washington State, USA
Posts: 362
Thanks: 80
Thanked 7 Times in 7 Posts
mjdemaris is on a distinguished road
Re: ODBC SQL Server driver

Well, Galaxiom and Pat, (and CJ hit on this right off the bat)
I just found out that you are both right. We have an Intel ComputeStick in use, and I played around with the file DSN and DSN-less connections to no avail.
I did notice that "SQL Server" was a driver listed, the version number was 10.xx, but that did not work.

So, it appears that you have done what I need to do: download the driver and ask the IT staff to install it.

Guess that'll have to wait...

Thank you for your help, I'll return on this topic when it gets solved.

Ridders: still not sure how you got it to work without a driver, but I suppose that depends on which versions of Windows, Access, and SQL Server you are using.

Also want to give additional thanks to the people on this forum: I have submitted this question to SQL server central and SQLTeam and have had no responses at this time. Even though this is designated as Access, the experience here is what I really appreciate.
__________________
Life is not a problem to be solved, but a reality to experience.

Soren Kierkegaard

Last edited by mjdemaris; 07-03-2018 at 06:06 PM.
mjdemaris is offline   Reply With Quote
Old 07-03-2018, 06:33 PM   #13
AccessBlaster
.
 
Join Date: May 2010
Posts: 1,103
Thanks: 24
Thanked 268 Times in 255 Posts
AccessBlaster has a spectacular aura about AccessBlaster has a spectacular aura about
Re: ODBC SQL Server driver

A couple of interesting videos that support the subject.

Creating An ODBC Connection To SQL Server
Migrating Access Data To SQL Server
AccessBlaster is offline   Reply With Quote
Old 07-03-2018, 07:00 PM   #14
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,578
Thanks: 88
Thanked 1,486 Times in 1,402 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: ODBC SQL Server driver

Quote:
Originally Posted by Pat Hartman View Post
Access installs with the default "SQL Server" driver.
I guess that also goes for the Runtime which is why I haven't had to install the driver on my client machines.
Galaxiom is offline   Reply With Quote
Old 07-04-2018, 12:06 AM   #15
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,081
Thanks: 110
Thanked 2,738 Times in 2,500 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: ODBC SQL Server driver

To clarify, I didn't say the driver wasn't needed. My point was I didn't have to install it on each workstation as it's already there.

However, I stand corrected about what installs it. I suggested it was part of SSMS install, but it makes much more sense that all supported drivers are installed with Access

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Reply

Tags
odbc , sql server

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
ODBC Driver 13.1 for SQL Server - new verson released Rx_ Modules & VBA 0 04-21-2018 02:12 PM
ODBC--call failed. [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object TBC General 2 04-14-2011 09:46 AM
ODBC Driver KenHigg General 3 10-19-2006 11:19 AM
Access DB and ODBC SQL Server Driver cbe Tables 0 10-15-2004 05:41 AM
[ODBC Driver Manager] Data source name not found and no default driver specified cft Queries 2 04-16-2002 06:50 AM




All times are GMT -8. The time now is 06:47 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World