Solved

Check if file has already been loaded

Posted on 2008-10-30
4
345 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
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.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

749 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