Solved

Importing data from excel to temp table then appending to existing table with autogen primary key

Posted on 2007-12-02
1
438 Views
Last Modified: 2008-02-01
I am looking for a way to copy data from an excel spreadsheet to a temp table in access 2003 and if it imports correctly to append the data to the main table.  The spreadsheet importtemp table and finalimport table will all have the same field names.  The finalimport table will have data inputting to this table from multiple parts of the database and there is an auto generated key for each new line.

I believe I can import the code using the code example below to the temp table. But how can I copy the code from the importtemp table to finalimport table.  The fields will be the same and in the same order. I think I can use the append command but I am not sure how to do it with the auto generated key in the finalimport table.

This is all in access 2000 excel 9. I prefer to is vba as apposed to sql but whatever works.  Initially I thought to input it into the finalimport table but importing external data into the final table didn't seem right.

Sub ImportSpreadsheet()

     DoCmd.TransferSpreadsheet _

          TransferType:=acImport, _

          SpreadsheetType:=acSpreadsheetTypeExcel9,_

          TableName:="tableCustomerSpread", _

          FileName:=CurrentProject.Path & "\customer.Xls", _

          HasFieldNames:=True

End Sub
 
 
 

Snippet from "Mastering Microsoft Office Access 2003" by Alison Balter c2004 Sams Publishing

Open in new window

0
Comment
Question by:omnova
1 Comment
 
LVL 10

Accepted Solution

by:
LennyGray earned 500 total points
ID: 20394175
SEE ATTACHED CODE.

THIS WILL IMPORT THE DATA FROM eXCEL AND ASSIGN THE PROPER DATA TYPE TO THE IMPORT INTO aCCESS.
Private Sub ConvertToTextFile(sTheTableName As String)
 

    '**********************************************************************

    '* The purpose of this routine is to overcome Microsoft's sloppiness. *

    '* After the raw Excel data is transferred to Access, we export the   *

    '* imported Excel data from the raw Access data table to a text file. *

    '***********************************************************************

    DoCmd.TransferText acExportDelim, , sTheTableName, sTheCurrentDatabaseDirectory & sTheTableName & ".txt", True

    

End Sub
 
 

Private Sub ConvertToAccessTable(sTheTableName As String)

Dim sTheFinalTableName As String
 

    sTheFinalTableName = Mid(sTheTableName, 1, Len(sTheTableName) - 4)

    '**********************************************************

    '* The next step is to return the text that we converted  *

    '* back to the Access database as a table defined by an   *

    '* Import Specification file. This is the "Raw" data file.*

    '**********************************************************

    

    DoCmd.TransferText acImportDelim, "AwardDataImportSpecification", sTheFinalTableName, sTheCurrentDatabaseDirectory & sTheTableName & ".txt", True
 
 

End Sub

Open in new window

0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

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

22 Experts available now in Live!

Get 1:1 Help Now