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.
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.
Probably some more info on the error....maybe the Authentication on the SQL server....
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.
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.
@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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
<<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.
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.
On Monday i will get the screenshots from the end users when they are back in office.
thanks again.
Jim's right, I've not seen that you also use 2007.
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
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.
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks guys.
Also sorry for the late feedback.
Also sorry for the late feedback.
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.