Using the as-a-service approach for your business model allows you to grow your revenue stream with new practice areas, without forcing you to part ways with existing clients just because they don’t fit the mold of your new service offerings.
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.
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.
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get this solution by purchasing an Individual license!
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
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.
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.
Jim.
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.
@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.
SQL Server Native Client should work fine, as long as you don't use some of the new features like Always Encrypted.
Then I strongly encourage you to install and use the same driver on all machines. And a further question: Do you use DSN or DSNless connections?
You should also examine the cause for your ODBC errors while using SQL Server Native client, cause this sounds pretty unusual. I'm working with SQLNCLI since years without any issues at all.
Then, as your using a mixed Access/Office environment: You need to develop on 2013. Otherwise there could be some version incompatibilities causing instabilities.
And last but not least: some concrete information about your errors would helpful, e.g. screenshots.
Then I strongly encourage you to install and use the same driver on all machines. And a further question: Do you use DSN or DSNless connections?
You should also examine the cause for your ODBC errors while using SQL Server Native client, cause this sounds pretty unusual. I'm working with SQLNCLI since years without any issues at all.
Then, as your using a mixed Access/Office environment: You need to develop on 2013. Otherwise there could be some version incompatibilities causing instabilities.
And last but not least: some concrete information about your errors would helpful, e.g. screenshots.
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trial
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.
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.
<<Then, as your using a mixed Access/Office environment: You need to develop on 2013. Otherwise there could be some version incompatibilities causing instabilities. >>
That would be the opposite; always would want to develop with A2007.
You can always move forward in a mixed environment, but you'll have issues if you go from A2013 back to A2007.
Jim.
That would be the opposite; always would want to develop with A2007.
You can always move forward in a mixed environment, but you'll have issues if you go from A2013 back to A2007.
Jim.
<<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.
Let's focus on the actual error(s) that you get first.
Jim.
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.
On Monday i will get the screenshots from the end users when they are back in office.
thanks again.
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
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
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.
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.
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.
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.
This sounds like an error in your macros or code-behind with the runtime.
Rule of thumb: When using the runtime, you need really good error handling.
Rule of thumb: When using the runtime, you need really good error handling.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access
From novice to tech pro — start learning today.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get this solution by purchasing an Individual license!
Start your 7-day free trial.
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.