Native Jet OLE DB Provider vs. Microsoft Access Direct Link
Hello EE,
I am using Access 2003 for this project. I have a database which has linked tables to 5 other databases. This databash crashes often and I have already gone through most of the famous steps for crash prevention. I came across the article below and need to know if my understanding of it is correct and if there's a fast way to implement amongst a lot of users.
It seems like this article is saying that instead of doing the traditional link to a table in another Access database I should: Go to the control panel's ODBC administrative options and create Access ODBC data source on each PC. Then within the main access database link to these ODBC connections (instead of linking directly to the other Access database.) Do I have this right?
LVBarnes
http://support.microsoft.com/kb/299974
excerpt...
When you run Microsoft Jet in an IIS environment, it is recommended that you use the native Jet OLE DB Provider in place of the Microsoft Access ODBC driver. The Microsoft Access ODBC driver (Jet ODBC driver) can have stability issues due to the version of Visual Basic for Applications that is invoked because the version is not thread safe. As a result, when multiple concurrent users make requests of a Microsoft Access database, unpredictable results may occur. The native Jet OLE DB Provider includes fixes and enhancements for stability, performance, and thread pooling (including calling a thread-safe version of Visual Basic for Applications).
I am using Access 2003 for this project. I have a database which has linked tables to 5 other databases. This databash crashes often and I have already gone through most of the famous steps for crash prevention. I came across the article below and need to know if my understanding of it is correct and if there's a fast way to implement amongst a lot of users.
It seems like this article is saying that instead of doing the traditional link to a table in another Access database I should: Go to the control panel's ODBC administrative options and create Access ODBC data source on each PC. Then within the main access database link to these ODBC connections (instead of linking directly to the other Access database.) Do I have this right?
LVBarnes
http://support.microsoft.com/kb/299974
excerpt...
When you run Microsoft Jet in an IIS environment, it is recommended that you use the native Jet OLE DB Provider in place of the Microsoft Access ODBC driver. The Microsoft Access ODBC driver (Jet ODBC driver) can have stability issues due to the version of Visual Basic for Applications that is invoked because the version is not thread safe. As a result, when multiple concurrent users make requests of a Microsoft Access database, unpredictable results may occur. The native Jet OLE DB Provider includes fixes and enhancements for stability, performance, and thread pooling (including calling a thread-safe version of Visual Basic for Applications).
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Did you find a solution for your speed problem?
<<It seems like this article is saying that instead of doing the traditional link to a table in another Access database I should: Go to the control panel's ODBC administrative options and create Access ODBC data source on each PC. Then within the main access database link to these ODBC connections (instead of linking directly to the other Access database.) Do I have this right?>>
No because this is a very specific instance. It's not saying that ODBC is bad, it's saying that ODBC is bad when used in conjuntion with IIS and ASP pages. It's a very specific circumstance.
There are many ODBC apps out there than run without problems.
JimD.
No because this is a very specific instance. It's not saying that ODBC is bad, it's saying that ODBC is bad when used in conjuntion with IIS and ASP pages. It's a very specific circumstance.
There are many ODBC apps out there than run without problems.
JimD.
and BTW, JET is usually *very* stable when used as the BE for a web server as there is only one user connected at one time, which is the web server software itself.
JimD.
JimD.
ASKER
Nelson,
I'm still working on the speed problem and testing out the VBA that was given.
But, while waiting I decided to try and tackle the crashing. At this point I have the database on my laptop and it is linked to 5 other databases on the same laptop to rule out network packet crashes. It is linked to the 5 other databases because they are each at or above 1gb. Even with this local format, I'm still getting crashes. I've eliminated yes/no's, decompiled, established static connections to the linked databases, etc. The fileservers here are fairly slow, but I'll be testing out your changes today.
EE has taught me a lot about SQL processing and I understand that this application should be pointing to SQL server, but for this client that is not an option. I can't even install a free version on a server nor put a pc on the network that pretends to be a server.
I'm still working on the speed problem and testing out the VBA that was given.
But, while waiting I decided to try and tackle the crashing. At this point I have the database on my laptop and it is linked to 5 other databases on the same laptop to rule out network packet crashes. It is linked to the 5 other databases because they are each at or above 1gb. Even with this local format, I'm still getting crashes. I've eliminated yes/no's, decompiled, established static connections to the linked databases, etc. The fileservers here are fairly slow, but I'll be testing out your changes today.
EE has taught me a lot about SQL processing and I understand that this application should be pointing to SQL server, but for this client that is not an option. I can't even install a free version on a server nor put a pc on the network that pretends to be a server.
<<Even with this local format, I'm still getting crashes.>>
"crashes" as in what happens?
JimD.
"crashes" as in what happens?
JimD.
ASKER
Hi Jim,
Thanks for your comments, you've planted a seed...instead of having the front end be Access for these 17+ users, maybe switch to an asp type of interface. If I can do it without the IT support it may be possible. (Their solution is to build everything within SAP which takes months. This is a rapid prototype designed to flush out the user needs prior to developing an actual spec document.)
LVBarnes
Thanks for your comments, you've planted a seed...instead of having the front end be Access for these 17+ users, maybe switch to an asp type of interface. If I can do it without the IT support it may be possible. (Their solution is to build everything within SAP which takes months. This is a rapid prototype designed to flush out the user needs prior to developing an actual spec document.)
LVBarnes
ASKER
JIm,
I forgot to answer you other question. By crash I mean that the standard Microsoft message appears saying that the database is corrupted and that Microsoft wants to send a report as well as compact/repair the database. Then the system creates a backup copy and the new copy opens almost immediately. I'm running Office's service pack 3 and I've ensured that the Jet engine is up to date as well. I do drive a lot of form driven events and the crashing is not triggered by the same thing. As I've been building I compile often as well.
When I'm done with the above the next steps are to move everthing into a new copy of access (via importing.), recompile, change it to an Access 2002-2003 .mde format and pray. I wish there were some way to review a crash log that the lay user (that's me) could view to see what data elements are causing it.
I forgot to answer you other question. By crash I mean that the standard Microsoft message appears saying that the database is corrupted and that Microsoft wants to send a report as well as compact/repair the database. Then the system creates a backup copy and the new copy opens almost immediately. I'm running Office's service pack 3 and I've ensured that the Jet engine is up to date as well. I do drive a lot of form driven events and the crashing is not triggered by the same thing. As I've been building I compile often as well.
When I'm done with the above the next steps are to move everthing into a new copy of access (via importing.), recompile, change it to an Access 2002-2003 .mde format and pray. I wish there were some way to review a crash log that the lay user (that's me) could view to see what data elements are causing it.
So Access itself is crashing? That's fairly unusual given that you've tried this on different machines and have created a new MDB and imported everything into it.
That means it's either something in the code that's tripping over an Access bug or somehow related to access the data in the remote DBs.
Is there any type of pattern to the crashes (ie while accessing a certain table, using a specific form, etc).
Is the address of the crash always the same or in the same module?
What types of databases are the remote DB's?
Did you check for updated ODBC or OLEDB drivers?
JimD.
That means it's either something in the code that's tripping over an Access bug or somehow related to access the data in the remote DBs.
Is there any type of pattern to the crashes (ie while accessing a certain table, using a specific form, etc).
Is the address of the crash always the same or in the same module?
What types of databases are the remote DB's?
Did you check for updated ODBC or OLEDB drivers?
JimD.
ASKER
....documenting crashes...to answer some of the above.
* Yes, Access crashes and sends data to Microsoft about the crash.
* I hope it's in the code, because then it is fixable. (One of the reasons I got rid of all the yes/no data types.)
* The remote databases are Access as well, usually containing one or two very large tables. (Over a million records.) These are keyed and also have certain fields indexed.
* Regarding drivers, I just used Office Update and also verified that I had the latest Jet engine I'll post what it is when I find it.
LVBarnes
* Yes, Access crashes and sends data to Microsoft about the crash.
* I hope it's in the code, because then it is fixable. (One of the reasons I got rid of all the yes/no data types.)
* The remote databases are Access as well, usually containing one or two very large tables. (Over a million records.) These are keyed and also have certain fields indexed.
* Regarding drivers, I just used Office Update and also verified that I had the latest Jet engine I'll post what it is when I find it.
LVBarnes
<<* Regarding drivers, I just used Office Update and also verified that I had the latest Jet engine I'll post what it is when I find it.>>
I would update MDAC (Microsoft Data Access Components) as well.
Also, when you get a crash, click for the details and start noticing the module and address where the crash occurs. That will usually get you started in a direction (Access itself, VBA problem, or data access).
Million records or not, you have an unusual situation.
JimD.
I would update MDAC (Microsoft Data Access Components) as well.
Also, when you get a crash, click for the details and start noticing the module and address where the crash occurs. That will usually get you started in a direction (Access itself, VBA problem, or data access).
Million records or not, you have an unusual situation.
JimD.
ASKER
If you are interested...just crashed again. Screenshots attached. Should I open another ticket to review this?
Error1.jpg
Error1.jpg
OK, so the crash occured in the Jet Expression Service. That means it is defintely related to data operations in some way. The offset was 00006fc9, which is where the error occured.
My suggestion would be to let it crash once or twice more and see if the module and offset are the same. I would also try and pin down if it is some specific action (ie. deleteing child rows).
Then I would call Microsoft as this is no doubt that this is an Access bug. I found references on the net in two places with the same error message, but no resolutions were posted.
I also checked for hotfixes and only found one that might be related. What you said "project" is this an Access project in that your as using SQL Server for the database engine (an ADP) or is it a normal Access app where your using JET (a MDB/MDE)?
JimD.
My suggestion would be to let it crash once or twice more and see if the module and offset are the same. I would also try and pin down if it is some specific action (ie. deleteing child rows).
Then I would call Microsoft as this is no doubt that this is an Access bug. I found references on the net in two places with the same error message, but no resolutions were posted.
I also checked for hotfixes and only found one that might be related. What you said "project" is this an Access project in that your as using SQL Server for the database engine (an ADP) or is it a normal Access app where your using JET (a MDB/MDE)?
JimD.
BTW,
this is the first hot fix that may be related:
http://support.microsoft.c
Your on build 8166 of Access, so this might take care of what your seeing.
and this is the second that corrects things broken in SP3:
http://support.microsoft.c
JimD.
this is the first hot fix that may be related:
http://support.microsoft.c
Your on build 8166 of Access, so this might take care of what your seeing.
and this is the second that corrects things broken in SP3:
http://support.microsoft.c
JimD.
ASKER
Hi Jim,
I'm just using .mdb/.mde for now. No SQL server available.
Thanks for the tips. How do I get points to you?
LVBarnes
I'm just using .mdb/.mde for now. No SQL server available.
Thanks for the tips. How do I get points to you?
LVBarnes
<<I'm just using .mdb/.mde for now. No SQL server available.>>
OK.
<<Thanks for the tips. How do I get points to you?>>
At the bottom right of all the comments, you'll see a button "Accept as solution". Just click that and you can then close the question that way.
JimD.
OK.
<<Thanks for the tips. How do I get points to you?>>
At the bottom right of all the comments, you'll see a button "Accept as solution". Just click that and you can then close the question that way.
JimD.
ASKER
I've had a few more crashes.. All with the same info.
AppName: msaccess.exe AppVer: 11.0.8166.0 AppStamp:46437912
ModName: msjtes40.dll ModVer: 4.0.9502.0 ModStamp:44859905
fDebug: 0 Offset: 00006fc9
AppName: msaccess.exe AppVer: 11.0.8166.0 AppStamp:46437912
ModName: msjtes40.dll ModVer: 4.0.9502.0 ModStamp:44859905
fDebug: 0 Offset: 00006fc9
ASKER
Regarding points...this question was closed a while back as it was related to ODBC vs VBA connections, thus the question above.
<<Regarding points...this question was closed a while back as it was related to ODBC vs VBA connections, thus the question above.>>
Sorry; I forgot. Don't worry about any points.
JimD.
Sorry; I forgot. Don't worry about any points.
JimD.
ASKER