Forum Discussion

Iron Contributor

Mar 26, 2025

How to make a Saved Export

Hello All, My very first post in the MS-Access lounge. I need to automate these steps: Press F-11 to get to the All Access Objects pane. Select a Table named Priority The Priority table has a D...

developer

office 365

arnel_gp

Iron Contributor

Mar 27, 2025

you can't use an SQL string in any Transfer() functions.

create a Query and use the Query in your Transfer.

example:

SELECT * FROM Priority Where YourDateField = Date;

save the query (say Query1).

now use Query1 to your TransferSpreadsheet.

(this will save to Priority.xlsx in Documents folder)

Docmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel12Xml,"Query1",environ$("userprofile") & "\Documents\Priority.xlsx"

GeorgieAnne

Iron Contributor

Mar 27, 2025

Thank You arnel_gp

Could you tell me if I could run this from MS-Excel directly say as if I was doing a Get External Data.

From MS-Excel Data Ribbon, Get Data, From Database, From Microsoft Access Database

Will any thing change if the database has a password?

Thanks,

GiGi

Gerrit_Viehmann
Brass Contributor
Mar 31, 2025
It's well documented if you use ODBC.
Driver={Microsoft Access Driver (*.mdb,*.accdb)};Dbq=YourPath\\YourDB.accdb;Uid=Admin;Pwd=YourPassword;

With the default PowerQuery it should be possible too, but documentation is lacking. Use OleDb there:
OleDb.Query("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=YourPath\\YourDB.accdb;Jet OLEDB:Database Password=YourPassword;", "SELECT * FROM YourTable")