VBA Access - Moving parts of a program to DLLs? Error 91.. "Object variable or With block variable n

[axs221]

I am trying to move some of our large VBA Access front-end file into
ActiveX DLL files. I created two DLL files so far, one was a module
that contains code to integrate into the QuickBooks accounting
software. Another has general utilities.

I tried referencing the utilities dll, and it shows up in the object
explorer. I instantiated an instance of the class and now it shows up
all okay in the Intellisense. Whenever I try to run a simple function,
though, even a subroutine that just does:

MsgBox "Test"

It gives me the error 91: Object variable or With block variable not
set

Am I missing a step here? We really need these DLLs, or some
alternative, so that we can better use Subversion for our main project.

 

[Norman Yuan]

Yes, you did miss something, for sure, in your code and in your post.
Without seeing your code to show how the routine in the dll is called, there
isn't much to say, except for "the code must be wrong).

 

[axs221]

Basically I created a DLL file, Utilities.dll, and inside it I made a
few classes. One class, lets say AGUtilities, I just wrote a simple
function to test the DLL calls out, like this:

Public Sub MsgBoxTest()
MsgBox "Testing"
End Sub

Then I made a reference to it from our main VBA program.

Dim UtilitiesTest As AGUtilities
AGUtilities.MsgBoxTest

There's a basInitialize that runs at startup. I made a reference to the
DLL in the references menu option, clicking browse and selecting it in
the same directory. I made an instance of the class in that module, and
tried calling the function to run a simple message box, and it gives
that error code. I got the same thing when trying to run another
function before that, one that gets the name of the program running.
The object browser and intillisense looked like any other class but it
just wouldn't run it.

Is there something more that you'd generally need to do in order to run
such a dll call? Where might I be going wrong?

Thanks,

Shawn

 

[Douglas J. Steele]

All you've done is indicate what object is supposed to point to the DLL
(through the Dim statement). However, you haven't actually instantiated that
object to make it point to the DLL (using a Set statement):

Dim UtilitiesTest As AGUtilities

Set UtilitiesTest = New AGUtilities
AGUtilities.MsgBoxTest

 

[axs221]

I just realized that on someone else's response somewhere else.. I feel
a little silly now. Are we doing things right otherwise, though? Is
putting code into DLLs the best way to split up a large vba access
program? The only annoyance with it I have is that we have to take
functions from modules that were coupled with other modules out and
make another DLL for the new DLLs to reference for the functions they
need. My boss showed me how we link tables the other day in Access. I
wish there was a way to link to external modules or classes, if there
isn't.

Shawn

 

[Douglas J. Steele]

You can put your code into a library MDB (or MDE), and add a reference to
that library under Tools | References while you're in the VB Editor.

 

[axs221]

Thanks, that might be better. Is there a way to get the separate MDB to
still be able to use function calls from the main front-end file,
though? The only way I know how to do that is to make another database
just for function calls that are used by more than one of the
databases, but that might be a lot of work.

Shawn

 

[axs221]

Well, not just functions, but we also need queries and a few global
variables and a few other things we have in our main program.

We have a few global variables that set the name and location of some
other files in initialization, along with a few other ones, but the new
MDB file I created can't access them when referenced. It won't let me
reference back to the main database, of course, it gives a cyclical
reference error.

Is there a way to have a seperate database referenced but have it act
like one database? It seems like there should be an easy way to do
that, to make things more modular...

Shawn

 

[Douglas J. Steele]

Not sure I understand your question.

If, in Database A you set a reference to Database B, within Database A you
can call functions from Database B. Database B, however, won't know anything
about functions in Database A.

 

[axs221]

That answered my question. I appreciate your help. Is there any way
that Database B could ever access anything from Database A, or do you
know of any other way around it other than having to make a "Database
C" with the functions, queries, global variables, and everything else
needed by both databases, as well as any other database we would like
to make?

Our modules are coupled together quite a lot unfortunately, so storing
everything in a third database might be messy and take more effort than
it is worth.

Shawn

 

[Guest]

Yes, you can call back down from a library into the
calling code, but if you try to make an MDE, or try compilation
at all, you have to use Run or Eval or a query or some
way of defeating the compilation check, because you
won't be able to resolve a reference from B back to
A unless A is loaded.

Note that it is considered bad design to have B call into A
if A calls into B, so this is not a major limitation

In general, if you want to do callbacks, you will be wanting
to dynamically pass the callback information anyway, so
you would be using Run or Eval even if it wasn't enforced
by the compilation process.

For example, our report configuration library opens reports
from our process libraries: The report names and calling
functions are read from a table, and never appear in the
report library code. You can also pass db, rs and frm
objects.

Our modules are coupled together quite a lot unfortunately

There was a lot of coupling and repeated code to start with,
but it's gradually been eliminated.

A significant limitation is that you can't directly open forms
and reports. You could in Access 95, but that was 'fixed'
in A97. There was still a work around, but that was 'fixed'
in 2003. Short of doing a direct modification to your MDB
file, you need to use a calling function for any form or report.

(david)