Solved

Add Import Date via VBA into MS Access

Posted on 2011-03-02
5
582 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

919 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

14 Experts available now in Live!

Get 1:1 Help Now