Error handling

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm running this code from Excel to import a file into Access.

Dim QueryName, Location As String
Set AccApp = GetObject(, "Access.Application")

QueryName = "Clear Table"
AccApp.DoCmd.OpenQuery QueryName

Location = "C:\North America\Offices.csv"
AccApp.DoCmd.TransferText acImportDelim, "Dept Specification", "Dept",
Location, False

The problem is when someone already has the Offices.csv file already open.
If no one has the file opened, this works just fine. Otherwise, there is an
Access error message that the file is already opened. The issue is you have
to click on Access to see the message. To the user, it appears that the
Excel macro is still running.

If the macro encounters this situation, I would prefer a message be
displayed by Excel and leave Access out of it. Ultimately, I would like for
the macro to (1) click "OK" for the Access message, (2) display a msg in
Excel, and (3) end the macro. This will allow the user to try again in a few
minutes.

I'm using Access to import recrods because there are more than 65,000
records. Otherwise I would just do the whole thing in Excel.

**** code continues*****

QueryName = "Create Reference"
AccApp.DoCmd.OpenQuery QueryName

etc.

Any help on solving this problem, would be greatly appreciated. Thanks for
the help.
 
Hi,
you can try to delete target file, and if this fail - display a custom
message:

Location = "C:\North America\Offices.csv"
on error resume next
Kill Location
if err.number =0 then
'restore error handler if you have one here
AccApp.DoCmd.TransferText acImportDelim, "Dept Specification", "Dept",
Location, False
else
msgbox "File already open, pls try later"
end if

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com
 
You'd be better off using automation. Tick M/soft Access Object Library in
Excel's VB Editor's Tools, References and use the following code to open and
then use your Access database. Error messages will now appear in Excel and
you can use an error handler to trap them. The other advantage to
controlling Access this way is that you can keep it invisible and the user
needn't know about it (though you can make it visible if you want with
AccApp.Visible = True).

On Error GoTo handler
Dim QueryName As String, Location As String
'Set AccApp = GetObject(, "Access.Application")
Dim AccApp As New Access.Application
AccApp.OpenCurrentDatabase "[path to file.mdb]"

QueryName = "Clear Table"
AccApp.DoCmd.OpenQuery QueryName

Location = "C:\North America\Offices.csv"
AccApp.DoCmd.TransferText acImportDelim, "Dept Specification", "Dept",
Location, False

Set AccApp = Nothing

Exit Sub

handler:
If Err.Number=... Then
... (perhaps msgbox to tell user to try later)
End If

End Sub

[N.B. I've amended your dim statement - there's a common misconception
(apologies if you know about this!) that you can have more than one variable
in a single line and define all of their types with a single As statement.
In fact they've all got to be done separately (your QueryName was being
tacitly declared as a variant)]
 
Back
Top