Help needed creating Macros to export and move tables..

[Boston Roberts]

Hi, I've got two questions that involve macros. I'm using Microsoft Access 2002.

1. I've got a query which brings up a table. Now I want this table to be exported to a text file when I activate the Macro, how do I build this macro?
I've tried looking at the help, it doesn't seem to help.

2. In my database I have a yes/ no field. I want the items labled yes to be moved to a seprate table. How do I do this? Is this even possible?

Any help would be greatly appreciated.

 

[ezfriend]

1. You can include a RunCode line in your macro and call a Function Name something like ExportMyFile()

Public Function ExportMyFile()

    'export as excel file
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryThatRetrieveMyInformation", "c:\temp\out.xls", True

    'export as text file
    DoCmd.TransferText acExportDelim, , "qryThatRetrieveMyInformation", "c:\temp\out.txt", True

End Sub

2. Copy your current table and pasted it. Chose Structure Only

Once it's pasted then create a query and run it.

Insert into tblNewTable
select * from tblOriginalTable WHERE Field1 = Yes

 

[Mike375]

You have a macro action called TransferText. Select that action then at the bottom of the macro design screen you will see the options. F1 will give help for each option. You can export or import.

If you want to move the Yes records to another table and also remove them from the existing table then make two queries

1) An Append query where the criteria for the Yes/No field is -1

2) A Delet query where the criteria for the Yes/No field is -1

Then use the OpenQuery action for each query in a macro. The first query will append the Yes/No records to the other table and the second query will delete them form the first table.

You might want to make the first action line SetWaning which will default to No. This prevents the warning coming..You are about to etc and etc.

SetWarning in a macro does not turn off the warnings except for when the macro runs. But if you want to be sure then after the OpenQuery action line add another SetWarning action and select Yes from the option list at the bottom of the macro design screen.

After you have made your macros and used them click on the macro name with the right clicker and select SaveAs and you will have the option to save as a macro or a module. If you select macro that is just to make a copy of the macro. Select Module and click OK. After it is finsihed click Module objects and you will see what your macros look like when done as code.

It is best to understand VBA or at least know what it looks like because most posters will tend to answer question that give the answer as code as was the case with ezfriend