DAO to ADO conversion.

[Guest]

I want VB code in Excel to call a function to manipulate
data in Access. Access stores Budget, all monthly Actuals
and Forecast data (which is cumbersome in spreadsheet).

I want Excel to Forecast... i.e. it will pass a forecasted
price-set to Access and the VB code will:-
calculate the Forecasts
store these Forecasts in Access tables
populate back the result in Excel for analyses

I have made a working code in Access using DAO. Though
Excel gives me the ability to integrate with Access
using "Get External Data" and run SQL queries, there is
nothing in standard Excel that allows me to run VBA
functions. Hence I thought I could have an ADO code that
connects to the Access database with capability to run the
code in both Excel and Access.

I have hit the following snags (and this is where I could
really use some help).

In DAO FindFirst, Edit and Nomatch() methods are
available. What are the similar methods available in ADO?

When I run the ADO code in Access, I get an error stating
that the database is in use (guess it is because of the
ldb file lock). Is there a way to circumvent this?

Appreciate your response

Rgds,

 

[Douglas J. Steele]

Are you saying that your queries in Access are using VBA functions in them?
There's no way to do that from outside of Access. That's because when you're
running the queries inside Access, you have the "luxury" of Access being
involved with the queries. When you try running them from outside of Access,
your only connection to the data is through the Jet Engine, which doesn't
know anything about VBA functions.

 

[Joe Fallon]

I would not use ADO.

Not sure if this will help:

How to Use Excel Functions in Access:

It would be very nice if I could call an excel function like yield directly
from a query.
Can this be done? If so, how?

Well, there are Excel functions and then there are Excel Add-ins (which
unfortunately includes Yield). The core worksheet functions can be included
as follows:

1. Open your database, type Ctrl+G to make sure you're in the VBA editor.
2. Choose Tools\References, and scroll down and check Microsoft Excel 10.0
Object Library (or whatever version of the Excel Object Library you have)
3. Use the functions as Excel.WorkSheetFunction.FunctionName, for example if
you type:
?Excel.WorksheetFunction.Ceiling(5,3)
in the immediate pane and hit enter, it should return 6.
4. To use such functions in queries, make a wrapper function by passing
appropriate data to the Excel function and returning the results, for
example:
Function MyCeiling(ByVal iOne As Double, ByVal iTwo As Double)
MyCeiling = Excel.WorksheetFunction.Ceiling(iOne, iTwo)
End Function
5. Call that wrapper function from queries, as in:
Result: MyCeiling([field1],[field2])

The Yield function, however, is a special case, as it's an Excel Add-in. If
you're *really* intent on using it, you can access it from automation, see:
http://support.microsoft.com/default.aspx?scid=kb;EN-GB;q198571

Probably best to develop your own library of functions: there are VB/VBA
versions out there for almost anything that you might need.

Originally posted by Max Dunn