Database copy oddity

[BrokenBiker]

Alright...Our database was benchmarked by several other units. They're completely separate, i.e. independant data collection, no shared networks, different bosses.

At any rate, I made some changes to the db and created a "utility update" db for these other units to use to automatically get these updates. Before sending all of this out, I received copies of these other databases to make sure that everything works properly.

Well, everything works fine except for one db. I thought originally that they must've changed something in the db, so I made a copy of ours and imported their data and got the same error as in theirs. So...obviously, I thought there must be some problem in their data. I ran through it and double-checked the verification VBA in the form used to enter the data and there's no noticeable difference in the data and the VBA/form are identical.

This might tie into my previous post because there's something goofy in Access that I can't quite pin-point. As far as I know, there's no difference between any of the databases except for the data itself. Can the data be corrupted if it looks normal in the table? Would corrupt data cause an error in a query/report? (I've never run into corruption in a db before.)

Sometimes I don't like Access so much.

 

[FoFa]

It's always possible is one I learned, but one other I learned it is usually the code. So I would go on the assumption that it is code, but there must be some other differance in the DB causing it. So then the question becomes, is your updated data driven? If your update is not data driven, which usually is usually the case, than I would start checking DB structure of all the affected objects. I bet you will find a differance there or a problem. Just a gut feeling. It might help if we had the actual error you were getting?

 

[The_Doc_Man]

The essence of troubleshooting is OBSERVATION - before, during, and after the fact. Plus the ability to describe what happened to anyone who might wish to help you.

Also, since the problem appears in more than one place, it is unlikely (not impossible, but unlikely) to be a registry issue.

The issue of differences in the metadata cannot be ignored. Document your databases using the documenter and export all of it to a file. Then drop down to the command prompt and compare the files via DOS-like FC command. Ignore the differences in headers but look for differences in the meta-data.

 

[BrokenBiker]

Here's how it all works. I have them go to File|Get External Data|Import and import a browsing module and a form that has all the update code in it. Then I have them open that form, click the 'Browse' button to find the update-utility db. After that, the code runs and deletes the old objects (queries, forms, and reports) and imports all the new 'stuff' from the utility db. (See the link from the previous post.)

Basically, it just deletes...

DoCmd.DeleteObject acQuery, "qrySquadronMxPercentage"

and imports...

DoCmd.TransferDatabase acImport, "Microsoft Access", strPath, acQuery, "FlightTECRateQuery", "FlightTECRateQuery", False

So, I know that all the objects/VBA are the same in all the different copies. Even if they had made some changes on their own, all related objects are deleted and then imported w/ the new stuff.

The updates are all object-driven, not data-driven.

The report that's giving me problems is basically an unbound report w/ sub-reports in it. The sub-reports are not related. The queries for the sub-reports are driven from criteria selected on a form. When I run the report I get:

Run-time Error 13
Type Mismatch

It de-bugs to

If Me.txtFinalPercentage = 1 Or Me.txtFinalPercentage >= 0.95 Then
        Me.Rating = "Outstanding"
    End If

When I run the under-lying queries, everything comes up fine. When I run the subreports separately only one gives me problems. An example here shows how the report works, although the example only has one subreport. The working example is in the last post (there are only three posts.)

I think that part of the problem may lie in the "final percent" field. It's a calculated, unbound field in each of the subreports. Each subreport has its own query and VBA for conditional formatting.

Let's run through how the percentages are derived.

First--the various inspections are totalled and summed and divided for a percentage. Sometimes there aren't any 'Pass' or 'Fail' inspections, so to avoid an error I gave it an IIf statement.
txtGroupTotals:

=IIf([txtSumPass]+[txtSumFail]=0,"N/A",[txtSumPass]/([txtSumPass]+[txtSumFail]))

Each query has a DeductPoints field. That's used in figuring the final percentage.
txtFinalPercentage:

=(([txtGroupTotals]*100)-[DeductPoints])/100

I know this is a lot, but I'm almost done.




I thought that part of the problem was that if txtGroupTotals ended up N/A then there would be an error in the txtFinalPercentage, but the other copies of the db work fine...until I delete that data and import the data from the one that's giving me problems.

I'll throw in an IIf statement for the txtFinalPercentage field to see if it helps.

What it needs to say is 'if txtGroupTotals = n/a then txtFinalPercentage = n/a'. Maybe:

=IIf([txtGroupTotals]="N/A", "N/A", (([txtGroupTotals]*100)-[DeductPoints])/100

 

[BrokenBiker]

Well...after all that babbling, the IIf statement works. Problem solved. Also, that means that I avoided this from potentially happening at the other sites.

=IIf([txtGroupTotals]="N/A","0",(([txtGroupTotals]*100)-[DeductPoints])/100)

Thanks for the responses. Sometimes (like this time) these forums are a good way to slow down and sift through the problem to help you key in on the faulty areas.