Split database & Multiusers

[Dream.Van]

Hi

I've split my database to have 2-3 users access it at the same time over a network.

The properties of the FE & BE are on shared but when I want to open a second copy of the FE it says that my BE is currently being used by another. All locks are set to none.

Using Access 2007. The one person that is using it continually adds reccord. The others are mostly wanting to use the search form.

Are there any other properties that i've missed?

Vanessa

 

[Crowbar]

Hi Vanessa,
I am by no means an expert, in fact I sponge off the experts here as much as I need to, but I have a few DB's set up with FE and BE. Here's how I do it:
Database is split, BE in a server folder with proper permissions
FE is on each users workstation.
In the Linked Table Manager, I use the UNC (\\servername\BE folder) for the location of the BE.

I hope that helps you out until a real expert comes along.

 

[boblarson]

Is there a COPY of the Frontend on EACH user's machine? If not, it needs to be.

 

[Dream.Van]

Thanks a lot for your reply
I know for sure that my BE is accessible to everyone on the server and that my FE is properly linked. I'm guessing the problem is a property option that I'm missing somehow.
Just for specification,
I open the FE (on a different computer with a different copy) but as I click on my buttons to open the forms and repports or even one of the linked tables a message appears : Cannot access N:/..../.../..._BE. Currently in use.

Yes Bob there is a different copy on each computer

 

[Steve R.]

Like Crowbar, I am not expert enough to identify your problem. We had an Access front-end and Access back-end that worked with approximately 6 users. Over-time, as more records were added, the database became balky. Fortunately, our office had Microsoft SQL-Server and we migrated the back-end to MS SQL-Server. Since then, performance has been much better.

With that in mind, the purpose of my post is to suggest using Microsoft's SQL Server Express, which is free according to the webpage. I don't know if installing SQL-Server Express would be worth the effort for you. Additionally, since I have not used SQL-Server Express, I can't say if it is good or bad.

 

[boblarson]

Check the forms because I had someone else have that same problem and they had the Record Locks property of the form set to ALL RECORDS.

 

[boblarson]

Also check the default Record locking for Access in the ACCESS OPTIONS. It should be

 

[Dream.Van]

I've rechecked all my forms and even my queries and all the lock properties are off. That goes for the one in the access options too.

However the "Open databases by using record-level locking " box isn't on. Does that change anything in my situation? Edit: No it's still not working.
Could it be the way I split it? I used the wizard.

 

[boblarson]

Does everyone have read/write/delete permissions on the folder where the backend resides? Can you upload a copy of the frontend and maybe an empty copy of the backend? (use this free tool of mine to empty it if you want).

 

[Dream.Van]

Yes everyone on the server is read/write/delete to the folder

My file is way too big even with all the data gone... actually it didn't go down when I took all the records out. I'm at 10,6 MO
Your tool to reset the database didn't work. I think the browse's default location isn't avaiable on my computer

 

[boblarson]

You have to run Compact and Repair for the file size to get smaller. It doesn't reclaim the space until you run the Compact and Repair feature from the Round Office Button. Then you would ZIP it by right clicking on an individual file and selecting SEND TO > COMPRESSED FOLDER.

 

[boblarson]

Also for the reset tool to work you must either put it in a trusted location or you also need to enable the code by clicking the button on the bar below the RIBBON when you open it. I just downloaded and tested with my Access 2007 and it works fine.

 

[Dream.Van]

Ok got it

Here's the BE
The FE is 185 MB... I'll take out as much as I can

 

[Dream.Van]

Never mind I hadn't compressed it so here's the hole thing

 

[boblarson]

So just a quick question - so you are sure everything updates fine? Looking at the relationships it looks like you have duplicated fields in some circumstances. But also, you really don't need to select CASCADE UPDATES because your ID fields are not going to be changing as they are autonumbers. Cascade updates do not do anything except make changes to foreign keys if primary keys change and autonumbers can't be changed (technically speaking).

Also, get rid of lookups defined at table level. You currently have, for example, ones for Subject, SubjectDobso, Layout, and LayoutDobso in the Documents Table.

But I don't see anything that should keep you from using it with more than one person. Have you and everyone else set up the backend and frontend locations as trusted locations on EACH computer?

 

[Dream.Van]

Ok for the cascading updates. I don't see what you mean by duplicated fields. I know they are bad and I avoid them completely. Everything works fine.

For the lookups in the tables do you mean the multi select combo boxes? I don't know if those are a good thing but there exactly what I need for those field (SubjectObso and layoutobso are temporary fields.. I have to add manually to subject and layout because they are multi select fields(in documentsform5))

I'll keep testing things out. When you say every one needs a read/write/delete right, you said on the folder but does it have to be on the access BE file too?

 

[Galaxiom]

Check out the Opportunistic Locks and Sharing Violation Delay registry values on the server.

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\lanmanserver\parameters]
"EnableOpLocks"=dword:00000000
"SharingViolationDelay"=dword:00000000