Import excel file into access and append a date to a field from unbound txtbox

[thmsjlmnt3953]

Hi,

I have a form which i use for a user to select an excel file they want to import and then click a cmd button to import the file into a table which works fine, however i want to append a date into a date field from an unbound txtbx before the file is imported so it will look something like;

id;date;excel info;excel info;excel info..............

however im not sure if this is possible?

any help would be great!

 

[namliam]

Best solution for this is to load your excel file into a temporary table then insert it into your perminant table and delete the temp table. (this is called a staging table)

Mind you, this will cause your database to increase in size (called bloating), advice: Turn on "Compact on close" in the options to prevent this from happening.

 

[thmsjlmnt3953]

That is something id thought about having read about this before howver im unsure how i would do this as ive never attempted before...

 

[namliam]

load into temp table (you know how to do this)
Run append query (Docmd.RunSQL "SomeQueryName", you can make the query in the query designer which I am sure you know how to do)
Run delete query (again make it in the query designer)

Just take it step by step, come back if you have any questions.

 

[thmsjlmnt3953]

i didnt realise it would be that simple - *bangs head* - thanks for explaining

 

[thmsjlmnt3953]

Hi,

I'm having another issue which i didnt have before however as my DB is now split my VBA to import the file into a temp table isnt working and im not sure how i would go about getting it to create a temp table in the back end. The code im using is as follows.

If DCount("*", "msysobjects", "[name]='tmpPicker_Stats'") > 0 Then
     CurrentDb.Execute "drop table tmpTable"
End If
If Me.txtFileName & "" <> "" Then
     DoCmd.TransferSpreadsheet acImport, 10, "tmpPicker_Stats", Me.txtFileName, True
     Else
     MsgBox "Please select excel file"
     Me.cmdFileSelect.SetFocus
     Exit Sub
End If

 

[thmsjlmnt3953]

HI,

Finally solved this, thanks very much for your help For anyone looking to do the same this is the following code i used:

txtPickDate.SetFocus
If txtPickDate.Text = Null Then
MsgBox "Please Enter a Date", vbOKOnly, "No Date"
End
End If
If DCount("*", "msysobjects", "[name]='tmpPicker_Stats'") > 0 Then
     CurrentDb.Execute "drop table tmpPicker_Stats"
End If
If Me.txtFileName & "" <> "" Then
     DoCmd.TransferSpreadsheet acImport, 10, "tmpPicker_Stats", Me.txtFileName, True
     Else
     MsgBox "Please select excel file"
     Me.cmdFileSelect.SetFocus
     Exit Sub
End If
DoCmd.RunSQL "INSERT INTO tblPicker_Stats ( pick_date, emp_id, zone1_lines, zone2_lines, zone3_lines, zone4_lines, zone5_lines, zone6_lines, zone7_lines, zone8_lines, total_lines )SELECT [forms]![frmNavigation]![NavigationSubform]![txtPickdate] AS Pick_date, tmpPicker_Stats.emp_id, tmpPicker_Stats.zone1_lines, tmpPicker_Stats.zone2_lines, tmpPicker_Stats.zone3_lines, tmpPicker_Stats.zone4_lines, tmpPicker_Stats.zone5_lines, tmpPicker_Stats.zone6_lines, tmpPicker_Stats.zone7_lines, tmpPicker_Stats.zone8_lines, tmpPicker_Stats.total_lines FROM tmpPicker_Stats;"
DoCmd.RunSQL "DROP TABLE tmpPicker_Stats"

Once again thanks for help!

 

[namliam]

Well done