CODEWITHSUNDEEP
sql-serverms-accessexport
David Carle
David Carle

Reputation: 13

Export from Access .mdb database with ODBC link to SQL Server into Access format database using VBA

I have an Access 2019 front-end database that links to a SQL Server 2017 Express database.

I'd like to export a table or query from the VBA code in the front-end into an Access (Jet) format database (as a portable data format to use for updating a remote site)

The code I've tried (for a table called FileLocation) is:

Access.DBEngine.CreateDatabase "C:\Temp\ExportTest3.mdb", DB_LANG_GENERAL
docmd.TransferDatabase transfertype:=acExport, databasetype:="Microsoft Access",databasename:="c:\temp\ExportTest3.mdb", objecttype:=acTable, source:="FileLocation", destination:="FileLocation", structureonly:=false

This "works" but the table created in the ExportTest3 database is a link to the SQL database (with the Connect property set in MSysObjects) so is dependant on the SQL Server connection, but I'm looking for an independant portable .mdb file that can be read on any PC.

Edit: I've discovered that I can use

docmd.RunSQL "select * into FileLocationLocal from FileLocation"

and then use TransferDatabase to export the FileLocationLocal table as a non-linked table

But is there a way to do this as a single step, or is there a better approach?

Upvotes: 1

Views: 498

Answers (1)

June7
June7

Reputation: 21370

Consider:

Access.DBEngine.CreateDatabase "C:\Temp\ExportTest3.mdb", DB_LANG_GENERAL
CurrentDb.Execute "SELECT FileLocation.* INTO FileLocation IN 'C:\Temp\ExportTest3.mdb' FROM FileLocation"

Upvotes: 1

Related Questions