I have a pawnshop app where when a customer arrives at the shop, the clerk uses an Excel worksheet to calculate how much money he can lend to the customer, based on the current market price of gold, gold karats and weight. If the customer agrees with the calculated amount, the clerk then goes into my Access 2007 pawnshop app and has to re-enter the price of gold and the total weight for each type gold karats. I would like to know if there's a way for my Access app to automatically plug-in the values which were entered in the Excel worksheet.
views:
40answers:
2
+1
A:
There might be a way, but I would implement the logic inside of a little database app with a calculate and apply button.
aking1012
2010-10-31 03:26:15
that's a good option, however I always thought it would be possible to acomplish my objective with DDE (dynamic data exchange) or VBA. I just don;t know how to go about doing it.
Frank Computer
2010-10-31 03:31:48
+2
A:
From VBA within Excel, you could create an ADODB.Connection with a connection string pointing to the Access Database. Directly with the Connection object (but preferrably with an ADODB.Command object) you could run SQL inserts into the Access database that pulls its data from a Range on the worksheet.
This is assuming you have some sort of VBA knowledge which I am uncertain if you do as this question was not tagged with VBA.
Edit: You also need to make sure you include the Microsoft Data Access Objects Reference to the VBA project. (Use the latest version available to you)
pinkfloydx33
2010-10-31 03:36:34
that sounds plausible.. but what if I'm using accdb jet and not SQL in access'07?
Frank Computer
2010-10-31 03:39:05
the language is still "SQL". I forget the actual connection string for access db's (a quick google should help you--I know the word 'JET' is in there somewhere!). Then you can do something like myConnection.Execute("Insert into sometable (somefield) values ( somevalue)")
pinkfloydx33
2010-10-31 03:41:33