Logging Imports (1 Viewer)

radshar

Registered User.
Local time
Today, 13:12
Joined
Aug 5, 2016
Messages
32
Hello,

I'm using the below code to import data into a table daily.
I would like a way for me to log the date and time of each import. Is this possible?
Ideally I'd like a table to have the list of each import (they had different names), and the date and time.
In the code below 'File_import' would have/be the name of the file I'm uploading....

Code:
Private Sub Input_Pending_Report_BUTTON_Click()
 Dim objXLApp As Object
Dim File_import As Variant
                          
        Set objXLApp = CreateObject("excel.application")
        
          ' Ask the user for the file name to open.
        File_import = objXLApp.GetOpenFilename
        
    If File_import = False Then
        MsgBox "No file selected. Import cancelled"
        Exit Sub
        Else
            DoCmd.TransferSpreadsheet acImport, , "dlyERR_Cumulative", File_import, True
                    
            MsgBox "Import completed"
    
    End If
            
End Sub
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 13:12
Joined
Aug 30, 2003
Messages
36,118
Sure, you can execute an append query or use the AddNew method of a recordset.
 

sxschech

Registered User.
Local time
Today, 13:12
Joined
Mar 2, 2010
Messages
791
If you are including the data in the imported table you can use an update query, something like this, but you'll need to add a where clause to update the appropriate record, it may be as simple as where the filename is null, or if you are importing multiple files, may need more criteria...

Change tbl_Import to the table name
Change FileSource, ImportDate to the names of the fields you want the file name and import date to be updated with

Code:
...
...
If File_import = False Then
        MsgBox "No file selected. Import cancelled"
        Exit Sub
        Else
            DoCmd.TransferSpreadsheet acImport, , "dlyERR_Cumulative", File_import, True
'Add filename and date to the import table
            
            DoCmd.SetWarnings False
            DoCmd.RunSQL "UPDATE tbl_Import " & _
                         "SET tbl_Import.FileSource = '" & File_Import & "', " & _
                         "    tbl_Import.ImportDate = Now() "
                         "WHERE    tbl_Import.FileSource IS NULL "
            DoCmd.SetWarnings True
...
...
 

radshar

Registered User.
Local time
Today, 13:12
Joined
Aug 5, 2016
Messages
32
Hi!

thanks this was great! I was able to use the code but I had to remove:
Code:
        "WHERE    tbl_Import.FileSource IS NULL "

Now whats happening is that the previous entry is overridden by when a new import of data is completed. I actually wanted to keep a running tab. I'm assuming what I removed would have fixed that issue, but for some reason when I put it in the VBA it is red showing error...

If you are including the data in the imported table you can use an update query, something like this, but you'll need to add a where clause to update the appropriate record, it may be as simple as where the filename is null, or if you are importing multiple files, may need more criteria...

Change tbl_Import to the table name
Change FileSource, ImportDate to the names of the fields you want the file name and import date to be updated with

Code:
...
...
If File_import = False Then
        MsgBox "No file selected. Import cancelled"
        Exit Sub
        Else
            DoCmd.TransferSpreadsheet acImport, , "dlyERR_Cumulative", File_import, True
'Add filename and date to the import table
            
            DoCmd.SetWarnings False
            DoCmd.RunSQL "UPDATE tbl_Import " & _
                         "SET tbl_Import.FileSource = '" & File_Import & "', " & _
                         "    tbl_Import.ImportDate = Now() "
                         "WHERE    tbl_Import.FileSource IS NULL "
            DoCmd.SetWarnings True
...
...
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 13:12
Joined
Aug 30, 2003
Messages
36,118
The line continuation was missed. Try

Code:
            DoCmd.RunSQL "UPDATE tbl_Import " & _
                         "SET tbl_Import.FileSource = '" & File_Import & "', " & _
                         "tbl_Import.ImportDate = Now() " _
                         "WHERE tbl_Import.FileSource IS NULL "
 

sxschech

Registered User.
Local time
Today, 13:12
Joined
Mar 2, 2010
Messages
791
Thanks for catching that pbaldy. I had a different Where clause when I pasted it and then deleted the extra lines and forgot to fix them afterwards.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 13:12
Joined
Aug 30, 2003
Messages
36,118
No problem; we've all done it.
 

radshar

Registered User.
Local time
Today, 13:12
Joined
Aug 5, 2016
Messages
32
Hey,

So I'm still getting an error...."Expected: end of statement"
Any ideas why?


The line continuation was missed. Try

Code:
            DoCmd.RunSQL "UPDATE tbl_Import " & _
                         "SET tbl_Import.FileSource = '" & File_Import & "', " & _
                         "tbl_Import.ImportDate = Now() " _
                         "WHERE tbl_Import.FileSource IS NULL "
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 13:12
Joined
Aug 30, 2003
Messages
36,118
I don't see a reason right off. Try using a variable and send the finished SQL to the Immediate window:

http://www.baldyweb.com/ImmediateWindow.htm

Post the resulting SQL here. Perhaps something in the file name is messing it up. Or I'm blind. :p
 

sxschech

Registered User.
Local time
Today, 13:12
Joined
Mar 2, 2010
Messages
791
might not be the problem, but what if you add an ampersand as all the other continuations have?

"tbl_Import.ImportDate = Now() " & _
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 13:12
Joined
Aug 30, 2003
Messages
36,118
Brain cramp! That is likely the problem. I always have my ampersands at the beginning, so that's my excuse for not noticing. :p
 

radshar

Registered User.
Local time
Today, 13:12
Joined
Aug 5, 2016
Messages
32
Amazing!! that worked to clear the error!! thanks guys :)

But now it wont update the table....ARGH!! there I no winning for me here is there lol
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 13:12
Joined
Aug 30, 2003
Messages
36,118
Are/were there records where the FileSource field is Null? Those are the only ones that would get updated.
 

radshar

Registered User.
Local time
Today, 13:12
Joined
Aug 5, 2016
Messages
32
Hi!

I'm not sure I follow your question....

Below the last and only entry there is nothing there, is htat what you mean by 'FileSource field is Null?"?

thanks again for all your help!
Are/were there records where the FileSource field is Null? Those are the only ones that would get updated.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 13:12
Joined
Aug 30, 2003
Messages
36,118
The method is intended to be used where the import will populate most of the fields, leaving this one Null. This query will populate that last field. For example, the second time you run the process, the previously imported records will have this field populated but the new records will not. The query given will thus populate the newly imported records leaving the older ones alone.
 

radshar

Registered User.
Local time
Today, 13:12
Joined
Aug 5, 2016
Messages
32
Ok I might have done something wrong.

This what I have:
2 Tables

  • Daily_cumulative
  • tbl_Import
I have a button macro which when pressed prompts me for the file I want to upload. The data is uploaded to Daily_cumulative.
tbl_Import should display for me the name (or path) of the file I just uploaded as well as date/time.

If I understand you correctly. I need to add the fields "FileSource" and "ImportDate" to the Daily_cumulative table, correct?
I tried this and still I get nothing...

I'm so sorry about this, but I am really an amateur at all this :(


The method is intended to be used where the import will populate most of the fields, leaving this one Null. This query will populate that last field. For example, the second time you run the process, the previously imported records will have this field populated but the new records will not. The query given will thus populate the newly imported records leaving the older ones alone.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 13:12
Joined
Aug 30, 2003
Messages
36,118
Ok I might have done something wrong.

This what I have:
2 Tables

  • Daily_cumulative
  • tbl_Import
I have a button macro which when pressed prompts me for the file I want to upload. The data is uploaded to Daily_cumulative.
tbl_Import should display for me the name (or path) of the file I just uploaded as well as date/time.

If I understand you correctly. I need to add the fields "FileSource" and "ImportDate" to the Daily_cumulative table, correct?
I tried this and still I get nothing...

I'm so sorry about this, but I am really an amateur at all this :(

You chose the method that uses those fields, yes. My first suggestion was more in line with your setup (executing an append query or using a recordset).
 

Users who are viewing this thread

Top Bottom