?
Solved

Forms, Subforms and split database

Posted on 2006-06-08
10
Medium Priority
?
382 Views
Last Modified: 2012-05-05
I am in the process of moving my database to a network so multiple users can share the data.  In doing so, I have split the database.  Once the database is split, I have the following problem(s):

I cannot run a report on one computer while a form is open on another.  Error message is along the lines of " the database engine could not lock the table because it is in use by another person".  I thought the point of splitting the database was to allow multiple users.

I then tried changing the recordset to "snapshot" from "dynaset" and record locks to "no locks" for both the form and subform.  This results in error message " database has been placed in a state by another user that prevents database from being opened or locked".  After I made that change, the other problem I noticed was on the form/subform, I was not able to add a record to the subform (add button was not usable).

Before I split the database I could get on it on the network and access all tables from both computers...I just couldn't run a report from a second computer while somebody was in a table on a first computer.   I split the database to resolve this issue( I thought).

 Any suggestions?
0
Comment
Question by:jostafew
  • 3
  • 3
  • 3
  • +1
10 Comments
 
LVL 85
ID: 16866491
First, make sure your users aren't opening the database exclusively. You can set this in the Tools - Options section.

All users must have read/write Windows permissions on the folder housing the data tables.

Your "database has been placed in a state by another blah blah" was likely due to the fact that you made design changes - shut down Access and restart, then start your database again and see what happens.
0
 
LVL 11

Expert Comment

by:donaldmaloney
ID: 16866649
jostafew,
HAd similar problem and did what LSM sugested.
Also made sure that the app was running local on the pc and not on the server.
Do you have the Data tables on the server and your application on both computers with only table links to the Server DB Tables?
So when the app runs it is running on the pc and not on the server?
Don

0
 
LVL 58

Expert Comment

by:harfang
ID: 16869676
Hello jostafew

While you are in the options box, as suggested by LSMC, also check the "default record locking" there. It would appear from the symptoms that the form is actually doing a full lock on the table(s) used.

(°v°)
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 3

Author Comment

by:jostafew
ID: 16872237
Hello,

Thank you everybody for your responses.  I have tried all of the above.  Here is a breakdown of my trouble shooting today:

2 users on two computers both with read/write permissions on the folder the front and back end are on:

open form on user 1 and user 2 -> both forms work, able to update and add etc.
open form on user 1 and open report on user 2 -> error "could not lock table..."

Changed form and subform to SNAPSHOT from DYNASET:

open form on User 1, open report on user 2 -> both work.
Leave report open on user 2, shut down form on user 1,reopen form on user 1-> form opens but can't make changes, adds etc.

Close report on user 2-> user 1 still can't do anything in form.
User 1 closes form, reopens-> stillcan't make any changes.

User 1 closes entire database and reopens-> still can't make any changes using form.
user 2 closes entire database- > both users still can't make changes using form.

Un-selected " open databases using record level locking" -> no change.  Both users closed database and reopened using file menu afterward - no change.

Changed form back to DYNASET from SNAPSHOT: -> original problem.

Tried putting front end on each PC-> appears to have the same issues.  

I think I have went full circle on this...any more suggestions.

0
 
LVL 11

Expert Comment

by:donaldmaloney
ID: 16872530
jostafew,

Maybe look at testing the tables that are required in the report.
See
http://www.experts-exchange.com/Databases/MS_Access/Q_20551120.html

Don
0
 
LVL 3

Author Comment

by:jostafew
ID: 16873597
I have no experience with VBA...I am looking at some online tutorials to bring me up to speed.  I think I may take a course in the near future.  
0
 
LVL 58

Accepted Solution

by:
harfang earned 500 total points
ID: 16873954
jostafew

Let's run this checklist once more:
 • database window menu "tools / options...", [Advanced] tab: "default record locking" (•) no locks
 • form in design view, property Recordset Type: Dynaset
 • just below, property Record Locks: No Locks
 • at the end, property Has Module: No [if yes, see below]
 • report in design view, property Record Locks: No Locks
 • property Has Module: No

If either the form or the report have a module, open it (menu "view / code") and search for the words OpenRecordset and Open. If one of these is found, post the line here.

If the form has subforms and if the report has subreports, the same checklist should be run on the subobjects.

Happy debugging!

(°v°)
0
 
LVL 11

Expert Comment

by:donaldmaloney
ID: 16874200
jostafew
Can you post the DB?  FE and BE?
Don
0
 
LVL 3

Author Comment

by:jostafew
ID: 16874361
Thank you very much everybody.  Harfang pointed something out that I didn't know until now.  I was only checking the form/subform for locks.  I didn't realize I had to check the report for locks as well.  Sure enough, once I changed it to "NO LOCKS" everything seemed to work the way I expected.  Thanks again.
0
 
LVL 58

Expert Comment

by:harfang
ID: 16874592
Welcome, glad you found it!
(°v°)
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Implementing simple internal controls in the Microsoft Access application.
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question