MS Access question

[RonR]

Should multiple users be able to view data in an Access DB?

I know only one user can edit at a time.

I had two computers looking at data on an Access Db and 2 records became corrupted and neither were doing any editing.

I am using the Db on a local network, Win XP Pro.

any ideas???

[Tony Hine]

>>>Should multiple users be able to view data in an Access DB?<<<<

The best way to do this is to divide your database into a front end and back end, keeping the backend database on the server and distributing an individual front end to each user. This front end would be linked to the back end database via linked tables.

[Tony Hine]

>>>I know only one user can edit at a time<<<

Not strictly true, although you can have issues if different users try to edit a particular record at the same time. However access will try and handle a conflict situation For you automatically, this is assuming you have a front end backend arrangement as suggested in my previous post.

[Tony Hine]

>>>any ideas???<<<<

Some versions of MS Access have a database splitter wizard, this can be found from the tools menu:

Tools >>>
Database utilities >>>
database splitter

Alternatively it is a very easy process to split the database into to yourself, basically make two copies, in one copies delete all the forms queries etc, and in the other copy delete the tables.

Search the Internet for frontend backend for more information.

[Allan Murphy]

Tony

Using your method of copying etc. you will also need to link the tables.

[Tony Hine]

Yes, good point Allan, I assume the wizard does it automatically (linking), not sure. (don't use it myself)

[RonR]

Vb is the only UI for my DB, I do not use any Access feature.
I used Access to create the DB but that is all.


I think I will "Mirror" the DB, in other words I will automatically copy the DB to the "Mirror" folders when the data entry person exits my program(provided the Db has been edited since the last exit).

the three users(view only) will view the "Mirrored" database.

most of my customers are NOT computer savy, anything I do has to run on its own.

[RonR]

>>>any ideas???<<<<

Some versions of MS Access have a database splitter wizard, this can be found from the tools menu:

Tools >>>
Database utilities >>>
database splitter

Alternatively it is a very easy process to split the database into to yourself, basically make two copies, in one copies delete all the forms queries etc, and in the other copy delete the tables.

Search the Internet for frontend backend for more information.

if the table is deleted, the data would be gone. I do not understand.

[Tony Hine]

if the table is deleted, the data would be gone. I do not understand.

If you are using Vb as the UI then comments are not relevant. My comments are only relevant to MS Access.

I don't know if this is possible through a VB front end, I would suggest you try a VB forum.

[RonR]

ok.

thanks for the info.

[Allan Murphy]

Tony

The Database Splitter creates the linking during the splitting of the database. When you make a "mirror" as per your method you will need to do the linking otherwise you will have a database with the forms etc and no linking to the tables in the other database. You will need to use the File, Get External Data, Link Table menu item

[RonR]

I thought this was a VB forum.


sorry.

[Tony Hine]

Don't worry, easy done Ron.

If it is possible to link multiple VB front ends to MS Access tables, then I would be most interested to know this. If you would be good enough to post your findings back here I would appreciate it.

[RonR]

I can open multiple tables with VB, using only one line of SQL for each table.

do you use VB 6?

[Tony Hine]

>>>do you use VB 6?<<<

Had a play with it a couple of years ago, however I went over to VB.Net....

I have just downloaded the latest version VB.Net 2008 Express (it's free) and it also includes a compact version of SQL server that you can publish with your applications. I just wondered if that might be of interest.