Query string getting corrupted

[Bill McCarthy]

I have an app where I use a query string, in both forms and reports, that
follow the form:

datefield >= #01/01/2001# and datefield <= #12/31/2002#

this query string exists in many places in many modules, varying slightly.

the app runs fine for a while and then suddenly we start getting error
messages indicating an invalid string. my error message display corrupted
string as having the <= replaced by <?. it does NOT get changed in the
source, just the compiled code. I can fix it by touching the source and
recompiling. this is obviously aggrivating as I have to recomile several
source modules by touching each one. my questions:

1. What can be causing this? Something within Access or could it be
external somehow?

2. Is there any way to force a complete application recompile? I can't
find a command to do this so I have to touch each code file separately.

Thanks for any help you can provide.

 

[John W. Vinson]

2. Is there any way to force a complete application recompile? I can't
find a command to do this so I have to touch each code file separately.

What version of Access? In XP and beyond, all of the code is in one place, and
it either all gets compiled or none of it. You may want to: Back up your
database, just for safety; Decompile your database (to remove all compiled
code); compact and repair it; open the VBA editor and select Debug... Compile
<my database>; compact again.

If this is happening to the SQL text of your Query (rather than VBA code) you
have a different corruption issue - you may need to import all objects into a
new database to get clean system tables.

See Tony Toews' corruption FAQ at
http://www.granite.ab.ca/access/corruptmdbs.htm
for other possibilities, and instructions for decompiling.

John W. Vinson [MVP]

 

[Maury Markowitz]

2. Is there any way to force a complete application recompile? I can't
find a command to do this so I have to touch each code file separately.

Yes, although it's much more annoying than it should be.

0) BACK UP!

1) make a shortcut to the db in question

2) right-click the shortcut and select Properties

3) in the "Target:" line, go to the end and add " /decompile" (no quotes,
and you might have to add quotes around the command that's already there)

4) make sure the db is not open, then double-click the shortcut

5) after it opens select Tools->Database Utilities->Compact and Repair...

This often leads to a dramatic decrease in size as well, depending on the
state of the db.

Maury

 

[Bill McCarthy]

Thanks for responding, John.

I am using Access 2003.

It is my experience that only the code segments I've changed get recompiled.
I have had to touch each module that contains one of these string in order
to fix the problem. Maybe you know something I don't (most likely).

Your suggestion below says to DECOMPILE the database. How do I do that? I
don't see any menu entry that says I can do that.

Thanks again.

 

[Bill McCarthy]

Thanks for the advice. Doing a recompile is one thing. Finding out WHY
this happens is another. I'll restate the main problem and question. I
hope someone can shed some light on this.

I have a multi-user app where I use a query string, in both forms and
reports, in VB code, that follow the form:

datefield >= #01/01/2001# and datefield <= #12/31/2002#

this query string exists in many places in many modules, varying slightly.

the app runs fine for a while and then suddenly we start getting error
messages indicating an invalid string. my error message display corrupted
string as having the <= replaced by <?. it does NOT get changed in the
source, just the compiled code. If I change ANYTHING in the code module to
force a compile, the problem seems to correct itself. This tells me that
somehow, the compoiled code is getting corrupted and only the <= gets
changes to <?, nothing else.

This sounds weird. Any ideas?

 

[Jeff Boyce]

Bill

I didn't see your earlier posts, but I'm wondering about the
environment/design of your "multi-user app"?

Do you have a single back-end data store (tables only) on a LAN server, and
a copy of the front-end on each user's PC?

If not, corruption could be sneaking in because of the "physical" design.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Bill McCarthy]

No, it's all on the server in a singe DB file.

I guess I don't understand your last comment 'corruption could be sneaking
in because of the "physical" design'.
would you elaborate? Thanks.

 

[Jeff Boyce]

Bill

If, as you say, "it's all on the server", then each/every person using your
database has to have the file/forms/queries/... run up and down the LAN
cables to your "single DB file". All that network traffic makes for a
considerable exposure to network ... "transients", any of which could cause
corruption.

Moreover, if you have five folks all hitting a single Access database on the
LAN at the same time, and one of them "kicks out the plug" (e.g., simply
turns off his computer), this will also scrag your database.

The commonly-used "physical" design for a multi-user database is to "split"
the database. The data (and only the data) is stored in one Access .mdb
file, located on a LAN server. Everything else (forms, queries, ...) and NO
data/tables is stored in another Access .mdb file ... and uses "linked"
tables (links to the "back-end" data on the LAN). This .mdb is considered
the "front-end", and EVERY user has a copy on his/her PC. Not one copy,
shared, one copy per user.

I've been using this "split" design for many years without corruption
issues.

(P.S. -- there are also corruption issues that crop up (apparently) related
to use of wireless networking -- is your network "wired"?)

Good luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/