Excel, Access linking problems

[Peter Rattigan]

Hi

I have a large xl programme used for financial planning which is becoming a problem as the number of clients grows and data storage increases. What I would like to do is create a Access frontend for my programme which will provide the data storage and I will modify the Excel sheet to become a template which will open, link with the DB, produce results which will be stored in Access. The SS will be closed without storing any data.

Sounds straight forward but I am having a lot of grief in working out how to link the table in access to excel. If I export from Access it wants to create a new worksheet which is no good for the cell references in excel. If I link it creates a new linked table in Access which cant be related to the client tables in access.
Eg. If I have a tbl in Access called loans and I create say 2 loans for a client I would like to have Excel calculate the loan details and then store the data back in Access. The linking function creates a new tbl in Access and I lose the client connection.

Can someone please tell me how I can just input the data into a table (which is related to another table) in Access and link it to a spreadsheet without getting another table created.

Any help would be greatly appreciated.

thanks

 

[Harvey]

use odbc. Take a look at the openrecordset and outputrecordset commands. They let you run an update query.

 

[Peter Rattigan]

Thanks a lot I will look into it.
Regards

Peter