Allowing users to link to back-end database

[DataMiner]

Hi,
I have several users who want to link their User.mdb to my BE.mdb backends. Seems like a reasonable request. However, I am reluctant to allow this because it severely compromises my ability to do maintenance work: For example, if a user is linked to the BE.mdb, I can't repair/compact it, and I have no way of figuring out who is linked to it, either. For the Frontends that I build, I control this by building in a system that (1) writes to a table every time a user logs in or out, so I can figure out who's logged in when I need to, and (2) allows me to send an auto-shutdown command to the FE when necessary.

So, what's the safest way to allow users the use of my BE data? HEre are the possibilities I can think of:

1. Use FileCopy to create a backup copy of the BE.mdb periodically, and have user link to the Backup_BE.mdb PRoblem here is that Filecopy won't work if BE.mdb is in use, which it may well be.

2. Give user a piece of code to place in his DB, which will use docmd.transferdatabase to import the desired tables from BE.mdb into User.mdb periodically. I believe this would work whether BE.mdb is in use or not.

3. Put code in my FE.mdb using docmd.transferdatabase to export tables from BE.mdb to User.mdb periodically.

4. Put code in my FE.mdb using a make-table query to transfer data from BE.mdb to User.mdb. (If the table name already exists in User.mdb, this will fail, thought, won't it?)

5. PUt code in my FE.mdb utilizing a delete query followed by an append query to refresh data in the tables in User.mdb.

6. Other ideas?

Would appreciate any discussion as to the pro's and cons of these.

Using Access 2002

Thanks

 

[MarkK]

To me this completely depends on what your "several users" need to do. If they need totals maybe you can connect, query, and disconnect from the backend in less than a second. If they need to modify data they can't work on a copy. Do they need to be active once a week? Once every ten seconds? Each of these calls for a different solution.

 

[DataMiner]

Sorry, I should've been a bit clearer. Users will not need to modify data, only analyze it. I don't want to deal with what analyses they may want to do, so I want to just furnish them with fresh copies of all the pertinent tables periodically, then they can massage data to their heart's content.

 

[DataMiner]

And, I should've added, by "periodically" I mean once a day or so.

 

[KeithG]

If you want to know who is in your db you can open the .IDB file that is create in the same directory as your back end db with Microsoft Word and it will tell whthe machine name of every user in the db and what account they are logged in under. If you are not using workgroup security it will always say ADMIN.

 

[Dennisk]

there is a lock file viewer called LDBView.exe available as a free download from the MS development centre

 

[DataMiner]

Unfortunately I have found the .ldb files to be notoriously unreliable for this. In a production environment, it's impractical to maintain user names for every hourly worker on the manufacturing floor. So I have multiple users logged in with the same name. In addition, we're running on terminal servers so the .ldb file only records the name of the server, NOT the PC; I can't determine the physical location of who's logged in. Added to all this is the fact that the .ldb file seems to record the info when someone logs in but does not always subtract them when they log out. It is very common for me to find .ldb files that indicate I'm still logged into a db that I haven't used in days.

 

[Wiz47]

Unfortunately I have found the .ldb files to be notoriously unreliable for this. In a production environment, it's impractical to maintain user names for every hourly worker on the manufacturing floor. So I have multiple users logged in with the same name. In addition, we're running on terminal servers so the .ldb file only records the name of the server, NOT the PC; I can't determine the physical location of who's logged in. Added to all this is the fact that the .ldb file seems to record the info when someone logs in but does not always subtract them when they log out. It is very common for me to find .ldb files that indicate I'm still logged into a db that I haven't used in days.

One of the backup methods we use is to filecopy the .be from the server and send it to a folder on my harddrive when a user uses the backup option on their fe. It's just one of many layers of BU that we use. I'm thinking you could modify that method a bit and filecopy the .be to whoever you wanted using either code from your end or make that an option on their fe. That way they could query the data all they wanted (offline) and it wouldn't have an effect on the current data in the live .be tables.

 

[Bobby1st]

This got my interest and made two databases and try the docmd.transferdatabase and this is the Error results, "The type isn't an installed type or doesn't support the operation you chose." On the Visual Basic window under the menu Tools, I tried to search for the program that I need but failed. Where can I find this type to install? I am in a company's network, could that the cause of restriction? Thank you.

 

[DataMiner]

Bobby1st, can you please post the exact command that you tried to use?

Wiz47, Filecopy the backend is now the method that I'm using. Thanks.

 

[The_Doc_Man]

In general, when you have this kind of requirement, your best (in the sense of most practical) solution is to determine a good time for it to happen, then you do this:

Build a query that contains the desired data, possibly with FORMAT functions in some of the fields if specific formatting is required.

Export the query to a file on a shared drive.

Let these users copy whatever they want from the file.

You can look up ways to automate this problem by searching the forum for keywords such as Windows Task Scheduler and variants thereof.