Best way to administer a database to multiple users

[snipe]

Hey guys,



What exactly is the best way to administer a database that is used by multiple users. Currently, we just have a bunch of access programs stored on a remote server that people can log into at will. The problems arise when I want to edit a database and someone is using it. I then have to call around to the store to make everyone finish what they are doing and log out. I have "test" versions of all of the programs that I go into to make the changes. Once the changes are complete, I make everyone log out of the real program, I jump in there and import all of the new queries, tables, etc that I made in the "test" database. Is there a better way? Does access have something built in for this?

We are using Access 03.

[pbaldy]

Lots of ways of updating front ends automatically. I use a local table in the front end and write a little VB6 app that checks the version on the server against the version on the local PC and copies down new versions automatically. My friend Bill uses script:

http://www.thatlldoit.com/Pages/howtosarticles.aspx

Bob has a utility:

http://www.btabdevelopment.com/ts/freetools

[June7]

Don't know about 'best' way but this is what works for me: http://forums.aspfree.com/microsoft-...ue-323364.html

[snipe]

Great. I'll look into the 3 options you guys listed. I appreciate the help!

[John_G]

Hi -

Having multi-user databases running off a server causes all sorts of problems, as you are finding out. Having multiple users in the same file, trying to use the forms, queries, reports, etc. almost always leads to a corrupt database eventually.

A suggested (recommended, actually) approach in these situations is to use a "split database". The actual data (tables), known as the back end, are in a database on the server, for common access.

The forms, queries, reports etc., are in another database file, known as the front-end - a copy of the front-end is on each user's PC. The front end data base links to the tables on the server. By doing this you can have a development copy of the front end on your own computer, and each time you want to issue an update you just distribute a new copy to each user.

HTH

John

[snipe]

Thanks John_G, that's what I have been reading in the 3 options that Paul and June7 mentioned.

I just went and got my IT departments thoughts on doing this and they had a few concerns.

All of my stores are in the DFW area of Texas, but our servers are in San Diego. The term server that everyone logs into is at the same location, San Diego. All of our access programs link to our sales system via ODBC. Our sales servers are also at the same server farm in San Diego. Running some of our big queries can take up to 10 minutes. By moving the front ends to our managers local machines and linking to BE in San Diego, we are concerned about the speed of the queries. What are your thoughts on this?

Also, my IT department it whining about having to put Oracle on the manager's machines. But too bad for them.

[John_G]

I'll admit I'm not terribly knowledgable on this sort of thing, but given your geographic distances, and the amount of data you are processing, you might want to look at upgrading the data (back-end) to something like SQL Server. Among other advantages, SQL Server can do a lot of the heavy query data crunching right on the server machine, sending only the results back to the requestor.

I'll leave it at that, and let more experienced folks here give you more infomation - I don't want to mislead you.

John

[June7]

Not sure it matters where the frontend resides, it still opens and runs in the local machine memory. Slow performance is mostly due to transfer of data between frontend and backend over remote connection.

[pbaldy]

If your front end file is on the terminal server, there shouldn't be a change in performance. I would recommend that each user have their own copy on the TS. When we had a TS environment, I used the logged-in user and copied the FE to a folder on the server with that name. I had quirky problems until I did that, despite what they will tell you in a TS environment that everybody is running their own copy anyway.

[NTC]

Whether the deployment is via Terminal Services or not - the architecture must be split. Each user must have their own FE which links to the common BE. In Terminal Services this means each virtual desktop must have its own FE.

Any attempt to have multiple users in an unsplit DB or in a single FE will corrupt - guaranteed. If you are doing it this way and it has not yet corrupted you have been lucky so far.

In a split db you must kick everyone out in order to alter the BE - but after initial design that becomes a very rare requirement. Since all code, objects, etc reside in the FE - one is typically deploying a new FE to users.

The link between FE and BE is stored in the FE. Therefore if there is uniform drive mapping / UNC - - one can link the FE to the BE before deploying it and no one else will have to relink.