append data from excel to existing table in access vba (2 Viewers)

[habinalshaikh]

Hello there

I have a database named "Dashboard" and I have an excel file named Report

I want to create a button that add the data from the excel file to the table "Dashboard"

what I did is this:

I created a button to get the excel file path to be in the textbox "txtPath"

but I need someone help me to do the add the data from the excel to the "Dashboard" table

thank you,,

 

[theDBguy]

Depending on what type of data you have, I would recommend importing the Excel data into a staging table first, so you can scrub it first, if necessary, before unintentionally contaminating the dashboard table.

 

[GPGeorge]

The VBA you need is like this:


Public Sub GetExcel()

DoCmd.TransferSpreadsheet TransferType:=acImport, SpreadsheetType:=acSpreadsheetTypeExcel12Xml, TableName:="Dashboard" "StagingTableName" , FileName:="C:\PathandFileNameExcel.xlsx", HasFieldNames:=True

End Sub

 

[habinalshaikh]

Depending on what type of data you have, I would recommend importing the Excel data into a staging table first, so you can scrub it first, if necessary, before unintentionally contaminating the dashboard table.

do you mean to import the excel data to a temporary table then add the data from the temporary table to the "Dashboard" table and then the temporary table will be deleted?

ok what is the code that I have to write?

 

[GPGeorge]

do you mean to import the excel data to a temporary table then add the data from the temporary table to the "Dashboard" table and then the temporary table will be deleted?

ok what is the code that I have to write?

If the table structure of the destination table (i.e. "Dashboard") does not change, I recommend not creating and deleting it. Rather I suggest you delete any existing records in the staging table prior to creating a new import. Either approach will create database bloat, but having the staging table with its structure makes the task of importing less troublesome, IMO.

 

[habinalshaikh]

The VBA you need is like this:


Public Sub GetExcel()

DoCmd.TransferSpreadsheet TransferType:=acImport, SpreadsheetType:=acSpreadsheetTypeExcel12Xml, TableName:="Dashboard" "StagingTableName" , FileName:="C:\PathandFileNameExcel.xlsx", HasFieldNames:=True

End Sub

thank you so much it works

 

[Pat Hartman]

When you have a process like this, the way to eliminate bloat is to use a template database. You create a database with however many temp tables you need. Create the columns and PKs as you need them with the correct data types. If your source data is really dirty, you might want two versions of the table. One correct version and the other with only text fields. In this case, you would import into the table with the text data types. Then your code and append query would append to the correct layout. The final step is to append to the permanent table in the normal BE.

To start each import cycle again, delete the local template database. Copy the master version from the server to your local folder. All the tables will remain linked as long as you don't change the name of the copy, so you don't have to relink anything. This allows you to always start with an empty, compacted template database so bloat never happens. If this appeals to you, i can post a sample db so you see how it works.