Quote Originally Posted by mpearce
The rest of the import seems to be working fine i am excited about that. However there have been a few time where I will get, i think its a runtime error saying "the database has been placed in a locked state" (or something like that).
When developing with ADO, I get this error occasionally:

Error -2147467259 The database has been placed in a state by user 'Admin' on machine 'VM2003' that prevents it from being opened or locked.

I'm not sure about the cause; it may be code changes not saved/compiled. If I compact the database, the error goes away ... until I make more changes.

So, I think this may be an error your users won't see. If you find a situation where it does show up for your regular (non-developer) users, or if you were talking about a different error, let me know.

Meanwhile I added minimal error-handling to Command1_Click. However, I really don't know what more to do. Everything we've done so far is basically the onclick event for a command button on a form in a larger application. (Right?) I think any error handling for spreadsheet import should be integrated with the error handling strategy in the parent application; I have no idea what you're doing there.

Also, from your users' perspective, what should happen if a spreadsheet import fails? And what about the Access tables: some might have newly imported data; others old data or empty?

Quote Originally Posted by mpearce
There is one small thing i need to do. Originally there is a summary sheet as the 1st sheet in the workbook. Since that sheet isn't needed for analysis I have code that opens each workbook and deletes that sheet before the merge is done and saves the workbook without the summary sheet. That part works fine. But if i try to merge two workbooks and the summary sheet is already deleted the code breaks because it is looking for a sheet that doesnt exist.
What business value do you add by deleting the Summary sheets? I'm thinking "do the simplest thing which could possibly work". The import code is happy if it finds the sheets it's looking for; it doesn't care whether any additional sheets are present in the workbook.

In other words, your problem figuring out how to delete a sheet which may not be there ... that problem goes away if you don't try to delete the sheet.

If you really need the sheet gone, you could just attempt the delete and ignore the error you get when the sheet isn't there:

[vba]On Error Resume Next
oBook.Worksheets("Summary").Delete
If Err.Number = 0 Then
MsgBox "Summary sheet deleted"
Else
MsgBox "Summary not sheet deleted"
End If
Err.Clear
On Error GoTo 0 'or your subroutine's error-handler[/vba]
Why tell anyone whether or not the Summary sheet is deleted?

I attached a Zip of the database with my latest changes. Beware, I don't know which fields you changed to cure the "The field is too small to accept the amount of data you attempted to add" problem.

Hans