Database Splitting

[Matthieu]

I want to split a database and put front ends on multiple users computers (8-10 computers). I split the database I have created, on our shared network drive, and left the back end there. I then put copies of the front end on each users computer. I then tested to see if it would work by having each of them open forms and start inputting data. However, only the first person was able to open their form, the other users got an error saying the table (CallInfo) was already opened. I can't seem to find a setting that would fix this, and my understanding is that multiple users should be able to input data simultaneously. I tested it on my own computer and the network drive and it worked fine, so It does work for one user at a time. Any ideas on this? I looked through Using Microsoft Access 2007 but was unable to find anything to fix this.

Note- my database is not completely optimal in its design. I have two tables that get updated simultaneously from any of the Forms. They share information about a particular call and are linked by an autonumber on the first table as the Primary Key and a lookup on the second table as a foreign key, that matches the autonumber in the first table, to match them up as one row of data. I did this because otherwise the table was very large. Not sure if this matters, but figured I'd mention it.

[RuralGuy]

Each form has a [Record Locks] property on the Data tab of the property sheet for the form. How is yours set? You should also be using Queries bound to your forms and not directly to the tables.

[MAF4Fam6]

You should also be using Queries bound to your forms and not directly to the tables.

From my experience...what RG mentions above, is usually the culprit in cases like this. You should be building queries to use as the record source for your Forms. Always strive to make this the rule and not the exception.

-RC

[Matthieu]

It's locked to All Records. I will try change that and see if it works then.

I don't have my forms running through queries. I set it up to input the data directly into the tables and then I run queries and reports off of what is in the table. What kind of query do you set up for the forms to run from? Does the query input data into the tables? I use these forms solely for data entry, they are not needed to display information.

Thanks for the help!

[MAF4Fam6]

You will need to build a Select query for each of your forms. This is the default when creating a new query in Design view. Then, change the record source on each form to its respective query.
And yes...using a query as the record source on a form, will still update its underlying table.

-RC

[Matthieu]

So I created a query to run my forms through, but I created just one query for all the forms. I had to change the record locking, so I changed it from All Records to Edited Records. It's working now, although it's rather slow to load the forms and such.

Is that due to running it through one query, or is it unrelated?
It's submitting the data quickly once they get into the form, just takes a while to load the form. I figure it could just be the Network Drive i'm running the FE/BE database through.

Also, is it better to have No locks or Edited locks?
I'm having up to 10 agents entering in data through forms at once, and then I will be running reports off of that data.

Thanks for the help, it's been great!

[RuralGuy]

I would look into a Persistant Connection as described in this link: http://www.granite.ab.ca/access/performancefaq.htm
Lock for Edited Records is best.

[Matthieu]

Sounds good, Thanks!

[RuralGuy]

Glad we could help.