Running Access Query from Excel VBA

[dunc723]

I have several users that use the same Excel template to generate quotations. Every time a user is creating a new quotation, he must get a sequential number from an Access database. That also involves entering a few fields of information into the database via a form. Basically the sequence of events (everything automated unless noted as User activity):

1. User opens the XL template and clicks a button to request the new number


2. Open AC database and open the data entry form
3. User completes all fields and clicks OK
4. Save the data in the AC table and close the database
5. Populate the quotation number cell on the XL template
6. User completes the rest of the quotation.

I can envision a couple of ways to do this...
A. All the AC activity is controlled by code in the XL VBA module. Is this possible and not really complex?
B. XL VBA code only opens the AC database (I have already learned how to do this), and on opening, an AutoRun macro + AC VBA does the rest. Can the number then be passed back to XL?

I like (A) because the AC database may be opened for other reasons, in which case I don't want any code executed. I like (B) because it seems a lot simpler and I'm more familiar with AC VBA than XL VBA. Can a variable be passed from XL to AC on opening that tells AC whether to run the code or not?

Thanks,

PD

[Rod]

PD,

You're not alone in your dilemma. I know of no simple way to poke or pull data between Office applications. (Someone will now shed light on this! :-))

Your strategy A. is feasible. What has to happen is that you reference the Access library in your Excel references. This makes the whole Access COM available within your Excel. Now you need to instantiate an object (probably more than one object from different Access classes) within Excel and equate it (them) to your Access application. Now your data interchange can take place freely through properties and methods (subroutines and functions).

A word of warning: while there is only ever one copy of the Excel application - with multiple workbooks if necessary - there is one copy of the Access application for each database. It is not sufficient to see whether Access is already running, you must also investigate which database it is using. If it's not the right one then you must open a new instance of Access.

Your strategy B. is somewhat similar but now the Access module must see if Excel is running and if the required workbook is open. If so it can poke a value into a spreadsheet or whatever.

Sorry this has been the 'what' rather than the 'how.' If anyone else has done this please feel free to jump in. I've done the Access controlling Excel scenario more than once; I've never done the reverse.

PS Both scenarios rather fly in the face of encapsulation. If this bothers you it is possible to invoke an Access embedded routine from another application thus paying lip service to encapsulation.

[ItsMe]

I vote for option C

C: Do not use Excel as an User Interface.

If need be you could Import a worksheet or two into a temp table(s).

[hapm]

There are some options to do this "application interoperation". They range from "very easy to understand, but some work to do to get it run" to "very comfortable and fast setup, but hard to understand when it fails".
The easy one: use a temp file, and let access write the number into it when done. Excel tries to read the file constantly (known as polling) and when it succeeds, it deletes the file.
The comfortable one: going over com interop, that allows excel to create an instance of the Access.Application object and access all the possibilities this object gives you in access vba.
There are much more options then these two, but these are the best ones I can think of at the moment. I fo myself prefer the com interop solution, because I know most of the things that can go wrong, but it depends on the environments your database runs in. Have environments you can't control or don't even know of, can make trouble (they shouldn't, but there are some funny misconfigurations of office com interop).