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

x
?
Solved

Check if file has already been loaded

Posted on 2008-10-30
4
Medium Priority
?
351 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 1000 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 1000 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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

730 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