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.
--
JT

Check if you can get exclusive access before you try to use it in Access:


http://support.microsoft.com?kbid=138621
XL: Macro Code to Check Whether a File Is Already Open

http://support.microsoft.com?kbid=291295
XL2002: Macro Code to Check Whether a File Is Already Open

http://support.microsoft.com?kbid=213383
XL2000: Macro Code to Check Whether a File Is Already Open

http://support.microsoft.com?kbid=184982
WD97: VBA Function to Check If File or Document Is Open

--
Regards,
Tom Ogilvy


"JT" wrote:

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.
--
JT