Here is my problem, I have an Access2010 database split into FE&BE. BE is hosted on the server in a folder on drive E. Selected users have the FE on their PC to access the BE and do normal activities like inserting, updating and reporting. A web application
done using ASP.net access the backend database for public users through the LAN.
Everything works fine, but sometimes for unkown reasons I get the error shown below, however, the FE does not get effect by this error.
If I don't do anything for 5 to 10 minutes, the web appliction somehow works ok and the error disappears. To do a quick fix, I open IIS and disable then enable windows authentication option and then the problem is solved right away.
Thanks for your support and help.
Server Error in '/' Application.
<div align="center">
</div>
Unspecified error
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Selected users have the FE on their PC to access the BE and do normal activities like inserting, updating and reporting.
Most likely, tha FE is not build to only open the database connection, execute some queries and then close the connection as soon as possible. When a user opens the FE, the .laccdb lock file is created and will be there untill all FE's are closed normally.
This is the reason many peoople give Access a bad name, because this is the wrong way to use Access in a multi user environemnt.
When you want to use Access in a mutliuser environment, you must open the database connection a late as possible, perform the SQL and close it as soon as possible. Always make sure the connection is closed properly, by adding error_handling to your code.
In ASP.NET, you can use the Using syntax to make sure the connection is always closed and disposed, even without adding error handling
Do you mean that I should not use Access database for web applications and FE users at the same time?
Yes you can, But when you build an Access Application using all the build in wizards, and create an frond and backend databasem, you; re asking for trouble, even when you don't want to use the data in a web application. Acces can be used as a database in
multi user environments but you need to build it just like you build a web application. Open the database as late as possible, close it as soon as possble. Only use action query's to modify or delete data, only use disconnected recordsets....
A web application is multo used, I've build many multi user applications (Windows, web and a combination of both) with Access without any problems. You only need to know how to use the tools that are avalable. In Access, that means that you don't use the
build in wizards but you need to write ALL data access code in VBA
Open the database as late as possible, close it as soon as possble. Only use action query's to modify or delete data, only use disconnected recordsets....
Thanks hans_v,
What I understood is that I should not link the FE with BE using linked tables, instead I should use VBA to run query on BE using ADO/DAO libraries to get the data required and disconnect.
What I understood is that I should not link the FE with BE using linked tables, instead I should use VBA to run query on BE using ADO/DAO libraries to get the data required and disconnect.
But as far as I see, Access db should be set to "Shared" with some specific connection string, otherwises it will be only single taken by someone.
No, it is possible to have multiple connection to an Access database. The problems occur when a user opens a recordset, then one or more tables get locked.
Decker Dong - MSFT
And what's more, compared with SQL, Access isn't easy to control with problems such as concurrency……
Why not? Simply add a column with a guid. When you update the record change this value to a new column, In the wher clause clause of the update of delete command, you just need to check if this column ihas still the same value. If not, someone else has changed
the record after you last retrieved it!
Decker Dong - MSFT
So if possible, SQL Express is still a good choice.
Of course, but I still like to work with Access too. The big advantage is that it works on every windows computer (when using mdb files), without the need to install anything.
The problems occur when a user opens a recordset, then one or more tables get locked.
Yes, that's the problem.
Compared with this, I think SQL can let us access the table parallely without locking the table.
hans_v
Of course, but I still like to work with Access too. The big advantage is that it works on every windows computer (when using mdb files), without the need to install anything.
Haha……But just a reminder to OP——If you haven't installed something about Access，maybe he/she has to install Office Access components such as this：
Haha……But just a reminder to OP——If you haven't installed something about Access，maybe he/she has to install Office Access components such as this：
And is this according to the license?
But to use a mdb file as a database, you don't need anything, except the OleDb 4.0 driver, which is installed on any Windows System (desktop or Server). Using a Access 2007 or higher database file needs the ACE driver, whicg isn't installed by default. But
there's no real advantage using an accdb file over a mdb file (or do I miss something?), so I always use mdb files.
Compared with this, I think SQL can let us access the table parallely without locking the table.
Yes, Sql Server will most likely handle that. But fo you consider this a good practice? The advantage of using Access that it more or less forces you to use good practices, otherwise you'll have problems. If you can work with Access, you can work with other
databases as well.
Compared with this, I think SQL can let us access the table parallely without locking the table.
Yes, Sql Server will most likely handle that. But fo you consider this a good practice? The advantage of using Access that it more or less forces you to use good practices, otherwise you'll have problems. If you can work with Access, you can work with other
databases as well.
SQL Server can definitely access data without locking the rows.
However, remember you still do need locking if you're accessing data that changes often (for example, people are adding orders every 3-4 seconds and you want to run a report on all orders)
Open the database as late as possible, close it as soon as possble
Many thanks hans_v for the tip. Just to update you and any one else visiting this thread.
I reviewed all my asp codes and found a couple ofDataReader and database connection that were not closed immediately. When I added the code to close them, the website/application has been working fine with
no errors for a couple of weeks now.
None
0 Points
8 Posts
[solved] System.Data.OleDb.OleDbException: Unspecified error
Jan 24, 2013 05:54 PM|barmajiat|LINK
I'm having the same problem, did anyone find a solution? Thanks.
All-Star
94120 Points
18111 Posts
Re: [solved] System.Data.OleDb.OleDbException: Unspecified error
Jan 25, 2013 10:17 PM|Decker Dong - MSFT|LINK
Hi,
Your description is too generic……So what codes have you written?
At which statement the error is thrown out?
None
0 Points
8 Posts
Re: [solved] System.Data.OleDb.OleDbException: Unspecified error
Jan 28, 2013 01:55 PM|barmajiat|LINK
Dear Dong,
Here is my problem, I have an Access2010 database split into FE&BE. BE is hosted on the server in a folder on drive E. Selected users have the FE on their PC to access the BE and do normal activities like inserting, updating and reporting. A web application done using ASP.net access the backend database for public users through the LAN.
Everything works fine, but sometimes for unkown reasons I get the error shown below, however, the FE does not get effect by this error.
If I don't do anything for 5 to 10 minutes, the web appliction somehow works ok and the error disappears. To do a quick fix, I open IIS and disable then enable windows authentication option and then the problem is solved right away.
Thanks for your support and help.
Server Error in '/' Application.
<div align="center">
</div>
Unspecified error
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
OleDbException: Unspecified error
Exception Details: System.Data.OleDb.
Source Error:
Line 6: dim dbconn,sql,dbcomm,dbread
Line 7: dbconn=New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=e:\db\TIM1.0_be.accdb;Persist Security Info=False;")
Line 8: dbconn.Open()
Line 9:
Line 10:
Source File: C:\inetpub\wwwroot\2013\
Stack Trace:
[OleDbException (0x80004005): Unspecified error]
Microsoft.VisualBasic.CompilerServices.Container.InvokeMethod(Method TargetProcedure, Object[] Arguments, Boolean[] CopyBack, BindingFlags Flags) +202
Microsoft.VisualBasic.CompilerServices.NewLateBinding.CallMethod(Container BaseReference, String MethodName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, BindingFlags InvocationFlags, Boolean ReportErrors,
ResolutionFailure& Failure) +150
Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateCall(Object Instance, Type Type, String MemberName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, Boolean IgnoreReturn) +178
ASP.eoeg2013_tmp_visitors_aspx.Page_Load() in C:\inetpub\wwwroot\2013\tmp\Visitors.aspx:8
System.Web.Util.CalliHelper.ArglessFunctionCaller(IntPtr fp, Object o) +8
System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +8672611
System.Web.UI.Control.OnLoad(EventArgs e) +99
System.Web.UI.Control.LoadRecursive() +50
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +627
<div align="center">
</div>
Version Information: Microsoft .NET Framework Version:2.0.50727.4214; ASP.NET Version:2.0.50727.4209
All-Star
94120 Points
18111 Posts
Re: [solved] System.Data.OleDb.OleDbException: Unspecified error
Jan 28, 2013 08:17 PM|Decker Dong - MSFT|LINK
Hi,
Have you deployed this on IIS？Do you wanna make Access db shared by many customers?
All-Star
25756 Points
7014 Posts
Re: [solved] System.Data.OleDb.OleDbException: Unspecified error
Jan 29, 2013 09:08 AM|hans_v|LINK
Most likely, tha FE is not build to only open the database connection, execute some queries and then close the connection as soon as possible. When a user opens the FE, the .laccdb lock file is created and will be there untill all FE's are closed normally. This is the reason many peoople give Access a bad name, because this is the wrong way to use Access in a multi user environemnt.
When you want to use Access in a mutliuser environment, you must open the database connection a late as possible, perform the SQL and close it as soon as possible. Always make sure the connection is closed properly, by adding error_handling to your code. In ASP.NET, you can use the Using syntax to make sure the connection is always closed and disposed, even without adding error handling
http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access
None
0 Points
8 Posts
Re: [solved] System.Data.OleDb.OleDbException: Unspecified error
Jan 31, 2013 06:26 PM|barmajiat|LINK
Dear hans_v
Do you mean that I should not use Access database for web applications and FE users at the same time?
Thanks,
All-Star
94120 Points
18111 Posts
Re: [solved] System.Data.OleDb.OleDbException: Unspecified error
Jan 31, 2013 08:09 PM|Decker Dong - MSFT|LINK
for multiple users, you'd better use SQL instead.
All-Star
25756 Points
7014 Posts
Re: [solved] System.Data.OleDb.OleDbException: Unspecified error
Feb 01, 2013 06:56 AM|hans_v|LINK
Yes you can, But when you build an Access Application using all the build in wizards, and create an frond and backend databasem, you; re asking for trouble, even when you don't want to use the data in a web application. Acces can be used as a database in multi user environments but you need to build it just like you build a web application. Open the database as late as possible, close it as soon as possble. Only use action query's to modify or delete data, only use disconnected recordsets....
All-Star
25756 Points
7014 Posts
Re: [solved] System.Data.OleDb.OleDbException: Unspecified error
Feb 01, 2013 07:02 AM|hans_v|LINK
A web application is multo used, I've build many multi user applications (Windows, web and a combination of both) with Access without any problems. You only need to know how to use the tools that are avalable. In Access, that means that you don't use the build in wizards but you need to write ALL data access code in VBA
None
0 Points
8 Posts
Re: [solved] System.Data.OleDb.OleDbException: Unspecified error
Feb 01, 2013 07:44 AM|barmajiat|LINK
Thanks hans_v,
What I understood is that I should not link the FE with BE using linked tables, instead I should use VBA to run query on BE using ADO/DAO libraries to get the data required and disconnect.
None
0 Points
8 Posts
Re: [solved] System.Data.OleDb.OleDbException: Unspecified error
Feb 01, 2013 07:46 AM|barmajiat|LINK
Thanks Dong,
I'm pretty sure that SQL is a better option, but I would like to keep it as my last option.
All-Star
94120 Points
18111 Posts
Re: [solved] System.Data.OleDb.OleDbException: Unspecified error
Feb 01, 2013 08:04 AM|Decker Dong - MSFT|LINK
Hello;)
But as far as I see, Access db should be set to "Shared" with some specific connection string, otherwises it will be only single taken by someone.
And what's more, compared with SQL, Access isn't easy to control with problems such as concurrency……
So if possible, SQL Express is still a good choice.
All-Star
25756 Points
7014 Posts
Re: [solved] System.Data.OleDb.OleDbException: Unspecified error
Feb 01, 2013 05:26 PM|hans_v|LINK
Yes,. that is exactly what you should do
All-Star
25756 Points
7014 Posts
Re: [solved] System.Data.OleDb.OleDbException: Unspecified error
Feb 01, 2013 05:37 PM|hans_v|LINK
No, it is possible to have multiple connection to an Access database. The problems occur when a user opens a recordset, then one or more tables get locked.
Why not? Simply add a column with a guid. When you update the record change this value to a new column, In the wher clause clause of the update of delete command, you just need to check if this column ihas still the same value. If not, someone else has changed the record after you last retrieved it!
Of course, but I still like to work with Access too. The big advantage is that it works on every windows computer (when using mdb files), without the need to install anything.
All-Star
94120 Points
18111 Posts
Re: [solved] System.Data.OleDb.OleDbException: Unspecified error
Feb 01, 2013 08:38 PM|Decker Dong - MSFT|LINK
Yes, that's the problem.
Compared with this, I think SQL can let us access the table parallely without locking the table.
Haha……But just a reminder to OP——If you haven't installed something about Access，maybe he/she has to install Office Access components such as this：
For Access 2003 or below：http://www.microsoft.com/en-us/download/details.aspx?id=21373
For Access 2010：http://www.microsoft.com/en-US/download/details.aspx?id=13255
And Access2003's connection string is quite different from that of Access2007/2010:
You can see this:
http://www.connectionstrings.com/
None
0 Points
8 Posts
Re: [solved] System.Data.OleDb.OleDbException: Unspecified error
Feb 02, 2013 03:35 AM|barmajiat|LINK
Many thanks hans_v, I'll modify the FE as so, apperciate you help.
All-Star
25756 Points
7014 Posts
Re: [solved] System.Data.OleDb.OleDbException: Unspecified error
Feb 02, 2013 04:45 PM|hans_v|LINK
And is this according to the license?
But to use a mdb file as a database, you don't need anything, except the OleDb 4.0 driver, which is installed on any Windows System (desktop or Server). Using a Access 2007 or higher database file needs the ACE driver, whicg isn't installed by default. But there's no real advantage using an accdb file over a mdb file (or do I miss something?), so I always use mdb files.
All-Star
25756 Points
7014 Posts
Re: [solved] System.Data.OleDb.OleDbException: Unspecified error
Feb 02, 2013 04:50 PM|hans_v|LINK
Yes, Sql Server will most likely handle that. But fo you consider this a good practice? The advantage of using Access that it more or less forces you to use good practices, otherwise you'll have problems. If you can work with Access, you can work with other databases as well.
All-Star
94120 Points
18111 Posts
Re: [solved] System.Data.OleDb.OleDbException: Unspecified error
Feb 02, 2013 07:39 PM|Decker Dong - MSFT|LINK
Hi again,
It seems that you are an expert in Access, thanks for tipping;)
None
0 Points
1 Post
Re: [solved] System.Data.OleDb.OleDbException: Unspecified error
Feb 05, 2013 04:51 AM|KaraokeStu|LINK
SQL Server can definitely access data without locking the rows.
http://www.mollerus.net/tom/blog/2008/03/using_mssqls_nolock_for_faster_queries.html
However, remember you still do need locking if you're accessing data that changes often (for example, people are adding orders every 3-4 seconds and you want to run a report on all orders)
None
0 Points
8 Posts
Re: [solved] System.Data.OleDb.OleDbException: Unspecified error
Feb 15, 2013 08:25 AM|barmajiat|LINK
Many thanks hans_v for the tip. Just to update you and any one else visiting this thread.
I reviewed all my asp codes and found a couple of DataReader and database connection that were not closed immediately. When I added the code to close them, the website/application has been working fine with no errors for a couple of weeks now.
Thanks again and appreciate your help.