Data Import automation

  • Thread starter Thread starter Toast
  • Start date Start date
T

Toast

I am using Access 2000 on a generic WinTel machine under Win2K

I am new to writing access macros, so I need some very clear help please.
1. Where do I go to look at past messages from this group before posting
requests for help?
2. I need immediate help with the following macro:
I need to read a disk directory for all files Bus*.csv
I need to import each of the files found into a table Business
(This table already exists and need to be appended to)

Thanks in advance for any and all help!
--
Gregory Della-Croce
(e-mail address removed)
630.248.0765

Building solutions for individuals, teams, and organizations for two decades
 
1. www.google.com/groups/
This search engine will let you search previous posts to newsgroups.

2. You can't do what you want with an ACCESS macro. But you can do it with
VBA code. Something like this:

Dim strFile As String, strPath As String
strPath = "C:\MyFolder\"
strFile = Dir(strPath & "Bus*.csv")
Do While strFile <> ""
DoCmd.TransferText acImportDelim, , "TableName", strPath & strFile
DoCmd.OpenQuery "AppendQueryName"
DoCmd.OpenQuery "DeleteQueryName"
strFile = Dir()
Loop

Where
-- TableName is the name of a temporary table that will receive the
data from the .csv files (the table should be empty when you start)
-- AppendQueryName is the name of an append query that you've created
and stored that appends the data from TableName into the permanent table
-- DeleteQueryName is the name of a delete query that you've created
and stored that deletes all data from TableName
 
Ken,
Thanks! This is excellent!

Greg.
Ken Snell said:
1. www.google.com/groups/
This search engine will let you search previous posts to newsgroups.

2. You can't do what you want with an ACCESS macro. But you can do it with
VBA code. Something like this:

Dim strFile As String, strPath As String
strPath = "C:\MyFolder\"
strFile = Dir(strPath & "Bus*.csv")
Do While strFile <> ""
DoCmd.TransferText acImportDelim, , "TableName", strPath & strFile
DoCmd.OpenQuery "AppendQueryName"
DoCmd.OpenQuery "DeleteQueryName"
strFile = Dir()
Loop

Where
-- TableName is the name of a temporary table that will receive the
data from the .csv files (the table should be empty when you start)
-- AppendQueryName is the name of an append query that you've created
and stored that appends the data from TableName into the permanent table
-- DeleteQueryName is the name of a delete query that you've created
and stored that deletes all data from TableName
Click to expand...
 
Back
Top