Is this possible?

[systematic]

Hi all,

Just wandering if this is possible within vba by clicking a cmd button.

1. Import a spreadsheet into a new table (tblTEMP)
2. Run a delete qry (qryDELETE)
3. Run an append qry (qryAPPEND)
4. Delete the table that was just imported (tblTEMP)

If so, could anyone give me any advice on where to start?

Thank you

Robert

 

[Smart]

if you create a macro and in the action column select transferspreadsheet
Transfertype = Import
Spreadsheet Type = Microsoft Excel8-10 (type of spreadsheet)
Table name = tablename (table name in the database it will created for you)
Filename = c:\a\aaatest.xls (the pathname of the spreadsheet)
has field names = yes

then select file , save as and from the drop down box select macro then click save. you can now design the module an copy the code to your button

The code for the above macro once saved in the module looks like this;


DoCmd.TransferSpreadsheet acImport, 8, "mm", "c:\a\aaatest.xls", True, ""

 

[systematic]

Champion! Thanks

 

[modest]

I avoid macros.

DoCmd.TransferSpreadsheet 'use help if you dont understand parameters
DoCmd.RunSQL "Query Name or Delete SQL here"
DoCmd.RunSQL "Query Name or Append SQL here"
DoCmd.RunSQL "Drop Table [tempTbl]"