Batch file for updating local frontend Access database copy

In my opinion, the biggest disadvantage of a loader access database is that you first need to have a running Access environment on the client, otherwise nothing works. With a batch file or your own exe (which is how we do it), this is not necessary.

In addition, we always copy the front end from the server to the client each time it is called up. This ensures that the client always has an up-to-date, decompiled version.
Our executable for example also checks whether Access is 32-bit or 64-bit on the current client and copies the correct accde library.
 
In addition, we always copy the front end from the server to the client each time it is called up. This ensures that the client always has an up-to-date, decompiled version.
Don't forget - no accumulation of bloat;)

And the decompiled point is important since it always ensures that querydefs will have the best execution plan available based on the current state of the BE.
 
That is the first time I have seen decompiled mentioned.?
 
That is the first time I have seen decompiled mentioned.?
I guess you've never seen any of my comments when I post a batch file solution. Many people think it is more efficient to not download a new version each time you open the FE but I always list several reasons why that is immaterial.

If you never use querydef's and always use embedded SQL, you force your embedded SQL to be compiled every time it runs because someone has to calculate an execution plan. Doing a C&R and not compiling the code, ensures that the first time a querydef runs, the execution plan is calculated and SAVED. That way as the statistics for the BE change, you are assured of always having the most efficient plan and because the querydef saves the plan, it is reused, at least for the day..
 
The reason we value a decompiled version of the accdb is that we have had problems in the past when different versions of Access were running on the clients. Some locations were unable to use the precompiled code of a (non-decompiled) accdb and refused to run but threw errors. With a decompiled accdb, each clients Access can compile the code itself.
 
That way as the statistics for the BE change
In what way, that affects the need for a decompile, other than that mentioned in #25?
I always supplied a compiled version.
 
In what way, that affects the need for a decompile, other than that mentioned in #25?
I always supplied a compiled version.
As I said, when the db is compiled, so are the querydefs if they were left that way. You want the querydefs to be uncompiled to force them to obtain new statistics and recalculate the execution plan each day.

Here are a couple of excepts from probably the best technical MS Access book ever written. Too bad there isn't an ACE edition. This is from my third copy of the book. The first was stolen, the second was lost to a fire and so I bought it again about 5 years ago. It was published in 1997 and still makes fascinating reading. The first two copies were $49.99 each, the last one was $5.00 I think and worth every penny. Get a copy if you can locate one.
 

Attachments

What book Pat? Nearly finished my second book on Access - MS Access Programming Inside Out. Very good but the section on classes could have had better examples IMHO.

probably the best technical MS Access book ever written.
 
Last edited:
I guess my reference was too obscure. The name is in the document at the footer with the page number and also in the title of the document.

"Jet Database Engine Programmer's Guide" Dan Haught & Jim Ferguson, Microsoft Press, 1997

This isn't even for the last version of Jet which was 4.0 so it is quite old but it still gives a good insight into how the desktop database engine works under the covers. ACE was copied from Jet and some additional data types were added (all the abomination data types like MVF's) but it has never been clear if there were also changes/improvements in the other stuff.
 
Doing a C&R and not compiling the code, ensures that the first time a querydef runs, the execution plan is calculated and SAVED. That way as the statistics for the BE change, you are assured of always having the most efficient plan and because the querydef saves the plan, it is reused, at least for the day..
We do a CR and don't compile every time we make changes to the master frontend. The CR physically removes the logically deleted records from the temp tables so users always run an unbloated copy of the master FE. We followed Ken Getz, RIP, recommendations and also use several of his standard routine modules in all our apps.
 
Last edited:
In my opinion, the biggest disadvantage of a loader access database is that you first need to have a running Access environment on the client, otherwise nothing works. With a batch file or your own exe (which is how we do it), this is not necessary.
whether Full version or runtime it will work.
The issue with relying on the loader to check the version is that you need to duplicate at least some of the code in your FE because if that pesky user simply navigates to his downloaded FE and opens it, all your validation is bypassed.
so with the .bat/.cmd file.
 
whether Full version or runtime it will work.

so with the .bat/.cmd file.
We don't bother with checking FE versions because our users always run a copy of the latest master FE version that's stored on the server.
 
We don't bother with checking FE versions because our users always run a copy of the latest master FE version that's stored on the server.
the suggestion is being offered to the OP and not to anyone.
 
Here are a couple of excepts from probably the best technical MS Access book ever written. Too bad there isn't an ACE edition. This is from my third copy of the book. The first was stolen, the second was lost to a fire and so I bought it again about 5 years ago. It was published in 1997 and still makes fascinating reading. The first two copies were $49.99 each, the last one was $5.00 I think and worth every penny. Get a copy if you can locate one.
jet.jpg
 
whether Full version or runtime it will work.
But if neither of them is on the client yet, then no.
A script or an exe file can specifically address this case.

But okay, it all depends on the environment you find at your customers' premises.
 
Personally I would never issue a batch file to a client for any reason whatsoever.
There could just be some clown who alters a batch file, causing a sequence of errors that I would be obliged to fix. I would always create the batch file and then convert it into an EXE file before issuing.
 
You want the querydefs to be uncompiled to force them to obtain new statistics and recalculate the execution plan each day.
I can understand that, if the BE changed it's data structure in some way, but if any change was just an increase in data?
 
As I said, when the db is compiled, so are the querydefs if they were left that way. You want the querydefs to be uncompiled to force them to obtain new statistics and recalculate the execution plan each day.

Here are a couple of excepts from probably the best technical MS Access book ever written. Too bad there isn't an ACE edition. This is from my third copy of the book. The first was stolen, the second was lost to a fire and so I bought it again about 5 years ago. It was published in 1997 and still makes fascinating reading. The first two copies were $49.99 each, the last one was $5.00 I think and worth every penny. Get a copy if you can locate one.
Picked up a copy from Amazon for £10.79.
Just hope I get to read it. :)
 

Users who are viewing this thread

Back
Top Bottom