Really sick Database

[DanCCC]

I wrote about this 2 weeks ago. Compact and Repair on source (linked) DB seemed to fix it. Not any more. Here's the current scoop:

When trying to look at our data using simple forms, the error message "Invalid Argument" appears and #Name? appears in a bunch of the fields.

We have tried Compact and Repair multiple times.

I created a totally new DB, imported all the objects - queries, reports, macros, objects etc. - exact same result.

When we run a certain query, the data appears momentarily, but the error message "Invalid Argument" appears on top of it in a window. When I click OK, all the data turns again into #Name?

We created a new query using the *, calling all fields, all records. Worked fine.

Added a simple criterion on a text field, back to the "Invalid Argument" error.

I took away the criterion, made it a Make-Table query, at runtime got the error message.

Made it back into a select query, tried to export to a text file, got the same message.

So it appears there's something bad in the table where it won't let itself be properly queried or exported. Do these additional clues give anyone an idea what's wrong with our DB?

With kind thanks to any DB Master who helps solve this puzzle,

Dan
Orlando, FL

 

[MichaelRed]

Not actually able to SOLVE the puzzle, but a suggestion for you to use as an approach.

Write some CODE V&V ro walk through the table on a record by record, field by field basis- checking each data element for the correct data type and properties as they NEED to be in your subsquent processing (query, form, format, report ...).

It can take some time and effort, but if the issue is corrupted DATA - as opposed to a corrupt database, this is -in my opinion- the beter soloution.

With out getting into your app, I would normally recommend extensive V&V processing of all data entering a database. This shortstops most subsquent process errors and allows for considerably less error checking in the following use of the data. After all, if date entries are all validated to be within a certain range on input, you hardly need to use "IsDate" later.

The only other data corruption I have seen often enough to comment on is hte use of autonumber fields in multiuser situations. IF you are using thie 'feature' in Ms Acccess tables AND have a multiuser app, it can cause several different problems. These may also be overcome, but V&V is not hte approach.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[DanCCC]

Michael,

Thanks for this advice.

But I must admit that I don't even know what V&V refers to. I don't expect you to totally explain it to me, but can you point me in the right direction as far as on online resource?

Dan

 

[MichaelRed]

"V&V" = Verification & Validation.

It really just means to do (programatic) checks on the integrity of the information.

A (trivial) example might be simply to check that a date is later than the current date when entering an order or promised delivery date, another might be to check that a payment amount on an invoice is not negative.

Obviously, the various individual items in a record can be checked for these types of conditions, but you can (and should) also check for various relationships between some of the fields. An (again trivial) example might be to check that a delivery date is at least three working days after an order date for the same item (overall order).

Again, these are trivial examples -but they are part of the overall process which should be routinely applied to information being included into you data tables. I do not know hte nature of your app, and really do not want to delve into your business, however data entry through forms, transfer of information from sources such as Exel, flat files (.CSV, .txt, et al) and any and all other processes need to be 'groomed' before you admit the information and use it as "DATA".

MichaelRed
m.red@att.net

Searching for employment in all the wrong places