Solved

Add Import Date via VBA into MS Access

Posted on 2011-03-02
5
580 Views
Last Modified: 2012-08-14
Here is the process that I use to import data into my MS Access database:

"Sub events()
Dim strFile as String, strPath as String
strPath = "c:\temp\csv\"
strFile = Dir(strPath & "*.*")
Do While strFile <>""
DoCmd.TransferText acImportDelim, "Evt", "Events", strPath & strFile, False
strFile = Dir()
Loop
End Sub"

I would like to be able to append an import date associated with the importing of CSV data in the strPath.  Every week I import security event files into the database.  I would like to have an import date associated with the most recent import so that I can query the import date and capture only this import date data.  I would like also to keep the same import code but be able to add the code to do what I have described above.  Hopefully I have stated what it is that I am looking for.  Thank you, Joe
0
Comment
Question by:oakvick
  • 3
  • 2
5 Comments
 
LVL 6

Expert Comment

by:TinTombStone
ID: 35021645
You could log the import in another table as follows:

Sub events()
Dim strFile As String, strPath As String
    strPath = "c:\temp\csv\"
    strFile = Dir(strPath & "*.*")
    Do While strFile <> ""
        DoCmd.TransferText acImportDelim, , "Evt", strPath & strFile, False
        '--------------------------
        LogImport strFile
        '--------------------------
        strFile = Dir()
    Loop
End Sub


Sub LogImport(strFile As String)
Dim rst As DAO.Recordset

    Set rst = CurrentDb.OpenRecordset("tblImportLog")
   
    rst.AddNew
    rst!importdate = Date
    rst!Filename = strFile
    rst.Update

End Sub
0
 

Author Comment

by:oakvick
ID: 35022577
TinTomStone,

VBS stops and this line is highlighted yellow:

rst!filename = strFile

When I hover over the rst!filename portion of the line, I get this:

rst!filename = <Item not found in this collection.>

I manually created field in my database called "ImportDate" and changed my import convention file to include this field but it still yields same error.  I believe the cause is due to my CSV files not having this field.

Any thoughts?

Thank you,  Joe
0
 
LVL 6

Expert Comment

by:TinTombStone
ID: 35022864
Sorry, I should have said.

You need to create a table to act as the log

in this example, the code is looking for a table called 'tblImportLog'

Set rst = CurrentDb.OpenRecordset("tblImportLog")

with fields called importDate and FileName

rst!importdate = Date
rst!Filename = strFile




0
 

Author Comment

by:oakvick
ID: 35023217
TinTombStone,

Your code works great.  I think I can use it for my weekly log reviews but I was wanting to actually add the importdate field to the table containing the event logs.  This field would be updated for each new record that is imported into the events table.  I was just wanting to query on this date in lieu of the security log date since there could be overlapping security log dates from importing logs each week.  Thank you for your effort.
0
 
LVL 6

Accepted Solution

by:
TinTombStone earned 250 total points
ID: 35024969
Add an extra Date/Time field to the "Evt" table

Give it a default value of Date()

When you do the import, the defualt value will automaticaly be the day of the import

You could also set the default to Now(), which would record the time as well
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now