Establishing Remote connections with VBA

[kellmich]

I have been attempting to establish a remote connection to another database
on my hard drive using a DIM statement within a form module (This is
supported by two prominent reference manuals - Access 2007 Inside Out, and
ACCESS VBA programming for absolute beginners. The Dim statement is as
follows:
Dim RemoteConnection As ADODB.Connection

When it is located in an event subroutine (i.e. the load event) I get the
following message:

Compile Error: User-Defined Type not defined
The debugger then highlights the above DIM statement.

A much longer message occurs if I move the DIM statement to the general
declarations area of the form module. However, It amounts to the same thing
- "User Defined Type not defined".

Has anyone else out there met and solved this problem? Help!

 

[Douglas J. Steele]

Access 2007 doesn't have a reference set to ADO by default. Presumably
there's something in the book explaining to you how to go into the
References dialog and add the necessary reference.

 

[kellmich]

No it doesn't. I successfully used ADO to establish a local connection to
the current application database. My problem has been with remote connection
to a different database (different .accb).

 

[kellmich]

Just to provide some further clarification:

The DIM I used to successfully make a local connection was:

DIM localConnection as ADODB.Connection

The only thing I have seen regarding providing reference is a code example I
found out in the help facility and adapted as follows:

Dim RemoteConnection As Object ' Declare variable in form gen. declaration
Set RemoteConnection = CreateObject("ADODB.Connection") 'in load event

This was of course guessing because that is what I'm reduced to at this
point. It didn't work. I got the same error (user defined type not defined).

 

[George Nicholson]

Huh?
Are you saying that
Dim RemoteConnection As ADODB.Connection
raises a "User defined type not defined" error but that
DIM localConnection as ADODB.Connection
does not raise an error? In the same database file? I don't think that's
possible.

In the VB Editor: Tools>References. Is there a reference to ADO checked (aka
Microsoft ActiveX Data Objects x.x Library)? Does it have "MISSING" next to
it?

 

[kellmich]

Yes George, that's exactly what I'm saying. I did the local connection and
the remote connection to the same .accdb file. The former from within that
databases application; the latter from another application.

I have a number of entries for ADO in the tools menu, each referencing a
different library version; however none of their checkboxes are clicked and
there is no "missing" notation next to any of them.

Thanks for the response, I look forward to hearing from you again.

 

[kellmich]

You asked me the right questions George. I investigated further and noticed
that the application that I used the local connection for had ADO 2.1 library
checked while my other application in which I was trying to remotely connect
to the former (application with the local connection) did not have that
library referenced. I checked it off and everything worked great. Thanks so
much.

Mike Kelly, Logikel Solutions