Solved

Check if file has already been loaded

Posted on 2008-10-30
4
341 Views
Last Modified: 2012-05-05
I use this VB code to load a test file into an access table.  How can I add to this to check if I have already loaded the file.


Private Sub cmdImport_Click()
If IsNull(Me.txtFileName) Or Len(Me.txtFileName & "") = 0 Then
    MsgBox "please select the excel file"
    Me.cmdSelect.SetFocus
    Exit Sub
End If
'DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Import", Me.txtFileName, True
DoCmd.TransferText acImportDelim, "ImportSpecification", "STG_Load", Me.txtFileName, False
'DoCmd.TransferText acImportDelim
MsgBox "Your file has sucessfully loaded"
'Me.txtFileName = Null
End Sub

Thanks
Brian
0
Comment
Question by:bmcdowell540
4 Comments
 
LVL 3

Accepted Solution

by:
jdthedj earned 250 total points
ID: 22846199
You could set a global variable the first time you open the file, then check it next time.
0
 
LVL 7

Assisted Solution

by:rheitzman
rheitzman earned 250 total points
ID: 22846507
Or build a log table to track what has been imported. Say a table with two fields: Filename as Text, TimeImported as Date.

After import:
   CurrentDB.Execute :INSERT INTO Log (Filename, TimeImported) VALUES ("'" & me.txtFileName & "'",#" & now() & "#)"

Checking the log may get complicated if you can ever import the same filename twice. Otherwise use DLookup.
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 22859601
If you only do this once a day, you can add the datestamp directly into the Imported Data. Anything with "Today's" date would be considered already imported.

Outside of that, we really don't have enough information to make an educated suggestion.

Importing is a whole process and has to be designed with rollback capability for it to be considered complete. Without an import ID or Timestamp, with a record of the file name you're importing (or a directory location of where its archived once its imported) then you simply don't have enough of your design in place.

J
0
 
LVL 1

Author Closing Comment

by:bmcdowell540
ID: 31511893
Thanks for your help.  Sorry it took me so long to respond.  I ended up using the global because it was easy to implement but if the future I may need to create a log table.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

757 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

18 Experts available now in Live!

Get 1:1 Help Now