Append from one table to another


I have the following code which imports an excel file into a temp table in my application.  I am trying to work out how I can then append/insert that data into my live table with an additional field value for each record being inserted.

Dim dbs As DAO.Database
Dim td As DAO.TableDef
Dim fd As Office.FileDialog
Dim BatchID As string

   ' set dbs to current database
   Set dbs = CurrentDb
   ' loop through all tables in current database
   For Each td In dbs.TableDefs
   ' check if Temp table is still in current database
      If td.Name = "DLPTemp" Then
        ' it is, so drop it
         dbs.Execute "Drop Table DLPTemp;"
      End If

            Set fd = Application.FileDialog(msoFileDialogFilePicker)

   With fd

      .AllowMultiSelect = False

      ' Set the title of the dialog box.
      .Title = "Please select the file to impot."

      ' Clear out the current filters, and add our own.
      .Filters.Add "Excel 2003", "*.xls"
      .Filters.Add "All Files", "*.*"

      ' Show the dialog box. If the .Show method returns True, the
      ' user picked at least one file. If the .Show method returns
      ' False, the user clicked Cancel.
      If .Show = True Then
        txtFileName = .SelectedItems(1) 'replace txtFileName with your textbox

      End If
   End With
   ' import table
      DoCmd.TransferSpreadsheet acImport, , "DLPTemp", txtFileName, True
   ' execute the insert SQL from temp table to Export

BatchID = me.Batch_ID
   dbs.Execute "INSERT INTO dbo_tbl_reference_data ([Batch_ID], [Reference1], [Reference2], [Reference3], [Reference4], [Reference5], [Reference6]"
'The Batch_ID field is the only field which is not present in the Excel document or temp table.  For each record being inserted into tbl_reference_data i need to assign the Batch_ID.  
   ' drop table
   'dbs.Execute "Drop Table DLPTemp;"
  ' release objects
   Set dbs = Nothing
   Set td = Nothing

End Sub

Open in new window

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
How do you assign the Batch ID? Is there some sort of logic involved in doing so, or is it just a random number/

If it's a Random number, just set your tables Batch_ID Column to AutoNumber, and Access will take care of it. You'd need to remove it from the INSERT string, of course.
anthonytrAuthor Commented:

The batch_id has already been created by the time the user clicks the import button and is available on the form (although hidden).  So I need to import all the columns from the temp table with the addition of the batch_id.

The batch_id is just the FK not PK.

When you don't know how to create SQL, you might be able to use the QBE to help you.

1. Link to the spreadsheet.  Do not import it.  Importing it is just causing extra bloat.
2. Open QBE to create the append query.  Select the linked spreadsheet and close the table picker.  Select all the columns you want.  You can double click on each, one at a time or you can use Cntl-click or shift-click to select blocks of them.  Drag the selected blocks to the grid.
3. Change the query type to Append.
4. Choose the table you want to append to.
5. At this point, every column except the batchID should be accounted for.  In an empty column, in the Field cell, type [EnterBatchID].  Then in the Append to, choose the Batch ID.

At this point, you can save the query as a querydef and use it that way,  If you prefer to have embedded SQL, switch to SQL view and select the generated SQL and paste it into your code module.  Using embedded SQL, you'll have to do some touch up to the  form field reference.  You will need to break the string into three parts.  The first is everything before the form field reference, the second is the form field reference and the third is everything after the form field reference.  What you will end up with is something like:

strSQL = firstpart & Me.batchID & thirdpart

Notice that the specific field reference replaced the [enterBatchID] place holder.

Rather than attempting to do this for you, I am trying to teach you how to do it for yourself so you can use the QBE as a helper in the future rather than relying on your own knowledge of SQL syntax.

Personally, I rarely use embedded SQL.  I prefer to use querydefs.   To use a querydef in code when that querydef has a parameter requires that you populate the parameter  ahead of time.

dim db as DAO.Database
dim qd as DAO.Querydef

set db = CurrentDB
set qd = qMyAppendQuery
      qd.Parameters!EnterBatchID = Me.BatchID

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

anthonytrAuthor Commented:
Thank you PatHartman,

This answer has really helped.   I agree with you and I do prefer to know how to achieve something and actually know the reason behind a solution.

I do occasionally use QBE to create a query and then look at the SQL syntax to work out how to embed it into code.  I seem to be over thinking this problem.  

I didn’t know of querydefs in ms access and using them in code as well.  That is something I will use again.  Your explanation was very helpful indeed.  thank you!

You are very welcome.  Please don't forget to close the question.

Also - I noticed a typo

set qd = qMyAppendQuery

should be

set qd = db!qMyAppendQuery
anthonytrAuthor Commented:
Just thought, if I am only linking to the excel spreadsheet, does it matter that this document could be called something different each time the import runs?
anthonytrAuthor Commented:
Sorry, I think I’m being a little dim here.  When you say “save it as a querydef” how is this achieved?
When you create a query using the QBE and save it, what you have saved is a querydef.  That is what saved queries are called.  The first time Access runs a saved querydef, it calculates an execution plan for how the query will do what it needs to do.  Then every other time the querydef is executed, Access uses the saved execution plan.  When you use embedded SQL, Access doesn't have a saved querydef to hold the execution plan it needs so it recalculates the execution plan each time the embedded SQL runs.  Calculating the plan is pretty quick and current versions of Access don't bloat when doing it so unless you are going to run a particular query multiple times each time the app opens, it probably won't make a whole lot of difference in application speed but it could.

When I link to spreadsheets whose actual name will be different each time I link (very typical), I standardize the name of the linked table.  Since my queries refer to the "table" name rather than the name of the actual spreadsheet, they will be fine even if you change the name of the physical spreadsheet when you link.

So, the workbook might be named C:\folder1\folder2\Invoice_20171231.xlsx and you might link that as tblInvoiceSheet_Link  Then in February, the file name might be C:\folder1\folder2\Invoice_2018_0131.xlsx but the "table" name will still be tblInvoiceSheet_Link so all your queries will still wor,k.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.