Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Add Import Date via VBA into MS Access

Posted on 2011-03-02
5
Medium Priority
?
598 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 1000 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

715 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