Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 605
  • Last Modified:

Add Import Date via VBA into MS Access

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
oakvick
Asked:
oakvick
  • 3
  • 2
1 Solution
 
TinTombStoneCommented:
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
 
oakvickAuthor Commented:
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
 
TinTombStoneCommented:
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
 
oakvickAuthor Commented:
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
 
TinTombStoneCommented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now