Multiuser Madness (advanced)

Hi all I have 3 horrible questions:
I am using an Access 2000 frontend with an Access 2000 db as the
backend.
I have built an automatic update feature that checks the front version
against the backend version and updates if needed.

Q1: I'm using TransferDatabase to import or link the forms, tables,
etc. I can't figure out the locking. I can't Dfirst or query a table
in one db that is being linked or imported by another db. This means
that while one user is trying to link to the backend, another can't be
using it. Is there a better way to do this than transferdatabase?

Q2: I tried just having a copy of the updated front end on a server so
that each frontend could just copy it and use it instead of the old
version. But 20% of the time the frontend connected extremely slowly
until I rebuilt it from scratch (importing all new stuff). Another
20% of the time the references don't work (they show up, but the code
won't compile) even though the library versions and paths are the same
- until I rebuild it from scratch.

I've spend a good 60+ hours one this and cannot get it solid 100% of
the time. Any suggestions you have would be greatly appreciated.
Thanks
Pachydermitis

This is a suggestion:

Assumptions:
Clients have a network drive that is mapped at startup and they all map to
the same drive letter.
The backend database contains the only the tables.
The front end database contains tables(links only), queries, reports, etc...

Process:
Put the backend and frontend database on the network drive, along with any
componenets (ActiveX ans so on)

Option A:
Put a batch file on the network drive that copies the database to the client
machine each time the user logs in.

Option B:
Create a simple program or simple database that will also recide in the
client computer, this file would not need to be updated just installed once,
the purpose of this file is to compare the dates of the frontend copies on
the server and the client machine, if the client is older than the server
then it uses the Shell function to run the batch file on the network that
copy and replace the file(s) on the client machine, otherwise it opens the
frontend and quits.

Hope it helps

"Pachydermitis" <dedejavu@hotmail.com> wrote in message
news:4f954dcf.0401151455.5f00df09@posting.google.c om...[color=blue]
> Hi all I have 3 horrible questions:
> I am using an Access 2000 frontend with an Access 2000 db as the
> backend.
> I have built an automatic update feature that checks the front version
> against the backend version and updates if needed.
>
> Q1: I'm using TransferDatabase to import or link the forms, tables,
> etc. I can't figure out the locking. I can't Dfirst or query a table
> in one db that is being linked or imported by another db. This means
> that while one user is trying to link to the backend, another can't be
> using it. Is there a better way to do this than transferdatabase?
>
> Q2: I tried just having a copy of the updated front end on a server so
> that each frontend could just copy it and use it instead of the old
> version. But 20% of the time the frontend connected extremely slowly
> until I rebuilt it from scratch (importing all new stuff). Another
> 20% of the time the references don't work (they show up, but the code
> won't compile) even though the library versions and paths are the same
> - until I rebuild it from scratch.
>
> I've spend a good 60+ hours one this and cannot get it solid 100% of
> the time. Any suggestions you have would be greatly appreciated.
> Thanks
> Pachydermitis[/color]

You'll find an updater at MVP Tony Toews' site,
http://www.granite.ab.ca/accsmstr.htm. It gets good reviews, but I had good
luck with the approach you used of versioning in the front and back ends,
and letting the user copy down the new front end, in several client-server
applications.

Larry Linson
Microsoft Access MVP

"Pachydermitis" <dedejavu@hotmail.com> wrote in message
news:4f954dcf.0401151455.5f00df09@posting.google.c om...[color=blue]
> Hi all I have 3 horrible questions:
> I am using an Access 2000 frontend with an Access 2000 db as the
> backend.
> I have built an automatic update feature that checks the front version
> against the backend version and updates if needed.
>
> Q1: I'm using TransferDatabase to import or link the forms, tables,
> etc. I can't figure out the locking. I can't Dfirst or query a table
> in one db that is being linked or imported by another db. This means
> that while one user is trying to link to the backend, another can't be
> using it. Is there a better way to do this than transferdatabase?
>
> Q2: I tried just having a copy of the updated front end on a server so
> that each frontend could just copy it and use it instead of the old
> version. But 20% of the time the frontend connected extremely slowly
> until I rebuilt it from scratch (importing all new stuff). Another
> 20% of the time the references don't work (they show up, but the code
> won't compile) even though the library versions and paths are the same
> - until I rebuild it from scratch.
>
> I've spend a good 60+ hours one this and cannot get it solid 100% of
> the time. Any suggestions you have would be greatly appreciated.
> Thanks
> Pachydermitis[/color]

> Q1: I'm using TransferDatabase to import or link the forms, tables,[color=blue]
> etc. I can't figure out the locking. I can't Dfirst or query a table
> in one db that is being linked or imported by another db. This means
> that while one user is trying to link to the backend, another can't be
> using it. Is there a better way to do this than transferdatabase?[/color]

Why would you need to use transferdatabase?
Since the front end size seldom exceeds 10-20 MB, the best (and
simple) way to update clients' frontends is to replace the whole
front-end mde files. On a 100Mbps network it would take less than
20-30 sec to do this.

[color=blue]
> Q2: I tried just having a copy of the updated front end on a server so
> that each frontend could just copy it and use it instead of the old
> version. But 20% of the time the frontend connected extremely slowly
> until I rebuilt it from scratch (importing all new stuff). Another
> 20% of the time the references don't work (they show up, but the code
> won't compile) even though the library versions and paths are the same
> - until I rebuild it from scratch.[/color]

Look at the answer for Q1
[color=blue]
> I've spend a good 60+ hours one this and cannot get it solid 100% of
> the time. Any suggestions you have would be greatly appreciated.
> Thanks
> Pachydermitis[/color]

You can spend about 30 minutes to do a good search on this NG and
other Access resource sites and may straight away find the solution

I personally used to update frontends by Tony's free utility at
http://www.granite.ab.ca/access/autofe.htm

Now I use this solution by Danny Lesandrini, much cleaner, and all
Access-native: http://www.databasejournal.com/featu...le.php/3286111

And last thing: your question is not "advanced" at all, I think

NB

Thank you all for your responses.
I did a miserable job asking the questions, let me try again.
I have an Access Database as a backend. I am using another as a
Access DB front end. The backend database only has tables in it that
the front end links to. There are multiple users. To update the
functionality of the database I only need to change out the front end.
Copying the front end database should be simplest way, but I ran into
two problems, hence my questions. Keep in mind that these problems
differ between companies and computers within the companies.

1: I have been having problems with occasional corruption (or
something) in the database - corruption that compact and repair won't
cure. Creating a new database and importing all the objects into it
does fix it though. It manifests itself in the database running at
about 1/4 speed.

2: I have also been having occasional problems with the references.
The database compiles, the reference paths are correct, but many of
the library's functions are unavailable. Re-establishing the
references solves this issue.

In order to compensate for the first two issues, when updating, the
old front end copies the new front end to the local machine and
imports all of its objects. (This also improves performance and
precludes any problems of two people updating their databases from a
single master at the same time). The problem is in importing the
linked tables. Since they are linked, they must come from the back
end and not from the new copied front end.

3: If someone is already using the back end, transferdatabase tries to
lock the table and causes an error. Is there another way to control
locking on transferdatabase since no data is in question? Is there
any other way to link tables?

4: I've thought of using adox, but how do I create a connection
between to databases that I am remote controlling from a third?

I hope I did a better job explaining this time.
Thanks again
P

Oh, and NB, perhaps now you will find this a little more challenging.
:)

> functionality of the database I only need to change out the front end.[color=blue]
> Copying the front end database should be simplest way, but I ran into
> two problems, hence my questions. Keep in mind that these problems
> differ between companies and computers within the companies.
>
> 1: I have been having problems with occasional corruption (or
> something) in the database - corruption that compact and repair won't
> cure. Creating a new database and importing all the objects into it
> does fix it though. It manifests itself in the database running at
> about 1/4 speed.[/color]

FE or BE corruption? I guess you refer to FE corruption

You should always have a master copy of the FE mde file on a network
shared folder (or FE mde files if you have different FE for different
users).

Users then copy that to their local folder

If a user's FE ever corrupts, just simply copy the fresh FE mde file
from the server's shared folder to replace it.

If network bandwith is not a problem and your FE mde file size is not
huge, make a batch file [that copies the new, fresh FE down to local
folder then runs it] everytime user double-click a shotrcut on their
desktop. Teach users to run the app only by double-clicking that
shortcut

[color=blue]
> 2: I have also been having occasional problems with the references.
> The database compiles, the reference paths are correct, but many of
> the library's functions are unavailable. Re-establishing the
> references solves this issue.
>
> In order to compensate for the first two issues, when updating, the
> old front end copies the new front end to the local machine and
> imports all of its objects. (This also improves performance and
> precludes any problems of two people updating their databases from a
> single master at the same time). The problem is in importing the
> linked tables. Since they are linked, they must come from the back
> end and not from the new copied front end.[/color]

When you import linked tables, the links are imported

But
If you have the new front-end, why not run it? why bother importing
objects into the old FE?

If you decide you do not need a fresh copy of the FE everytime users
run the app, the order here should be:

- User runs the FE
- The FE compare its version and the latest version on server
- If the two are different, the FE opens an update utility (another
mde file) before quitting
- This Update utility copies the FE on server to local folder
- The Update utility opens that new FE for user before quitting itself

NB