The company for whom I now work buys some version of Microsoft Office that includes Powerpoint and Publisher, but not Access. I’d be more specific, but I don’t really know what the versions of Office are. But no Access? I need to figure out a way to fix that!
In the mean time, I’ve downloaded Nwind.mdb from Office Online, which I assume is the same as the Northwind.mdb sample database that ships with Access. When I attempt to create an external data query in Excel 2003, I get this message at the end of the query wizard:
I don’t typically use the query wizard, but I got the same error message in the MS Query interface when I tried to add a field by dragging it from the tables area to the query area. Furthermore, I got the same error when I tried to manually enter this SQL statement:
I’m not quite sure how I figured this next one out, but the problem was fixed by manually entering (manually means in the SQL dialog box from within MS Query):
I wasn’t able to avoid the error using any kind GUI – it had to be manually typed.
When I use ADO via VBA, I’m not required to enclose the table name in brackets in any SQL statements I write. The connection statement I use to connect via ADO is exactly the same as the Connection property of the QueryTable object except that the ODBC is removed from the front of it. Does that mean the ODBC driver is the problem? Why do I have an ODBC driver for Access if I don’t have Access installed? Is it time to learn MySQL so I don’t have to pay for Access?