Save Data to Access

[sabloomer]

I have an Excel 2007 sheet that has several data tables, Access is the source. Some tables are large, others small. My goal is to have the users modify the data in the tables and then save it back to Access.

I know I can loop through a range and execute one append/update transaction per line, but I would really like to Excel append all the records at once into a blank staging table in Access. Is there anyway to make a recordset out of a range on a worksheet? If so, can I pass that recordset to Access?

Thank you to all that take the time to read this.

sabloomer

 

[PHV]

Why not using the DoCmd.TransferSpreadsheet method of the Access.Application object ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[combo]

If you plan to work with the whole table in excel, why not reverse the process? I mean storing data in excel tables and link to them in access. Alternatively, use queries to transfer data from linked table to table in access file.

combo

 

[sabloomer]

Thank you for the suggestions. Here is a little more about this problem.

The data needs to be pushed from Excel to Access. The Excel file(s) will move and change name, but the Access database will not move.

If the file moves, and there are multiple copies, I don't think I can use the linked table method.

Can I use DoCmd.TransferSpreadsheet method of the Access.Application object from Excel? I will look more into this.

Thank You,

sabloomer

 

[mrdod]

Why don't you use an ADO connection and send info using an SQL statement? You can store it in your personal macro workbook. Just define the range and while looping through the range use an "ISERT INTO" statement. I'm sure there is a FAQ on it some where. If not I can even help you with this one even though I'm by no means an expert.

 

[sabloomer]

mrdod,

Thank you for the feedback. I was trying to avoid having to write one record at a time, but that may not be an option. If I can't find a bulk method soon I will using your suggestion.

Thank You,

sabloomer