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

#10 (**permalink**) 28-Jul-2006, 08:40 AM

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

Douglas J. Steele wrote:
> 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.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
>
>
> "axs221" wrote in message
> news:1153752396.944194.20280@i3g2000cwc.googlegrou ps.com...
> > 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
> >
> >
> > Douglas J. Steele wrote:
> >> 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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/13730-vba-access-moving-parts-program-dlls.html
> >>
> >> --
> >> Doug Steele, Microsoft Access MVP
> >> http://I.Am/DougSteele
> >> (no private e-mails, please)
> >>
> >>
> >> "axs221" wrote in message
> >> news:1153581537.773940.152010@h48g2000cwc.googlegr oups.com...
> >> >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 wrote:
> >> >> 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
> >> >>
> >> >>
> >> >> --
> >> >> Doug Steele, Microsoft Access MVP
> >> >> http://I.Am/DougSteele
> >> >> (no private e-mails, please)
> >> >>
> >> >>
> >> >> "axs221" wrote in message
> >> >> news:1153533480.310317.12690@75g2000cwc.googlegrou ps.com...
> >> >> > 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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13730
> >> >> >
> >> >> > 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
> >> >> >
> >> >> >
> >> >> >
> >> >> > Norman Yuan wrote:
> >> >> >> 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" wrote in message
> >> >> >> news:1153516589.043190.20630@i3g2000cwc.googlegrou ps.com...
> >> >> >> >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.
> >> >> >> >
> >> >> >
> >> >
> >

#11 (**permalink**) 28-Jul-2006, 08:41 AM

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)

"axs221" wrote in message
news:1153760793.225695.144790@m79g2000cwm.googlegr oups.com...
> That answered my question. I appreciate your help. Is there any way
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13730
> 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
>
>
> Douglas J. Steele wrote:
> > 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.
> >
> > --
> > Doug Steele, Microsoft Access MVP
> > http://I.Am/DougSteele
> > (no private e-mails, please)
> >
> >
> > "axs221" wrote in message
> > news:1153752396.944194.20280@i3g2000cwc.googlegrou ps.com...
> > > 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
> > >
> > >
> > > Douglas J. Steele wrote:
> > >> 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.
> > >>
> > >> --
> > >> Doug Steele, Microsoft Access MVP
> > >> http://I.Am/DougSteele
> > >> (no private e-mails, please)
> > >>
> > >>
> > >> "axs221" wrote in message
> > >> news:1153581537.773940.152010@h48g2000cwc.googlegr oups.com...
> > >> >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 wrote:
> > >> >> 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
> > >> >>
> > >> >>
> > >> >> --
> > >> >> Doug Steele, Microsoft Access MVP
> > >> >> http://I.Am/DougSteele
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13730
> > >> >> (no private e-mails, please)
> > >> >>
> > >> >>
> > >> >> "axs221" wrote in message
> > >> >> news:1153533480.310317.12690@75g2000cwc.googlegrou ps.com...
> > >> >> > 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
> > >> >> >
> > >> >> >
> > >> >> >
> > >> >> > Norman Yuan wrote:
> > >> >> >> 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" wrote in message
> > >> >> >> news:1153516589.043190.20630@i3g2000cwc.googlegrou ps.com...
> > >> >> >> >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.
> > >> >> >> >
> > >> >> >
> > >> >
> > >
>