如何仅在Excel中查询Access数据库且速度接近Access内置查询？

Hey Stefan,

Absolutely—you can achieve near Access-native query speed without modifying the original Access database at all. The key fix is to make Power Query push your filtering/join logic directly to Access (server-side processing) instead of pulling the entire table into Excel first (client-side processing, which is what was slowing you down). Here's how to do it:

Method 1: Direct SQL Query on Import #

This is the simplest approach if you're setting up a new query:

1. In Excel, go to Data > Get Data > From Database > From Microsoft Access Database.
2. Browse to your .accdb/.mdb file, then click the Advanced options dropdown at the bottom of the window.
3. Paste your optimized SQL query (the same logic you used in Access) into the SQL statement box. For example:

   SELECT t1.*, t2.relevant_column
   FROM YourFirstTable t1
   INNER JOIN YourSecondTable t2 ON t1.ID = t2.MatchingID
   WHERE t1.DateColumn >= DateAdd('d', -30, Date()) -- Filter to last 30 days
   

4. Click OK—Power Query will send this SQL directly to Access, which executes the filter/join locally and only returns the small result set (your <20 rows) to Excel. Refresh will be nearly instant.

Method 2: Modify an Existing Power Query #

If you already have a Power Query setup and want to tweak it:

1. Open the Power Query Editor, then find the initial Source step in the Applied Steps pane.
2. Right-click the Source step and select Advanced Editor.
3. Replace the default Access connection code with a direct SQL call using either ODBC or OLE DB. Here are two examples:

   ODBC Version #

   let
       Source = Odbc.Query("Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\Path\To\YourDB.accdb;", "SELECT ... WHERE ...")
   in
       Source
   

   OLE DB Version (More Stable for Access) #

   let
       Source = OleDb.Query("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Path\To\YourDB.accdb;", "SELECT ... WHERE ...")
   in
       Source
   

4. Replace the "SELECT ... WHERE ..." part with your optimized Access-compatible SQL, then click Done.

Critical Notes #

• Make sure your SQL uses Access-specific syntax (e.g., DateAdd('d', -7, Date()) instead of MySQL-style DATE_ADD, single quotes for string literals). Test it in Access first to confirm it works.
• If your Access database is password-protected, add ;PWD=YourPassword to the connection string (e.g., Dbq=C:\YourDB.accdb;PWD=MySecurePass).
• This approach works because Access does all the heavy lifting (filtering, joining) on its end, sending only the tiny final dataset to Excel—exactly how your Access-built query behaves.

内容的提问来源于stack exchange，提问作者Stefan L.