asked on

MS Access FROM clause with IN clause to external file when the path includes brackets?

Given a valid path and file, the following works.

SELECT * FROM [Excel 12.0;HDR=YES;IMEX=1;DATABASE=C:\ExtFile.xlsx].[Sheet1$]

My question is how to handle a path that includes brackets ([test] folder)? I understand the extra brackets cause an issue since the entire connection definition is enclosed/qualified with brackets. Is there an escape character to process the brackets as part of the path?

SELECT * FROM [Excel 12.0;HDR=YES;IMEX=1;DATABASE=C:\[test]\ExtFile.xlsx].[Sheet1$]

Thanks,
Erich

chaau

Enclose the path into "", like this:

    SELECT * FROM [Excel 12.0;HDR=YES;IMEX=1;DATABASE="C:\[test]\ExtFile.xlsx"].[Sheet1$]

Open in new window

siemoer

ASKER

chaau-
Thanks for the suggestion, but it didn't work for me.

However, I continued troubleshooting and believe I found a solution. I knew IN would work with an ACCDB file w/o a PWD, but I had abandoned IN in an effort to support an ACCDB w/ a PWD or Excel/Text files. The solution is a hybrid. Use IN for the path, followed by brackets with the remaining connection parameters.

SELECT *
FROM [Sheet1$] IN 'C:\[test]\ExtFile.xlsx' [Excel 12.0;HDR=YES;IMEX=1]

Thanks again for your time.

siemoer

ASKER

Upon further review, there is an exception to my suggested solution. It does not work for MS Access files that require a DB Password. In which case, a work-around could be mapping a drive so the folder with brackets does not appear in the path (if the folder isn't the last one).

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.