Format$ not valid until database is repaired

[david]

I have a database in Access 2000 that uses linked Excel
worksheets as tables. I have build table queries in a
macro that select certain data fields and reformat the
data before placing into the new tables. All runs fine on
my PC with the database local or even with the database on
a network drive. However, when I go to any other PC and
open the database on the network drive and then run the
macro, it errors out on the very first query, stating that
the Format$ function is not valid. Clicking "compact and
repair database" fixes the problem, as the macro then
runs. However, you have to get the error first, then
repair before it runs. Any thoughts??

 

[Sylvain Lafontaine]

This is because you are opening the same mdb file as a FrontEnd (FE) on
different platforms (different versions of Windows, MDAC, VBScript, etc.).
This kind of error messages will also occurs with others with VBA fonctions,
like Format$, Mid$, etc. You can also encounter more exotic manifestations,
like a corrupted database.

Sharing a mdb file beetween different stations is a sure mean of getting a
corrupted database on a regular basis; where even the "Compact and Repair"
won't be able to fixes the problem anymore.

The safest way to proceed is to split the database into a FE and a BE
(backend), storing the BE (which contains the data only) on the network
server, then decompiling the FE with the /decompile option (using a shortcut
pointing to both Access and the MDB file) and then distributing the
decompiled FE to each user on their local machines (do not share it on the
network server!)

At first, this process will consume more time than simply putting the entire
database in a single file and sharing it on the server; but that's nothing
to the time you may loose when dealing with a corrupted database.

S. L.