resources on vba and macros for importing text files into Access

[r_darling]

Hi to all,
I found this forum while searching the Internet in frustration on the process of updating Access databases. So far I've found out that macros in Access aren't as east to create as they are in Excel; which I've been doing successfully for years.

Could someone suggest a good "soups to nuts" reference or resource?
It seems simple what I want to do:

Open an existing Access DB; delete all records in existing tables, and read new data into those tables from a text file. I then run a query and output the data to an Excel spreadsheet.

Thanks.
Richard

 

[llkhoutx]

Litwin's Access Developer's Handbook, Desktop Edition, is widely recognized as one of the best. It comes in 97, 2900, and 2002 Editions. Like new copies are very cheak on amazon.com.

 

[r_darling]

Thanks - I'll check it out!

 

[r_darling]

After sniffing around different sites I put together this macro. The DoCmd is very slick and easy...as stated several times on this forum VBA is the way to go.

Sub importSeasonalSales()

DoCmd.RunSQL "delete from Gr_orddtl2"
DoCmd.RunSQL "delete from Gr_ordhdr"
DoCmd.TransferText acImportDelim, , "gr_ordhdr", "C:\Documents and Settings\rdarling\My Documents\downloads\gr_ordhdr.txt", True
DoCmd.TransferText acImportDelim, , "Gr_orddtl2", "C:\Documents and Settings\rdarling\My Documents\downloads\gr_orddtl.txt", True
DoCmd.OpenQuery ("cust_query_CURRENT"), acViewNormal, acReadOnly
DoCmd.OutputTo acQuery, "", "MicrosoftExcel(*.xls)", "", True, ""
DoCmd.Close acQuery, "cust_query_CURRENT"

End Sub

 

[r_darling]

make vba macro available - can only run it in VBA editor

This one is driving me batty - I finally got the vba code to work, but I can only run the VBA macro from within the VBA editor. When ever I try to run it form anywhere else the run button is greyed out.

 

[llkhoutx]

Put the code in a class module as a public subroutine, then call that subroutine as appropriate.

 

[r_darling]

Need to do some research - I was looking to be able to open the db, select tools->macro->run macro and select it

 

[llkhoutx]

Assuming you have a form which opens when you start the mdb, put the code in the the "OnOpen" event of that form.

 

[r_darling]

thanks for the information.
RD