Importing/Linking Daily Files

[EBox]

Hello:

I have an Access DB which I import a daily text data file into and then run an append query in order to aggregate it to a cumulative table. I was wondering if there is a way to set up a macro or VB to automate this for me.

The daily file name format is ED_Archiveyyyymmdd.txt (example: ED_Archive20091001.txt for October 1st data).

Would welcome any thoughts-

Thank you!
EBox

 

[dhookom]

Do you want this to happen without you doing anything or just automate to the point of clicking a button on a form?

Do you have any functionality now such as import specifications or append queries?

Duane
Hook'D on Access
MS Access MVP

 

[EBox]

I would love for it to happen automatically, if that's possible, instead of having to push a button on a form.

I do have the import specs loaded up into Access currently, but do not have any append queries. I have been manually just taking all the fields from the imported table and creating, then running a append query to a master table. All the fields from each daily file correspond exactly to the master table.

Thank you!

 

[dhookom]

I would start by saving the append query so you can use a little code to just run it. You can write some code using

docmd.TransferText ....

You should be able to use your file name pattern to correctly name the import file.

Once you have this piece working, you can create a macro that can be called in the command line to kick off the process.


Duane
Hook'D on Access
MS Access MVP

 

[EBox]

What would the code consist of? I am not really a coder, so any help you could offer would be appreciated.

Thank you,
EBox

 

[dhookom]

I'm not going to provide all the code. There is intellisence when writing code to help you. This is some code for importing a worksheet from an Excel file with the location contained in a text box.

If Len(Me.txtRawDataFile & "") > 0 Then
   strExcelRawDataFile = Me.txtRawDataFile
   If Dir(strExcelRawDataFile) = "" Then
      MsgBox "Your path to the SAP RawData File is incorrect. Please update it.", _
          vbCritical + vbOKOnly, "Missing File"
       Exit Sub
   End If
End If
strSQL = "DELETE * FROM tblTempImport"
CurrentDb.Execute strSQL, dbFailOnError
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblTempImport", strExcelRawDataFile, True, "RawData"

Duane
Hook'D on Access
MS Access MVP