Excel, Access linking problems

Peter Rattigan

Board Regular
Joined
Oct 18, 2004
Messages
77
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 :rolleyes:
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Harvey

Well-known Member
Joined
Nov 18, 2004
Messages
953
use odbc. Take a look at the openrecordset and outputrecordset commands. They let you run an update query.
 

Forum statistics

Threads
1,148,047
Messages
5,744,493
Members
423,880
Latest member
CRE_finance_guy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top