Transferring Excel Columns to Access Fields

[mahmud1180]

Hi there!! I want to run a VBA code that makes a column in the excel file and then transfer it to Access with a certain Field Name. Then this excel Column is replaced by another set of Values and I want to transfer it to Access with another Field Name.I have a recurring procedure so, I can not use the AccessImport Method. I can add values cell by cell but I want to avoid that as it is gonna take more time. Can anyone help? Thanks a lot!!

 

[sheetspread]

instead of SELECT * can you write out the field names?

 

[mahmud1180]

I think the problem lies with the "Inset Into ..... Select From " statement where it picks up the data from Excel worksheet not from the Access table. Can you please give me a correct syntax for that? Thanks a lot!

 

[mahmud1180]

instead of SELECT * can you write out the field names?

There are no field names to select as I am trying to get the values from Excel.

 

[sheetspread]

You're writing a query of Excel data which is fine, but without headers how will Access know which field names to assign?

 

[mahmud1180]

You're writing a query of Excel data which is fine, but without headers how will Access know which field names to assign?

I think "Select *" selects the range (like A1:A100) of cells in Excel. That's why I said there's no field name

 

[sheetspread]

That's right, but is your Access database updating those records to existing fields or creating new fields with names like 1000000 etc.?

 

[mahmud1180]

It's updating the excel records to a existing field. It's not creating any field with the name of 1000000 (at least that's what I think)

Can you give me an example of query of excel data? I tried "Insert into Values()" sytax. This works with a single record but not with multiple records. or Can I insert into a table from a recordset? I think I can. But that wasn't working either with multiple records :/

 

[sheetspread]

I tested the code here:

https://support.microsoft.com/en-us/kb/145826

which successfully appended an Excel spreadsheet to an Access database. The fields were set up identically in each, and I named the Excel range rather than leaving it as an address. I only have Access 2003 which might not be able to handle the later ADO methods, and saved the spreadsheet in Excel 2003 even though 2010 would probably be ok too.

 

[mahmud1180]

I tried the code but I still get the same error "The INSERT into statement contains the following unknown field name: '10000000'. Make sure you have typed the name correctly" . 100000000 is actually the first value in the data range

 

[sheetspread]

Wait I'll try to find a way to turn off the headers import

HDR = No?

(in the first code you tried)