Linking Excel and Access to import a single record from DB

[Guest]

friends,

I posted this in the "Excel Progromming" section but I didn;t get a response
so I thought here.

I have an Access database (Customers) and an Excel template (Quote
Template). I would like to open the Quote template , click a command button
within the workbook, ( I need a macro for this) and bring up a list of
record (Customers) from the
database. I would then like to be able to choose a single record from the
list and have the appropriate info imported into the Excel template.

I have searched the board rather extensively and not come across this
scenario. Your help would be most appreciated.

Thanks in advance

Steve

 

[Guest]

hi Steve,
I saw your post in programing. I was hoping someone more experience than me
would respond. I have been transfering data between excel and access for
about 8 years now and i am not aware of any way to do what your want. you can
transfer data between the two quite easily but i have never been able to link
the two at least not like linking 2 excel sheets.
how would i do it?
1. Create a Microsoft Query (MSQ) to download the customer data(on a second
sheet). You would need an access query for this and the MSQ would use the
access query as it's data source.
2. Once the customer data has been downloaded, you would need a second MSQ,
a second Access query and a second button. MSQ can use the contents of a cell
as a selection parameter for the second access query. select the customer ID
and run MSQ2.
3. once the selected customer data has been download (to a second area on
the sheet), you would need an excel macro to populate your template which
would transfer each field of the MSQ to the template one field at a time. the
populate macro could be attached to the second command button to fire after
the down load.

except for the decision time to select the customer from MSQ1, the rest of
the process should run quite quickly.

Regards
FSt1

 

[Guest]

Steve -

I'd suggest a multi-step approach.

Step 1 - Create an Excel validation list using your current customers. You
can do it manually by copying data from Access or, if you like using VBA, you
can code it. Search the MS Knowledge base for ADODB articles. There's
plenty of examples showing you how to extract data from Access.

The advantage to coding it is you can allow users to update the customer
list at any time, simply by running your code, attached to a command button,
for instance.

Step 2 - Either code a query that uses the customer selected in the
validation list or use the info at Nick Hodge's site to pull the remaining
customer data via MS Query:

http://www.nickhodge.co.uk/gui/datamenu/dataexamples/externaldataexamples.htm

Personally, I'd recommend coding the whole thing, since that gives you much
better control over what happens and allows you to put in error handlers to
deal with the unexpected.

Good luck

The code to do all of this is not difficult, but neither is it trivial

 

[Guest]

hi again,
had a thought. you would only need to run the first MSQ if you had new
customers. if no new customers add to the access database then all you would
need is to run the second MSQ to get the individual customer data.

regards
FSt1

 

[Guest]

Thanks guys for the responses.

I guess I,ve got a bit more research to do.

Steve