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)
barmajiat
Member
2 Points
9 Posts
Re: [solved] System.Data.OleDb.OleDbException: Unspecified error
Feb 01, 2013 11:46 AM|LINK
Thanks Dong,
I'm pretty sure that SQL is a better option, but I would like to keep it as my last option.
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: [solved] System.Data.OleDb.OleDbException: Unspecified error
Feb 01, 2013 12:04 PM|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.
hans_v
All-Star
35986 Points
6550 Posts
Re: [solved] System.Data.OleDb.OleDbException: Unspecified error
Feb 01, 2013 09:26 PM|LINK
Yes,. that is exactly what you should do
hans_v
All-Star
35986 Points
6550 Posts
Re: [solved] System.Data.OleDb.OleDbException: Unspecified error
Feb 01, 2013 09:37 PM|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.
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: [solved] System.Data.OleDb.OleDbException: Unspecified error
Feb 02, 2013 12:38 AM|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/
barmajiat
Member
2 Points
9 Posts
Re: [solved] System.Data.OleDb.OleDbException: Unspecified error
Feb 02, 2013 07:35 AM|LINK
Many thanks hans_v, I'll modify the FE as so, apperciate you help.
hans_v
All-Star
35986 Points
6550 Posts
Re: [solved] System.Data.OleDb.OleDbException: Unspecified error
Feb 02, 2013 08:45 PM|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.
hans_v
All-Star
35986 Points
6550 Posts
Re: [solved] System.Data.OleDb.OleDbException: Unspecified error
Feb 02, 2013 08:50 PM|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.
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: [solved] System.Data.OleDb.OleDbException: Unspecified error
Feb 02, 2013 11:39 PM|LINK
Hi again,
It seems that you are an expert in Access, thanks for tipping;)
KaraokeStu
Member
4 Points
1 Post
Re: [solved] System.Data.OleDb.OleDbException: Unspecified error
Feb 05, 2013 08:51 AM|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)