Solved

Forms, Subforms and split database

Posted on 2006-06-08
10
373 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

733 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