Solved

Add Import Date via VBA into MS Access

Posted on 2011-03-02
5
593 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses
Course of the Month7 days, 15 hours left to enroll

617 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