Solved

Check if file has already been loaded

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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

734 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