I'm trying to automate Ms Access through Excel. Basically< I need to open Access from Excel then insert data from a spreedsheet into an Access Table. I know your probably thinking "Why don't you just link the table to the Excel spreedsheet?". I'd love to, but unfortunately, when I do this I'm not able to control the data type of the fields of the linked table.
If you know of a way to control the data type from a linked table, then cool. But right now, the only thing that I can think of is to set up a program that will delete the current data and insert the new data from the Excel spreedsheet. By doing this, I can set up the data type in my table as a text file and insure that it won't change.
I've tried going into Excel/Format/Cells and setting up the column as a text type. Did'nt work. I guess because all the items in the column are numbers, Access keeps setting up the data type as a number.
Ideally, I would love to take my old data from my table and transfer it into a new table with a name format like ("tblReport"& Date). Its funny, but everything I've read on Ole Automation talks about control Excel thru Access but not controlling Access thru Excel.
I'd be thankful for any assistance.
Thank You
