Export a query to a new database

  • Thread starter Thread starter MarkB
  • Start date Start date
M

MarkB

Access 2003

I have a large database with several tables. One of the tables has about 74
fields and I have created a quety using only 12 fields and criteria to
filter only certain records. I need to send the results of the query
(basically the records and fields) to an associate. How can I export the
records and fields of that query to a new Access MDB file to send to them
without sending my other data?
 
Does it need to be in an .mdb file? You can export the results to a text
file (using TransferText) or to an EXCEL file (using TransferSpreadsheet) --
these will be a bit easier to create.
 
Yes. It need to be an MDB file as that as they have a preset application
which uploads that file to a Web site refreshing a master database.
 
It's a multistep operation:

1) Create the new .mdb file using CreateDatabase method of DAO library.
Here's an example:

Sub CreateDatabaseX()

Dim wrkDefault As Workspace
Dim dbsNew As DATABASE
Dim prpLoop As Property

' Get default Workspace.
Set wrkDefault = DBEngine.Workspaces(0)

' Make sure there isn't already a file with the name of
' the new database.
If Dir("NewDB.mdb") <> "" Then Kill "NewDB.mdb"

' Create a new database with the specified
' collating order.
Set dbsNew = wrkDefault.CreateDatabase("NewDB.mdb", _
dbLangGeneral)
' code goes here to work with the new database file' ....

dbsNew.Close

End Sub(2) Use a maketable query that is based on the query of interest to
create a new table; or create a new temporary table and use an append query
based on the query of interest to fill that table; or use an existing, empty
("temp") table and use an append query based on the query of interest to
fill that table.(3) Use DoCmd.TransferDatabase to copy the table from (2)
(with its data) to the new file.(4) Close the new file.(5) If you're using
the same "temp" table over and over without recreating it, then empty the
table using a delete query.-- Ken Snell<MS ACCESS MVP> "MarkB"
 
Easier to Import than to Export:

-Create a new, blank mdb file
-File>GetExternalData>IMPORT
-Browse to the file with the data you want
-Select the query with the data you need. Under Options, be sure to select
"Import Queries as Tables", then click OK.
-Change the table name as needed (it will have the name of the query, but it
will be a table).

HTH,
 
George Nicholson said:
Easier to Import than to Export:
Click to expand...

Good thought... I was thinking of what to do programmatically, and didn't
consider the manual approach.
 
Well, the OP now has 2 methods to chose from depending on their coding
experience level and how often they may need to do this (neither of which is
known)

:-)
 
Back
Top