Batch file for updating local frontend Access database copy

[AHeyne]

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.

 

[Pat Hartman]

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.

 

[Gasman]

That is the first time I have seen decompiled mentioned.?

 

[Pat Hartman]

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..

 

[AHeyne]

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.

 

[Pat Hartman]

That too.

 

[Gasman]

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.

 

[Pat Hartman]

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.

 

[dalski]

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.

 

[Pat Hartman]

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.

 

[BlueSpruce]

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.

 

[arnelgp]

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.

 

[BlueSpruce]

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.

 

[arnelgp]

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.

 

[arnelgp]

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.

 

[DickyP]

View attachment 121361

Couldn't endorse this recommendation more! Basically it is still good for everything except additional data sizes added and system limits.

Wouldn't be without mine, which is the second edition. Mine cost £46.99 , which was lot of money in 2001 when I bought it!!

 

[AHeyne]

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.

 

[Cotswold]

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.

 

[Gasman]

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?

 

[Gasman]

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.