Solved

Forms, Subforms and split database

Posted on 2006-06-08
10
369 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 84
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
 
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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 125 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

762 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now