• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 375
  • Last Modified:

Getting Error Message using DAO Object for Access.

Hi Team,

The given below code gives an error when more than one person access the database using this code.

The Database is getting locked.  Can you please suggest a possible solution for this.  I want multiple users to be able to access the same table when using the below code.  Please suggest changes.

I am guessing I am going wrong in the lock type.

Sub Testing()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    Set db = OpenDatabase("Test.mdb")
    Set rs = db.OpenRecordset("tblRecevied", dbOpenTable)

    rs.Close
    db.Close

end Sub

Open in new window


Screen Shot of the Error Message
0
Sandesh555
Asked:
Sandesh555
  • 4
  • 4
  • 3
  • +2
1 Solution
 
Dale FyeCommented:
Why not just create a link to that table on each of the machines, using the TransferDatabase method?
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
1.  Make sure that all users have read, write, and delete priv for the directory where the DB resides.

2. Make sure that all users are opening the DB in shared mode (tools/options).

Jim.
0
 
BullmanTechCommented:
Try adding the following to the end (after closing the database and recordset):

Set rs = Nothing
Set db = Nothing

Open in new window


This still wouldn't allow for truly concurrent access, this is just me thinking there might be a handle still holding onto the DB until the variable is completely gone.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
shambaladCommented:
Please do not award points to this entry as Jim, above, already gave you this answer (he beat me to it). Consider this is an elaboration on his entry.

In Access 2003, this is the entry you would change to open the DB in Shared Mode:
Open Share - 2003
In Access 2007/2010, this is the entry you would change to open the DB in Shared Mode:
Open Shared - 2010
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Actually, were a little off base<g>; the code is using an OpenDatabase method, so the tools/options doesn't come into play here.

The default open mode for OpenDatabase is shared, so it's permissions.

Everyone needs full privs for the creation/deletion of the .LDB file.  If Jet can't create it, the DB gets opened exclusive even if you've requested it to be shared.

Jim.
0
 
Sandesh555Author Commented:
Hi team,

I checked the defualt Open Mode and Jim is right.  It is by default "Shared".

Please advise how do I proceed..

Please note the codes are running on Excel VBA.

Thanks,
Sandesh.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<Please advise how do I proceed..>>

 Check and make sure all users have full security for the directory for the diretory where the DB resides.

Jim.
0
 
Sandesh555Author Commented:
The error message comes up at random  to all users.  I thing its because more than one person. Is setting connection at the same time.


When they hit end and try again it works.
0
 
Sandesh555Author Commented:
I am only guessing here.
0
 
Dale FyeCommented:
Are you getting the error on line 6 (opening the db) or on line 7 (creating a recordset based on the table)?

What are you trying to do with the recordset once it is open?  It may be that you could use one of the other options (other than dbOpenTable).
0
 
Sandesh555Author Commented:
I am getting the error at line 6.
0
 
Dale FyeCommented:
You might want to consider putting the full file name, including the folder.  My guess is that this file is on a shared server.  To use it the way you have it, I believe the current drive (on the system where the Excel file is located) would have to be the same as the path to the Test.mdb file.

And since each of your concurrent users probably has their own Excel file that is trying to open this file, the path to that file would have to be explicitly identified.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
So did that actually solve the problem or not?  If not, I would rather see the question deleted.

 If you needed additional info or the problem is not yet resolved we would have been glad to help further (curious why the "C" grade).

Jim.
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 4
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now