Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
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.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
<<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.
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.
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.
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial