0

I have this code that loads data from Excel to my tables in Access. I have the last column as [Load_date] in all my tables, and in Design View of my tables I am using the function =Now() for the Default Value.

My problem is each of my table has different time stamp differ by few seconds. How can I update my VBA Code to import data in all tables at the exact same time?

Sub ImportAllTables_New_Click()
Call LoadData("C:\Idea Attributes\tbl_IdeasITAssumptions.xlsm", "TempIdeasITAssumptions", "Qry_IdeasITAssumptions", "Qry_AppendIdeasITAssumptions")
Call LoadData("C:\Idea Attributes\tbl_IdeasDependencies.xlsm", "TempIdeasDependencies", "Qry_IdeasDependencies", "Qry_AppendIdeasDependencies")
Call LoadData("C:\Idea Attributes\tbl_IdeasImpactedPlan.xlsm", "TempIdeasImpactedPlan", "Qry_IdeasImpactedPlan", "Qry_AppendIdeasImpactedPlan")
Call LoadData("C:\Idea Attributes\tbl_IdeasImpactedSubsidiaries.xlsm", "TempIdeasImpactedSubsidiaries", "Qry_IdeasImpactedSubsidiaries", "Qry_AppendIdeasImpactedSubsidiaries")
Call LoadData("C:\Idea Attributes\tbl_IdeasLOB.xlsm", "TempIdeasLOB", "Qry_IdeasLOB", "Qry_AppendIdeasLOB")
Call LoadData("C:\Idea Attributes\tbl_IdeasPhaseGate.xlsm", "TempIdeasPhaseGate", "Qry_IdeasPhaseGate", "Qry_AppendIdeasPhaseGate")
Call LoadData("C:\Idea Attributes\tbl_IdeasDataExtractMain.xlsm", "TempIdeasDataExtractMain", "Qry_IdeasDataExtractMain", "Qry_AppendIdeasDataExtractMain")

End Sub

Sub LoadData(Filepath As String, TempTable As String, Qry_Ideas As Variant, Qry_Append As Variant)

If FileExist(Filepath) Then
    DoCmd.TransferSpreadsheet acImport, , TempTable, Filepath, True

     'The following will Check for new Load_Date , if it is not new you will get no new data msg. This function is currently not useful since we are using Now() function in our tables.
     'But if in future we need to use it, delete now() in tables it self under Load_Date Default Value.
    If IsNull(DLookup("(idea_code)", Qry_Ideas)) Then
        MsgBox "No New Data to add"
    Else
        DoCmd.OpenQuery Qry_Append, acViewNormal
    End If
Else
    MsgBox "File not found. Please check the filename or File Location."

End If

'Use Sql Command to delete everything in Temp Table
Dim SQLDelete As String

    SQLDelete = "Delete * from " & TempTable
    DoCmd.RunSQL SQLDelete
End Sub
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
user3860954
  • 101
  • 10

2 Answers2

2

  1. Ditch the default value of now() for [Load_date] so it will be null after import

  2. decide if you want the timestamp to be captured before import starts or after import ends. (See 2A and 2B)

  3. after all tables are imported, run an update query on your table to update [Load_date] col with timestamp where [Load_date] is null

2A. Before import starts

  Sub ImportAllTables_New_Click()
    dim dt as date
    dt = now() 'save current single timestamp for later use

    'do all of your loaddata() calls

    'update all [Load_date] null values to the timestamp captured before you started import
    'the only nulls are going to be the newest records added
    'no problem to run this if there are none.
    docmd.runsql "UPDATE [yourtablename] set [Load_date] = #" & dt & "# where [Load_date] is null"

End Sub

2B. After import is complete

  Sub ImportAllTables_New_Click()

    'do all of your loaddata() calls

    'update all [Load_date] null values with current timestamp
    'the only nulls are going to be the newest records added
    'no problem to run this if there are none.
    docmd.runsql "UPDATE [yourtablename] set [Load_date] = now() where [Load_date] is null"

End Sub

I hope that helps. You might want to look into turning warnings off before running SQL and I would highly recommend adding some error handling to your subs

PractLogical
  • 248
  • 1
  • 6
  • Thanks, I am still learning VBA, would you please tell me how to turn warnings off, and how would I add Error Handling for my subs? – user3860954 Aug 28 '15 at 20:32
  • Thanks PractLogical, but I have a issue. I am keeping historical data in my tables. And My Queries were looking at previous Load_Date to import new data. If I import data a min ago, and If i want to import the same data at different time, it won't import it since i no longer have now() function for load date in default values – user3860954 Aug 31 '15 at 14:47
  • I used the following code, and it worked. Thanks for your help: – user3860954 Aug 31 '15 at 18:30
  • Hi @user3860954, sorry i've been too busy to get on here for a bit. DoCmd.SetWarnings False (This will turn off the prompts to update/delete records when running SQL) Error Handling is important and basic in any programming language so you should learn all you can. I found this on a search of this site: http://stackoverflow.com/questions/1038006/good-patterns-for-vba-error-handling . The most important part of turning warnings off is to turn them back on! So, be sure to ALWAYS put DoCmd.SetWarnings True at the start of error handler if it was set to false. – PractLogical Sep 11 '15 at 17:29
  • @user3860954, I'm afraid that I don't understand your other issue and the datestamps in here are a little confusing...but from what I can tell, you are all set. – PractLogical Sep 11 '15 at 17:37
1

Capture the timestamp of "now" at the start of the script and store it in a variable. Then use that variable in place of the "now" function in your queries

Ryan Smith
  • 41
  • 2