Click here to Skip to main content
15,860,972 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
In (DAO) SQL, I can use ISAM to connect to an Excel sheet to import/export data using expressions like:

SQL
SELECT T1.* INTO Temp FROM [Excel 8.0;HDR=YES;IMEX=1;Database=D:\test.xls].[Sheet1$A1:U65536] AS T1


Is there and equivalent way to connect to an MDB/ACCDB file?

(I'm in an app that can run SQL, but doesn't give me direct control over connection strings etc., so it needs to run just from SQL)

EDIT:
I found I can directly access older format MDB files using something like:
SQL
SELECT Components.Description FROM `C:\Users\me\Desktop\database.mdb`.Components

But this doesn't work for newer MDBs, where my query complains about the file format.

What I have tried:

Googling, but there is no ISAM for Access databases.
Posted
Updated 21-Nov-21 10:04am
v2

1 solution

Try using OPENROWSET (Transact-SQL) - SQL Server | Microsoft Docs[^]
SQL
SELECT CustomerID, CompanyName
   FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
      'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';
      'admin';'',Customers);
(example is from the documentation quoted)
 
Share this answer
 
Comments
Kyudos 21-Nov-21 16:02pm    
Thanks, I've tried that but it doesn't recognise the FROM clause. I've found I can connect directly to an old (Access 97) format MDB like this:
SELECT Components.Description FROM `C:\Users\me\Desktop\database.mdb`.Components
But it complains about the file format for newer databases.
CHill60 21-Nov-21 16:05pm    
I've had some success with using save as to "back date" format from accdb to mdb. Also try changing from jet to (can't remember the name sorry)
CHill60 22-Nov-21 4:02am    
When you say "but it doesn't recognise the FROM clause" - what is the exact error? Also what version of SQL Server are you attempting this in?
Other things you can try - use ACE drivers instead of JET (my comment above - I finally remembered the name)
Ensure you have the redistributable for Access installed Microsoft Download Center: Windows, Office, Xbox & More[^] (although I have had issues with that affecting Power BI/Power Query
Kyudos 23-Nov-21 15:37pm    
It's not SQL server. My app is an interface to MDB / ACCDB files, and part of that interface allows the user to run SQL queries through CRecordset classes and/or CDatabase::ExecuteSQL. In this case the CDBException is "Syntax error in FROM clause." (State:37000,Native:-3506,Origin:[Microsoft][ODBC Microsoft Access Driver])

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900