Exporting a table/query from a form button

[dwarvenmilitia]

Hi...

I have a simple database with name, surname and address details etc, however I want to be able to hit a button from a form to export it to a new mdb file with only basic things from the table, say just names and not the addresses.

I understand that I could create a query to select only the fields I need but then using the DoCmd.TransferDatabase how would I create a new file.

I'm searching how to do this but any help would be sweet.

Cheers.

 

[dwarvenmilitia]

Ok slowly learning here.

So far I have this code with an empty db in that folder. But how do I make it specific for a query rather than just a table as a whole?

DoCmd.TransferDatabase acExport, "Microsoft Access", "C:\db\test.mdb", acTable, "dancers", "dancers", False

Cheers

 

[ssatech]

Try:

DoCmd.TransferDatabase acExport, "Microsoft Access", "C:\db\test.mdb", acQuery, "dancers", "dancers", False

 

[dwarvenmilitia]

Hey ssatech

That doesn't work. It says Jet DB engine could not find object dancers...

I have a form called "Dancers" which updates data in a table "dancers". I want to copy most of the data from the table "dancers" for a simple db related webpage but excluding those addresses etc. (This is to remove unsecure details should it be found to be copied at all and to minimise the db as much as possible for hosting size restraints).

I thought that would work but it just exports the query to a new db rather than running the query and copying the returned data. What about CopyObject function? Would that work?

 

[ssatech]

But how do I make it specific for a query rather than just a table as a whole?"

I thought you wanted to export a query named "dancers" into amother database located at "C:\db\test.mdb"

What about running an append query for only those fields you want into a new table/temp table and than exporting that table. You could do it all (Run the query and export)from your command button.

 

[dwarvenmilitia]

Hey sorry for the confusion.

How would I run the query. I don't usually code in VB so I don't really have much of an idea.

Cheers

 

[dwarvenmilitia]

A little digging again and I found this...

Dim SQL_Text as String
SQL_Text = "Select Name, Surname from dancers"
Docmd.RunSQL (SQL_Text, false)

I haven't had a chance to run this yet but I will later on. Is this what you were meaning and then do the DoCmd.TransferDatabase function?

If not I could also transfer the table using the original function and then run a similar SQL query to delete unnecessary data.

Which would you suggest if these are the right way arount the problem.

Cheers

 

[dwarvenmilitia]

Hey ssatech...

Running the query and exporting it is exactly what I want to do I have no idea how to do it. I thought I might have been onto something but with my limited knowledge I can't say that I was...

Any help would be appreciated.

Cheers

 

[PHV]

And what about something like this ?
SQL_Text = "SELECT Name, Surname INTO dancers IN 'C:\db\test.mdb' FROM dancers"
Docmd.RunSQL SQL_Text

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dwarvenmilitia]

Hey cool PHV

That is a quicker way to do it.

Legend. Cheers

-------------------------------------
Your space here!