Importing databases - test if database file is valid to avoid error 3

[scriggs]

I have a routine which is importing 000's of Microsoft Access databases into a large Access Database.

Some of the files appear to be corrupt and when the routine tries to open them I get the error:

"The database xxx needs to be repaired or isn't a database file. You or another user may have unexpectedly quit Microsoft Office Access while a Microsoft Office Access database was open. Do you want Microsoft Office Access to attempt to repair the database?

Yes No"

I am not worried about fixing or importing it, I just need to skip the dialog so it carries on.

Any ideas please?

 

[CautionMP]

scriggs,
I'm not sure if it will work for this error, but have you tried adding and error handler to your routine to trap this error?

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[scriggs]

Hi CMP

I have tried adding error handling but this error message appears before the error handling - i.e. the error comes, then waits for user input, then runs error handler.

For now I have just used setWarning=false but not ideal as I cannot trap or log the error files.

Eoin

 

[CautionMP]

scriggs,
Yeah, I wondered. Maybe a different method of importing might let you catch the error.

What method are you using to import objects, and what objects are you importing?

CMP


[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[Golom]

Try something like

Function ValidDB(DBname As String) As Boolean
    Dim db                          As DAO.Database
    On Error GoTo InvalidDB
    Set db = DAO.DBEngine(0).OpenDatabase(DBname)
    ValidDB = True
    Set db = Nothing
    Exit Function
InvalidDB:
    ValidDB = False
End Function

and

If ValidDB("C:\somepath\somedb.mdb") then
   ' Do the import
Else
   ' Report the problem
End If

 

[scriggs]

Golom

That sounds great, just like I was looking for. I will try it out later and report back.

 

[JerryKlmns]

And to follow on Golom's idea when ValidDB = False try to compact and repair the db in code.

 

[MichaelRed]

While Golem is an ACE, I would go with the concept of knowing what the overall goal / objective is, the very concept if [qoute scriggs] "... is importing 000's of Microsoft Access databases ... " [/qoute] could easily run afoul of so very many issues / rules that it woiuld be better to understand the overall intent and some history of the set of dbs before proceeding. Surely it is POSSIBLE to grope blindly through the fog and get some results, but I would feel confident that these (results) would not produce a collection of much use. Duplication of object names and types will leave a profusion of missing and / or mis-nammed objects. Variations in the origin (version of MS Access used to create the source objects will cause some of the errors already mentioned. Different libraries (e,g, references (and even the order of the references) in the sources will contribute to more troubles along the path to the rubber romper room. More pitfalls (or pratfalls?) abound in going forward.

Good luck and happy learning ... I'm quite sure this advice is totally superflous ...




MichaelRed

 

[scriggs]

MichaelRed

Thanks for the reply, very good comments and things to consider. Is superflous to my post, I am importing databases with identical structures generated by 000's of individual terminals running the same software. The purpose of this accumulation is to get the data into a single datastore for mining.

Thanks!

 

[Golom]

MichaelRed

You're right of course. I too am somewhat leery of handling thousands of databases with this scattergun approach ... and I certainly wouldn't attempt to try compacting and repairing in the course of this process. A really significant issue to me would be the validity of the data that does get into the data warehouse if we failed to import a significant fraction of the databases that were supposed to be out there.

All I was trying to do was just raise a flag that a particular database couldn't be processed and bypass it. Remedial action to figure out what the problem was would take something much more extensive than this.

I'm an ACE??? Who woulda thought ...?

 

[MichaelRed]

Even considering the "Identical structures", I would still be a LOT more cautious than running a blind script. Of course, you are going to follow your proclivities and go ahead, so this is more for others who may seek some info re the overall process and be somewhat more cautious.

Issues remain in consideration of the parent "App":
[tab]What V&V of data entry was included?
[tab]What assures 'you' that the many copies were -in fact- identical (e.g. version control).
[tab]How do you know -for SURE- that no one modified ANY copy of an access database in the 'field'
[tab] ... so many more I will not even attempt to enumerate

Further, even when assured or the above, I take the approach that each import should be brought into a 'clean room' type environment and undergo extensive V&V on the records (both individually and collectively) and only after passing the V&V test, is the DATA passed on to the parent (target) db. Of course, this does take more time and effort, so the lame, lazy and those in a (deradful) hurry bypass it ... and get the same results you will:

[tab]missing inputs
[tab]skewed results of your mining
[tab]ands (eventually) increased carrear opportunities

MichaelRed

 

[MichaelRed]

"... I'm an ACE??? Who woulda thought ... "

2700+ votes in recent times is 'an ACE' to me. Not particularly concerned with the opinions of others re this ...



MichaelRed

 

[JerryKlmns]

Well, I think a star for MichaelRed would be appropriate, for sharing his valuable experience