Appending an Excel directly into an existing table in Access

[FuzMic]

Hi friends

Let say i have an Excel table and want to append to an existing mdb table in my BEnd with the same field type & fields; can i do with VB codes. I would like to do all this with Office.03.

I know how to import an Excel as a new FEnd table & then append to my BEnd target table; but that is not want i like to do. I am trying to avoid an
extra table being created in the FEnd.

Appreciate suggestion, Thanks!

 

[Gasman]

I *thought* you could append to any table, be it FE or BE?

 

[arnelgp]

Insert Into backEndTableName In 'D:\Path\BackEnd.accdb' SELECT * FROM [Excel 12.0 Xml;IMEX=2;HDR=YES;ACCDB=YES;Database=D:\EXCELNAME.XLSX].[SHEETNAME$];

 

[sneuberg]

Insert Into backEndTableName In 'D:\Path\BackEnd.accdb' SELECT * FROM [Excel 12.0 Xml;IMEX=2;HDR=YES;ACCDB=YES;Database=D:\EXCELNAME.XLSX].[SHEETNAME$];

That works nicely but I suggest that the OP note that HDR=YES seem to mean that the first row in the spreadsheet are considered field names. If the spreadsheet just contains data that should be HDR=NO

 

[sneuberg]

@ Mr. arnelgp

Could you provide us a reference for how the FROM part of that statement is formed? I'd like to see what other options are available.

 

[sxschech]

Here is an example if there are no headers. The From part you specify the excel file, the sheetname and the range.

stsql = "SELECT F1 " & _
            "FROM [Excel 12.0;HDR=NO;IMEX=1;Database=" & PathFile & "].[" & sheetname & "$A1:A10000] AS T1 " & _
            "WHERE F1 Is Not Null"

 

[FuzMic]

Thanks guys, all noted all to expand my horizon.

Meanwhile i just use

DoCmd.TransferSpreadsheet acImport, 8, _
"Target-Tbl", "source-Excel.xls", True

The only thing is the Excel file must have all the name of the files else there will be an error message, even if we tell Access there is the first line is not fieldNames.

So in short we have to both Excel & Access.tbl with same field names. Is there another variant on this.

Thanks again.

 

[sneuberg]

If the Excel file doesn't have field names (HDR=NO) you can replace the * after SELECT with F1,F2,F3...Fn where n is the number of fields. Then list the field names in the database table after the table name. For example I named the databases fields Field1,Field2, and Field3 and the following line of code worked for me.

CurrentDb.Execute "Insert Into Table1 (Field1,Field2, Field3)  In 'C:\Users\sneuberg\Documents\Access Projects\InsertExcelIntoBackend_be.accdb' SELECT F1, F2, F3 FROM [Excel 12.0 Xml;IMEX=2;HDR=No;ACCDB=YES;Database=C:\Users\sneuberg\Documents\Access Projects\InsertExcelInBackend.xlsx].[Sheet1$];", dbFailOnError

 

[FuzMic]

Using TransferSpreadSheet has two puzzling phenomena

1. An Excel03 file is first filled with say 1+3 rows x 5 columns with 1st row being heading. When i import the content to a table in Access.03 it took in more than 3 rows (300 plus). Why?
2. The same Excel03 file, then had the 3 data rows ERASED, next import this blank Excel file, 3 old rows came back. Only if i DELETE the 3 rows, it will imported as no data. Why?

Any advice friends?

 

[AccessBlaster]

I know how to import an Excel as a new FEnd table & then append to my BEnd target table; but that is not want i like to do. I am trying to avoid an
extra table being created in the FEnd.

Appreciate suggestion, Thanks!

Who says you have to create a table in the front end?

 

[FuzMic]

Thanks ABlast, i got your point but how to do your suggestion in VB6.

 

[sneuberg]

Thanks ABlast, i got your point but how to do your suggestion in VB6.

If you can get this to work for you at the end of the process you get a screen like:

You can run these steps in VBA with the RunSavedImportExport method.

 

[FuzMic]

I am sticking to using the simple

DoCmd.TransferSpreadsheet acImport, 8 ...

When it arrive, just clean up the mess (if any) that may come in eg extra blank lines by SELECT...