module

microsoft.public.vb.general.discussion

2 Feb 2006 5:41 PM

Pinto1uk

Hi, i am using the code below over and over again to connect to my database.
I have tried to place in within a module and call it from whereever i need
it, but i get the error stating: variable or procedure expected not module.
can anyone help.

Dim adoConnection As ADODB.connection
Dim adoRecordset As ADODB.Recordset

    Set adoConnection = New ADODB.connection
    Set adoRecordset = New ADODB.Recordset

'build our connection string to use when we open the connection

    connectString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
                & "Data Source=C:\Documents and Settings\My
Documents\System\yDatabase.mdb"

'open the connection with the above string
    adoConnection.Open connectString

2 Feb 2006 6:24 PM

Gman

I can probably help you help yourself. You have to report the correct
error message to the group. I don't think your error message is
"variable or procedure expected not module"

I think it's "Expected Variable or Procedure not Module"

Copy that into google, including quotes. The very first result has
someone explaining the likely cause of the error.

It took me about 10" to google it. That's got to be a fraction of the
time it takes to write a post (or rather copy and repost your earlier
post) and wait for a response.

So as part of the google training exercise I'm not going to tell you the
answer - it's on google: clue.... it involves module names. If that's
not it... then post back. :-)

Pinto1uk wrote:
Show quoteHide quote

> Hi, i am using the code below over and over again to connect to my database.
> I have tried to place in within a module and call it from whereever i need
> it, but i get the error stating: variable or procedure expected not module.
> can anyone help.
>
> Dim adoConnection As ADODB.connection
> Dim adoRecordset As ADODB.Recordset
>
>     Set adoConnection = New ADODB.connection
>     Set adoRecordset = New ADODB.Recordset
>
> 'build our connection string to use when we open the connection
>
>     connectString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
>                 & "Data Source=C:\Documents and Settings\My
> Documents\System\yDatabase.mdb"
>
> 'open the connection with the above string
>     adoConnection.Open connectString

2 Feb 2006 7:20 PM

Pinto1uk

Hi, thank you for pointing me on the right direction. I have managed to solve
the problem and the error does not occur anymore. But now i come across
another error: Object Required.

This line below is the cause of the error:

adoRecordset.Open ("Select * From Product"), adoConnection, ,
adLockPessimistic

this is because i am trying to use the recordset object that has been
declared and set in the module. cannot find any solutions using google.

regards

regards

Show quoteHide quote

"Gman" wrote:

> I can probably help you help yourself. You have to report the correct
> error message to the group. I don't think your error message is
> "variable or procedure expected not module"
>
> I think it's "Expected Variable or Procedure not Module"
>
> Copy that into google, including quotes. The very first result has
> someone explaining the likely cause of the error.
>
> It took me about 10" to google it. That's got to be a fraction of the
> time it takes to write a post (or rather copy and repost your earlier
> post) and wait for a response.
>
> So as part of the google training exercise I'm not going to tell you the
> answer - it's on google: clue.... it involves module names. If that's
> not it... then post back. :-)
>
>
> Pinto1uk wrote:
> > Hi, i am using the code below over and over again to connect to my database.
> > I have tried to place in within a module and call it from whereever i need
> > it, but i get the error stating: variable or procedure expected not module.
> > can anyone help.
> >
> > Dim adoConnection As ADODB.connection
> > Dim adoRecordset As ADODB.Recordset
> >
> >     Set adoConnection = New ADODB.connection
> >     Set adoRecordset = New ADODB.Recordset
> >
> > 'build our connection string to use when we open the connection
> >
> >     connectString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
> >                 & "Data Source=C:\Documents and Settings\My
> > Documents\System\yDatabase.mdb"
> >
> > 'open the connection with the above string
> >     adoConnection.Open connectString
>

2 Feb 2006 7:48 PM

Gman

I'm not sure what it could be. Maybe a problem with your declarations.
Something that confused me about your other posts is:

Dim adoConnection As ADODB.connection
Set adoConnection = New ADODB.connection

I don't understand why connection is not written Connection - that
should be automatic. So it suggests something funny is going on to me.

I would break down the .Open line in an attempt to work out where the
trouble is. See below, and find out what line it trips up on.

'--------------
connectString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
               & "Data Source=C:\Documents and Settings\My
Documents\System\yDatabase.mdb"

'open the connection with the above string
adoConnection.Open connectString

If adoconnection.State <> adStateOpen Then
    msgbox "problem connecting"
    exit function 'or sub
end if

adoRecordset.Open ("Select * From Product"), adoConnection, ,
With adoRecordset
     set .ActiveConnection = adoconnection
    .CursorLocation = adUseClient
    .CursorType = adOpenForwardOnly
    .LockType = adLockPessimistic
    .Open "Select * From Product"
End With

'-----------------------

Also, if you post your full code (including declarations) it would be
easier to nail down the problem.

Pinto1uk wrote:
Show quoteHide quote

> Hi, thank you for pointing me on the right direction. I have managed to solve
> the problem and the error does not occur anymore. But now i come across
> another error: Object Required.
>
> This line below is the cause of the error:
>
> adoRecordset.Open ("Select * From Product"), adoConnection, ,
> adLockPessimistic
>
> this is because i am trying to use the recordset object that has been
> declared and set in the module. cannot find any solutions using google.
>
> regards
>
> regards
>
>
>
>
>
>
> "Gman" wrote:
>
>
>>I can probably help you help yourself. You have to report the correct
>>error message to the group. I don't think your error message is
>>"variable or procedure expected not module"
>>
>>I think it's "Expected Variable or Procedure not Module"
>>
>>Copy that into google, including quotes. The very first result has
>>someone explaining the likely cause of the error.
>>
>>It took me about 10" to google it. That's got to be a fraction of the
>>time it takes to write a post (or rather copy and repost your earlier
>>post) and wait for a response.
>>
>>So as part of the google training exercise I'm not going to tell you the
>>answer - it's on google: clue.... it involves module names. If that's
>>not it... then post back. :-)
>>
>>
>>Pinto1uk wrote:
>>
>>>Hi, i am using the code below over and over again to connect to my database.
>>>I have tried to place in within a module and call it from whereever i need
>>>it, but i get the error stating: variable or procedure expected not module.
>>>can anyone help.
>>>
>>>Dim adoConnection As ADODB.connection
>>>Dim adoRecordset As ADODB.Recordset
>>>
>>>    Set adoConnection = New ADODB.connection
>>>    Set adoRecordset = New ADODB.Recordset
>>>
>>>'build our connection string to use when we open the connection
>>>
>>>    connectString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
>>>                & "Data Source=C:\Documents and Settings\My
>>>Documents\System\yDatabase.mdb"
>>>
>>>'open the connection with the above string
>>>    adoConnection.Open connectString
>>

2 Feb 2006 7:54 PM

Gman

Ooops - I left a line of your code in. SHould read:

'--------------
connectString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
               & "Data Source=" _
   C:\Documents and Settings\My Documents\System\yDatabase.mdb"

'open the connection with the above string
adoConnection.Open connectString

If adoconnection.State <> adStateOpen Then
    msgbox "problem connecting"
    exit function 'or sub
end if

With adoRecordset
     set .ActiveConnection = adoconnection
    .CursorLocation = adUseClient
    .CursorType = adOpenForwardOnly
    .LockType = adLockPessimistic
    .Open "Select * From Product"
End With

'-----------------------

2 Feb 2006 9:21 PM

Pinto1uk

Hi, i am still getting the "object required" error after trying the code
given. I'll try and explain exactly what i am doing.

I am creating a system where i am connecting to the database so many times.
for products, suppliers, customers etc.

so in order to simplify my code, i have entered the connection code into a
module. This is the code in the module:

Sub connectToDatabase()

Dim adoConnection As ADODB.Connection
Dim adoRecordset As ADODB.Recordset

    Set adoConnection = New ADODB.Connection
    Set adoRecordset = New ADODB.Recordset

'build our connection string to use when we open the connection

    connectString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
                & "Data Source=C:\Documents and Settings\Iqbal\My
Documents\IQBAL\DMU\Final Year\Final Year
Project\System\StationaryDatabase.mdb"

'open the connection with the above string
    adoConnection.Open connectString

End Sub

Now, from various parts within my forms, i am trying to call the sub using:

Call connectToDatabase

After this call, i am opening the recordset depending which form i ma in. in
this example i want to connect to the product table. on other occasions it
may be customer etc.

    adoRecordset.Open ("Select * From Product"), adoConnection, ,
adLockPessimistic

the above line is where i am getting the error: object required. hope this
makes sense. sorry for the troubles.

Show quoteHide quote

"Gman" wrote:

> Ooops - I left a line of your code in. SHould read:
>
> '--------------
> connectString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
>                & "Data Source=" _
>    C:\Documents and Settings\My Documents\System\yDatabase.mdb"
>
> 'open the connection with the above string
> adoConnection.Open connectString
>
> If adoconnection.State <> adStateOpen Then
>     msgbox "problem connecting"
>     exit function 'or sub
> end if
>
> With adoRecordset
>      set .ActiveConnection = adoconnection
>     .CursorLocation = adUseClient
>     .CursorType = adOpenForwardOnly
>     .LockType = adLockPessimistic
>     .Open "Select * From Product"
> End With
>
> '-----------------------
>

2 Feb 2006 10:28 PM

Gman

A few things:

(1) Your "Sub connectToDatabase():
In this module you're instantiating two objects, recset and connection.
You then open a connection to the database.
And then that's it. When you exit the sub you've lost them. The
connection is open and you've taken up memory with the two objects.

You either need to declare and use the connection and recordsets in the
same procedure (Sub or function)...

*or*..

.... you need to make them available in the scope of a module or
publicly. You can't do both.

So, when you try this:
adoRecordset.Open ("Select * From Product"), adoConnection, ,
adLockPessimistic
the application has no idea what adoConnection is - it doesn't exist
outside the scope of the connectToDatabase procedure.

[As an aside, this is a perfect example to demonstrate why posting as
much code as you can helps people help you. I would never have realised
what you were doing if I hadn't have seen the full connectToDatabase sub.]

(2) How to do this.
I've already posted (in your previous thread) how to do all this,
keeping the connection permanently open. This isn't necessarily the best
way to do it - it depends on your circumstances. So I'll demonstrate how
to do it with a connection you open then close as soon as you can. In
this case, with a disconnected recordset. (I don't work like this - so
it's untested.)

'-----Post this in a new module------
Option Explicit

'I've made this public so it can be accessed from all your modules
'including forms.
Public cn As New ADODB.Connection

Private Const myConnectString As String = _
       "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" _
& "C:\Documents and Settings\Iqbal\My Documents\IQBAL\DMU\Final
Year\Final Year Project\System\StationaryDatabase.mdb"

'-----------------------------------------------------------
'Sets up a connection to database and returns true if
'successful.
Private Function ADO_ConnectToDB() As Boolean
     'check if we're already connected
     If Not cn Is Nothing Then
         If cn.State <> adStateClosed Then
             'we're already connected
             ADO_ConnectToDB = True
         Exit Function
     End If

     'try and open a connection
     On Error GoTo ErrorTime
     cn.Open myConnectString
     ADO_ConnectToDB = True

ErrorTime:

End Function

'Returns a disconnected recordset - this is fine for, say, loading a
'a listview.
'Returns nothing if an error occurs.
Public Function ADO_GetDisconnectedRecordSet(mySQL As String) As
ADODB.Recordset
Dim rs As New ADODB.Recordset

     If Not ADO_ConnectToDB Then Exit Function
     On Error GoTo ErrorTime

     'again, please note how I've separated the commands
     'it's easier to understand and errortrap this way
     ' - probably not quicker
     With rs
          Set .ActiveConnection = cn
         .CursorLocation = adUseClient
         .CursorType = adOpenForwardOnly ' or adOpenStatic
         .LockType = adLockReadOnly
         .Open mySQL
          Set .ActiveConnection = Nothing
     End With

     Set ADO_GetDisconnectedRecordSet = rs

ErrorTime:
On Error Resume Next
     'close our connection to the database
     cn.Close
     'It's vital that you clear any reference to these objects
     'otherwise you'll start to eat away at memory
     Set cn = Nothing
     Set rs = Nothing

End Function

'-----End of this module

'Code for any module - in a form, say.

Sub DoSOmethingSpecific()
Dim rs As ADODB.Recordset

   Set rs = ADO_GetDisconnectedRecordSet( _
        "SELECT * FROM myTable")

   If not rs is nothing then exit sub

   'Woohoo - now you have a recordset
   'do what you need to do

'Don't forget:
set rs = nothing

End Sub

Finally, I think you need to sit down and read up on objects, variables
and their scope (on the net or in help). It might take an hour but it
will save you a lot of time and frustration in the long run. Honestly
honestly honestly - trust me!!

4 Feb 2006 7:55 PM

Pinto1uk

thank you for your help, i really apprecaite it.
regards

Show quoteHide quote

"Gman" wrote:

> A few things:
>
> (1) Your "Sub connectToDatabase():
> In this module you're instantiating two objects, recset and connection.
> You then open a connection to the database.
> And then that's it. When you exit the sub you've lost them. The
> connection is open and you've taken up memory with the two objects.
>
> You either need to declare and use the connection and recordsets in the
> same procedure (Sub or function)...
>
> *or*..
>
> .... you need to make them available in the scope of a module or
> publicly. You can't do both.
>
> So, when you try this:
> adoRecordset.Open ("Select * From Product"), adoConnection, ,
> adLockPessimistic
> the application has no idea what adoConnection is - it doesn't exist
> outside the scope of the connectToDatabase procedure.
>
> [As an aside, this is a perfect example to demonstrate why posting as
> much code as you can helps people help you. I would never have realised
> what you were doing if I hadn't have seen the full connectToDatabase sub.]
>
> (2) How to do this.
> I've already posted (in your previous thread) how to do all this,
> keeping the connection permanently open. This isn't necessarily the best
> way to do it - it depends on your circumstances. So I'll demonstrate how
> to do it with a connection you open then close as soon as you can. In
> this case, with a disconnected recordset. (I don't work like this - so
> it's untested.)
>
> '-----Post this in a new module------
> Option Explicit
>
> 'I've made this public so it can be accessed from all your modules
> 'including forms.
> Public cn As New ADODB.Connection
>
> Private Const myConnectString As String = _
>        "Provider=Microsoft.Jet.OLEDB.4.0;" _
>   & "Data Source=" _
>   & "C:\Documents and Settings\Iqbal\My Documents\IQBAL\DMU\Final
> Year\Final Year Project\System\StationaryDatabase.mdb"
>
> '-----------------------------------------------------------
> 'Sets up a connection to database and returns true if
> 'successful.
> Private Function ADO_ConnectToDB() As Boolean
>      'check if we're already connected
>      If Not cn Is Nothing Then
>          If cn.State <> adStateClosed Then
>              'we're already connected
>              ADO_ConnectToDB = True
>          Exit Function
>      End If
>
>      'try and open a connection
>      On Error GoTo ErrorTime
>      cn.Open myConnectString
>      ADO_ConnectToDB = True
>
> ErrorTime:
>
> End Function
>
> 'Returns a disconnected recordset - this is fine for, say, loading a
> 'a listview.
> 'Returns nothing if an error occurs.
> Public Function ADO_GetDisconnectedRecordSet(mySQL As String) As
> ADODB.Recordset
> Dim rs As New ADODB.Recordset
>
>      If Not ADO_ConnectToDB Then Exit Function
>      On Error GoTo ErrorTime
>
>      'again, please note how I've separated the commands
>      'it's easier to understand and errortrap this way
>      ' - probably not quicker
>      With rs
>           Set .ActiveConnection = cn
>          .CursorLocation = adUseClient
>          .CursorType = adOpenForwardOnly ' or adOpenStatic
>          .LockType = adLockReadOnly
>          .Open mySQL
>           Set .ActiveConnection = Nothing
>      End With
>
>      Set ADO_GetDisconnectedRecordSet = rs
>
> ErrorTime:
> On Error Resume Next
>      'close our connection to the database
>      cn.Close
>      'It's vital that you clear any reference to these objects
>      'otherwise you'll start to eat away at memory
>      Set cn = Nothing
>      Set rs = Nothing
>
> End Function
>
> '-----End of this module
>
> 'Code for any module - in a form, say.
>
> Sub DoSOmethingSpecific()
> Dim rs As ADODB.Recordset
>
>    Set rs = ADO_GetDisconnectedRecordSet( _
>         "SELECT * FROM myTable")
>
>    If not rs is nothing then exit sub
>
>    'Woohoo - now you have a recordset
>    'do what you need to do
>
>
> 'Don't forget:
> set rs = nothing
>
> End Sub
>
>
> Finally, I think you need to sit down and read up on objects, variables
> and their scope (on the net or in help). It might take an hour but it
> will save you a lot of time and frustration in the long run. Honestly
> honestly honestly - trust me!!
>
>