Error 3051 encountered (1 Viewer)

S

sjslall

Registered User.
Local time
Today, 14:59
Joined
Oct 21, 2010
Messages
34
I have an accdb database which is being accessed via frontend in a networked environment.
Users have access to the networked folder (read and write: full access)

when they try accessing the database via the fromend, they get the error code # 3051: need permission to access or DB is locked for exclusive use.

Code:

Dim db as dao.database
dim rs as dao.recordset
dbpath="\\10.111.65.198\project\data.accdb"
set db=opendatabase(dbpath) <<this is where the error # 3051 is reported>>

Please assist
 
J

jal

Registered User.
Local time
Today, 14:59
Joined
Mar 30, 2007
Messages
1,709
To help figure out whether the DB isn't corrupt, try doing a compact and repair. If successful, it's probably not corrupt. Then try the DAO code again.

Also, I'm not familiar with "openDatabase" as such. I've heard of
- Workspace.OpenDatabase
- DBEngine.OpenDatabase

Therefore be sure to try it this way
Dim DBE as New DAO.DBEngine
Dim DB as DAO.Database
Set DB = DBE.OPenDatabase(dbPath)
 
P

Poppa Smurf

Registered User.
Local time
Tomorrow, 07:59
Joined
Mar 21, 2008
Messages
448
This may help you, I have a database with just a switchboard and menu items to access various databases on our network. This saves the user from having a short cut for each database.

When the button is pressed a module similar to below is called depending on the database.

j:\_site etc is the location of directory and database name.

The Shell Command opens access database.

Sub open_branch_attendance()
Dim app_name As String

app_name = "MSACCESS.EXE ""j:\_site data\COMMON\Databases\branch_attendance.mdb"""
Call Shell(app_name, 1)

End Sub
 
S

sjslall

Registered User.
Local time
Today, 14:59
Joined
Oct 21, 2010
Messages
34
I will check with the new database engine part
 
S

sjslall

Registered User.
Local time
Today, 14:59
Joined
Oct 21, 2010
Messages
34
I have updated the code with open the database via the workspace, but still get the same error message: 3051
I have checked the access to the shared folder.
multiple users can read and write to the folder.
Still getting the runtime error 3051

I can however access it from my system
 
boblarson

boblarson

Smeghead
Local time
Today, 14:59
Joined
Jan 12, 2001
Messages
32,059
So why are you

1. using DAO for this instead of linking the tables?

2. Using the machine's IP address instead of the computer name and share?
 
S

sjslall

Registered User.
Local time
Today, 14:59
Joined
Oct 21, 2010
Messages
34
1. I am using the external database as need to access the DB as backend. Using accdb access 2007 database as frontend and backend.

2. The network shared drive is on a ip address. I need multiple users accessing the database

3. I get the error when accessing the database:
Code:

dim db as dao.database
dim wrkspc as dao.workspace
dim dbpath as string
set wrkspc=createworkspace("wrk","admin","",dbusejet)
dbpath="\\192.168.0.5\project\project.accdb"
set db=wrkspc.opendatabase(dbpath) >>> this is where the error is reported

Do you think using ADO will resolve the issue?
 
boblarson

boblarson

Smeghead
Local time
Today, 14:59
Joined
Jan 12, 2001
Messages
32,059
sjslall said:
1. I am using the external database as need to access the DB as backend. Using accdb access 2007 database as frontend and backend.
Click to expand...
Yep, just link the tables. Save yourself this hassle.
 
S

sjslall

Registered User.
Local time
Today, 14:59
Joined
Oct 21, 2010
Messages
34
I think the link of table may work or might not work as we are trying to link the tables and use the front end in multiuser environment, but I will try to do it.

My main job here is to get the database to connect using either DAO or ADO

Surinder
 
boblarson

boblarson

Smeghead
Local time
Today, 14:59
Joined
Jan 12, 2001
Messages
32,059
sjslall said:
I think the link of table may work or might not work as we are trying to link the tables and use the front end in multiuser environment, but I will try to do it.

My main job here is to get the database to connect using either DAO or ADO

Surinder
Click to expand...
Linking the tables uses the built-in DAO. As for multi-user usage, each user needs to have a COPY of the frontend on THEIR machine. Do NOT attempt to use it from a centralized location. See here for more about why you split and how to use it.

Linked tables work fine in a multi-user setting. I currently work for a large bank and we have linked SQL Server tables as well as Access tables in backends (but each user has a distinct copy of the frontend) and it works just fine.
 
S

sjslall

Registered User.
Local time
Today, 14:59
Joined
Oct 21, 2010
Messages
34
Really great read and thanks on the update that you are using the same.
I will try to have the linked table part taken care of.

Now an update from my end.
I have got all the users a copy of the front end on their workstations and they still get the same issue.

I agree using the linked tables would be a great idea, but would love to have the feature of connecting using DAO/ADO to a remote database.

Just a small help required:

After I have linked the tables, do I use currentdb() to connect to the local linked db or some other code. Please assist with this.

In the meanwhile, please someone can help us to use DAO/ADO on remote database connect.
 
S

sjslall

Registered User.
Local time
Today, 14:59
Joined
Oct 21, 2010
Messages
34
OK current status on this and how it has been resolved:

1. I had created a linked frontend and the database got connected. Used DAO for data access.
Still unable to connect to the external database on networked drive using DAO's opendatabase method.

2. Was able to connect to the external networked Access 2007 database on networked drive using VBA and ADO.

Code:
Dim DB As ADODB.Connection
Dim rs As ADODB.Recordset

Set DB = New ADODB.Connection
Set rs = New ADODB.Recordset

DB.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=<path to the networked accdb file>;Persist Security Info=False"
DB.Open
rs.Open "select * from kb_table", DB
Do While Not rs.EOF
List1.AddItem rs![kb_sec]
rs.MoveNext
Loop
DB.Close

Anyone can provider a better way to connect using ADO pls?
 
J

jal

Registered User.
Local time
Today, 14:59
Joined
Mar 30, 2007
Messages
1,709
What do you mean by "better"? In what ways are the current connections unsatisfactory?
 
S

sjslall

Registered User.
Local time
Today, 14:59
Joined
Oct 21, 2010
Messages
34
Well I am new to ADO VBA, so felt like a child.
Anyways I have a question.
is there a way to put the data source as a separate like:
db.data source=dbpath
I tried this and got an error that the database is not open
 
J

jal

Registered User.
Local time
Today, 14:59
Joined
Mar 30, 2007
Messages
1,709
I believe It's possible to write an ADO connection string in 2 different stages like this:

Dim cn as New ADODB.Connection
Cn.Provider = "Microsoft.Jet.OLEDB.4.0"
Cn.ConnectionString = "Data Source=C:\Northwind.mdb;"

but I don't see any compelling reason to do it that way.
 
S

sjslall

Registered User.
Local time
Today, 14:59
Joined
Oct 21, 2010
Messages
34
Worked like a charm....thanks.
Was looking for this way as would like to locate the accdb file and then add the dbpath to the connection string with: &dbpath
 
You must log in or register to reply here.

Similar threads

R
  • Question
Save the names of tables, forms, and reports in a Microsoft Access database to a table named "ObjectNames.
2
Replies
32
Views
990
spaLOGICng
S
S
Issue with Access Data Right after saving
Replies
11
Views
598
jdelano
J
N
Solved Scraping Json from a web using VBA
Replies
11
Views
540
isladogs
isladogs
Reshmi mohankumar
Solved Unable to Insert/Update DB
Replies
3
Views
491
Reshmi mohankumar
Reshmi mohankumar
W
Trouble Accessing Linked Tables in Access 2021 via OLEDB Connection: 'Could Not Find Installable ISAM' Error
Replies
6
Views
666
wardah
W

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom