Solved

Check if file has already been loaded

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

910 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

20 Experts available now in Live!

Get 1:1 Help Now