Invalid Bracketing of name

[matthewR]

I am trying to use the import external data in Excel 2003 (not upgraded to 2007 yet). I am getting the error "Invalid Bracketing of name 'mdb.'. (see attachment). I make a table of the query I am using because the query uses the NZ function and I get an error so I make the query into a table. It looks like the name of the database is Ok but not sure where to look for an invalid bracketing.

[HansV]

Do you have Office 2003 and Office 2007 installed on this PC?

[matthewR]

I don't know. What would I look for to see if Office 2007 is installed? I have excel 2003, Word 2003 and Access 2003. I have Outlook 2007.

I copied the database and the excel file to the C drive and I do not get this error. The database and the excel file were on a network drive. Why would a network drive cause this error?

[HansV]

You have at least part of Office 2007 since you have Outlook 2007.
Try this:

- In Excel, select Tools | Get External Data | New Database Query...
- Select MS Access Database as data source.
- Clear the check box "Use the Query Wizard to create/edit queries", then click OK.
- Select the database, then click OK.
- If you're prompted for a username and password, specify admin for the username and leave the password blank.
- Select the table, then click Add, next click Close.
- Double-click the asterisk * at the top of the field list, or double-click only the fields that you want to import.
- Click the SQL button. You'll see the SQL statement that will be used to import the data.
- Scroll down until you see the FROM part.
- It should look similar to this:

FROM `C:\Databases\MyDatabase.mdb`.tblSomething tblSomething

- Make sure that there are no extra ` characters in there.
- You can also try removing the .mdb part so that it looks like

FROM `C:\Databases\MyDatabase`.tblSomething tblSomething

- Click OK, then click File | Return data to Microsoft Excel.
- You'll now return to Excel where you can specify where you want the data.

[matthewR]

Thanks Hans, I will try that. Right now it works on C but I would like to put it on the network drive in case my computer crashes.

[matthewR]

Hans - I followed your instructions for the database that is on the network drive. Following your directions and not using the Query Wizard to create/edit query, I didn't get the error. I am not sure why it worked because I didn't do anything to the FROM clause in the SQL. It didn't have anything with mdb in it. Thank you for your help.

[matthewR]

I am back again with the same problem but a different error. I am on a network drive. ( I copied the database to the C drive and it works there). I am trying to Import external data. I am now getting "The microsoft office access database engine cannot find the input table or query "mdb.PamMtable". I tried the way you said earlier but when I double click the * to bring down the fields, I get that error. I can't look at the SQL at this point because there isn't anything when I click the SQL button. I am on the same drive as the last database. The other database (by using your suggestion) works fine on the network drive. I created another spreadsheet and did the import external data and it didn't give me any errors.

[HansV]

Can you add a single field instead of * ?

[matthewR]

No, I tried but I get that error.

[HansV]

Try this, after adding the table:
Click the SQL button. The window will be empty, but don't worry.
Enter the following SQL statement:

SELECT * FROM PamMTable

assuming that PamMTable is indeed the one you want to use.
Click OK.

[matthewR]

I put the select statement in the blank SQL but it still gives me the same error. It must be a network thing because I have no problem on the C drive.

[HansV]

And if you use

SELECT * FROM "networkpath\databasename.mdb".PamMTable PamMTable

with the correct path and filename substituted?

[matthewR]

Hans:

That worked. Thank you very much for your help.

[HansV]

Phew!

[matthewR]

Another question (or problem). I just got an email that they are moving all our files to another drive. I just setup all my connections from my databases to Excel which included the above scenario. Will I have to redo everything since my database and Excel spreadsheets will be moved to another drive?

[HansV]

Jan Karel Pieterse has written a free utility Query Manager that lets you edit the path to the data source of database queries in Excel. You can download Query Manager from JPK-ADS Download Page.

[matthewR]

Thanks Hans - I will take a look at that.

[matthewR]

I loaded the add in. I can see where to edit the path. Once we change to 2007, will I be able to load the Add-in?

[HansV]

Yes, it'll work in Excel 2007 too.

[matthewR]

The query manager works perfectly except on the excel sheet where I had to use the SQL (SELECT * FROM "networkpath\databasename.mdb".PamMTable PamMTable)
that you gave me. Attached are screen prints of the Query manager - before changed path and after. The error I am getting is that it is not a valid path. I changed the path in the SQL too.