Avatar of Keran O'Brien
Keran O'Brien
 asked on

MS Access / SQL Server - ODBC Problems

Hi,

I am developing a database application on MS access 2016 with SQL Server as the backend.

I am having difficulty with ODBC connection; it seems OK on my own machine (full version of Access) but on the end-users machine(s) which are running various versions of Access runtime.

Through the ODBC connection wizard on the end user machine I used EXACTLY the same configuration options as on my machine. I test the connection and the result is successful, but when I open the application and i get an ODBC error the first time i try to do any data interactions.

Something that might be of help: on one of my forms where I was using a datasheet the data would display fine, then later i changed to a continuous form (same record source) and it comes up blank on the machine running runtime.
Microsoft Access

Avatar of undefined
Last Comment
Keran O'Brien

8/22/2022 - Mon
Jim Dettman (EE MVE)

<<Through the ODBC connection wizard on the end user machine I used EXACTLY the same configuration options as on my machine. I test the connection and the result is successful, but when I open the application and i get an ODBC error the first time i try to do any data interactions.>>

 Within the Access DB, how are you accessing the tables?   Are they linked and your just binding forms, or are you doing something more complex, like opening ADO recordsets and assigning recordsets to forms?

 Any pass-through queries being used?   Views out of SQL?  Stored Procedures?

Jim.
John Tsioumpris

Probably some more info on the error....maybe the Authentication on the SQL server....
ste5an

Verify that all machines have the same drivers installed. This means SQL Native Client 11 and/or Microsoft ODBC 13 for SQL Server. Cause both drivers are not installed per default.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Jim Dettman (EE MVE)

Also that the client settings for protocols for talking to SQL (TCPIP/named pipes/shared memory) are listed in the same order as your station and/or server.

Jim.
Keran O'Brien

ASKER
Thanks gents for the reply.

@Jim Dettman The tables are linked in Access. I have not used bound forms at all.
I used pass through queries for populating datasheets / continuous forms.
I use recordsets when I add / update data, or when populating unbound forms.
I'm not sure what you mean by assigning the recordsets to the forms, generally my practice (and tell me if this is wrong) is to create a recordset in the open event of the form and use this to populate the fields.

@John Tsioumpris regarding Authentication on the server i'm not 100% sure if im answering your question properly but the server admin added me as a user on the server and when I set up the ODBC i use "integrated windows authentication" leaving SPN blank.

@ste5an this is something i tried before and felt like i was making some headway. I have a W10 64bit machine but running a 32bit version of Access 2016, some end users have the same machine with Access runtime, others have W7 with Access2007 / 2013.
When i started developing the system I used the Native Client 11 on my machine but this started to give me odd problems like 20 tables would be linked and work fine and then the next table id link to would throw the ODBC error every time i opened it. I then switched to ODBC 13 for SQL Server and never had any problems on my own machine after that. I tried to do this on every end user machine but not every machine was accepting that driver.
ASKER CERTIFIED SOLUTION
ste5an

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Keran O'Brien

ASKER
I used System DSN connection.
Also i must be lying about originally using the Native Client 11 when I first started, it must have been a different driver because when i setup a new connection using the NC11 i cannot see any of my tables in the SQL Server (i can see just about everything else just none of the tables i have created)

I will try to post up some screenshots, i actually don't know where to start now.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Jim Dettman (EE MVE)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Jim Dettman (EE MVE)

<<I will try to post up some screenshots, i actually don't know where to start now.>>

 Let's focus on the actual error(s) that you get first.

Jim.
Keran O'Brien

ASKER
ok guys thanks for all your input.
On Monday i will get the screenshots from the end users when they are back in office.

thanks again.
ste5an

Jim's right, I've not seen that you also use 2007.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Keran O'Brien

ASKER
Hi,

Attached is a screenshot from a user this morning with W10, access runtime V.13x and NativeClient 11 connection. (i changed this on Friday evening on my machine)

In the interest of trying something i got him to upgrade to AR V16.x now everything works fine. Im starting to think this is not an ODBC problem and more likely im using something from a library only available in 16.x is this possible?

I'm not exactly sure of the solution but your advice to program on 2007 is probably the most robust option if i could get a full version of that??
I sat with our IT guy today and went through the potential users (probably should have been done first). 2 of them have had their machines upgraded to W10 recently and this left only 1 person (albeit a critical player) running an older version. He is going to try sort hers in the coming days and we will see if that works.

Thank you all very much for your help until now, and if you have any further advice on this i will appreciate it.
NC_Error.PNG
ste5an

Okay, back to start.. The most common issues here are not ODBC-caused, because we can see that you're able to load data (the order number field).

Step 1: Confirm that your data access works. Thus open the data source in the database window or via the Data/Record Source property in the design mode.

Step 2: Why action is performed? Thus where is content changed? Take a look at the event of that control in the design mode with property sheet.
Keran O'Brien

ASKER
the data access works fine in both design mode and "run" mode on my machine.
On the machine where the error was generated I cannot verify the data access as this machine is running Access runtime however the error does not happen after i upgraded that user to Access Runtime V16.x. everything works fine then it seems.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
ste5an

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Keran O'Brien

ASKER
Thanks guys.
Also sorry for the late feedback.