I don't see anything wrong with your code in the second module, apart from
the point I mentioned earlier, and the fact that you don't do a MoveFirst
before referencing the recordset, but I don't think this actually matters;
it should be there anyway unless no records have been returned, and you
aren't testing that.

I have revised your code to show how I may lay it out, and as a basis for
further discussion, but I haven't tested it and there's no error handling.

I hope this helps

Vanessa
-------------------------------------
In global module

Public Function fGetConnection(sqlcon) As Boolean

    ' Connect to SQL server through SQL Server OLE DB Provider.
    'Set the ADO connection properties.
    sqlcon.ConnectionTimeout = 25  ' Time out for the connection
    sqlcon.Provider = "sqloledb"   ' OLEDB Provider
    ' set the ip address of your sql server
    sqlcon.Properties("Network Address").Value = "xxx.xxx.xxx.xxx"
    sqlcon.CommandTimeout = 10
    ' set the network library to use win32 winsock tcp/ip
    sqlcon.Properties("Network Library").Value = "dbmssocn"
    sqlcon.Properties("Data Source").Value = "xxxxx"
    sqlcon.Properties("Initial Catalog").Value = "sqlmigtest"
    sqlcon.CursorLocation = adUseServer ' For ADO cursor location
    sqlcon.Properties("User ID").Value = "sysdba"
    sqlcon.Properties("Password").Value = "Masterkey"
    sqlcon.Open

    fGetConnection = True

End Function

In form(?) module

Dim sqlcon As New ADODB.Connection

Private Sub Command1_Click()
    Dim rs As ADODB.Recordset
    Dim strQL As String

    'Set a Simple Sql query to return the servers time
    strSQL = "SELECT * from tperson "

     If sqlcon is Nothing Then
        If fGetConnection = False Then
            'raise an error
        End If
    End If

    ' Lets open a connection with our new SQLCON connection , and our SQL
statement
    Set rs = New ADODB.Recordset
    rs.Open strSQL, sqlcon

    'Test for errors in the Open statement here before trying the move to
the first recordset

    'Move to first row.
    rs.MoveFirst
    Dim r As String
    r = rs(6)
    MsgBox (r)

End Sub

Private Sub Command2_Click()
    Dim rs As ADODB.Recordset
    Dim r As String
    Dim strSQL As String

    strSQL = "select * from tresourcetype"

     If sqlcon is Nothing Then
        If fGetConnection = False Then
            'raise an error
        End If
    End If

    Set rs = New ADODB.Recordset
    rs.Open strSQL, sqlcon

    'Test for errors in the Open statement here before trying the move to
the first recordset

    'Move to first row.
    rs.MoveFirst
    r = rs(0)
    MsgBox (r)

End Sub

----- Original Message -----
From: "Niall Hannon (ext. 772)" <Niall.Hannon@f...>
To: "professional vb" <pro_vb@p...>
Sent: Friday, April 05, 2002 01:03
Subject: [pro_vb] Re: Simple problem thats driving me mad!


> Hi,
>
> Thanks for that. I was thinking it was something like that. The real basis
> of the problem is I am converting a VB app written with DAO to ADO. So the
> module for opening the database in this app uses DAO. I have managed to
> convert this to use ADO and it opens a connection successfully. But I get
> this error in other subroutines even when I have converted them to ADO
also.
>
> So, I thought with ADO you have to do something more than is required in
> DAO? Lets assume my code for opening the connection is in a module and it
> works. Then is my code in Command2 subroutine correct? I think it should
> work but.....arghhhh!!
>
> Any ideas?
>
> Thanks
> Niall
>
> -----Original Message-----
> From: Vanessa Moss [mailto:vmoss@c...]
> Sent: 05 April 2002 12:53
> To: professional vb
> Subject: [pro_vb] Re: Simple problem thats driving me mad!
>
>
> It looks as though you are creating sqlcon in subroutine Command1_Click;
by
> the time your user clicks on Command2, the sqlcon variable will have gone
> out of scope. If you want to use sqlcon in the Command2_Click subroutine,
> you will need to promote it to a module level variable.
>
> Can the users click Command2 before Command1? If so, you could move the
> setup of sqlcon to a separate subroutine and call this subroutine from
both
> Command1_Click & Command2_Click if sqlcon is not set up.
>
> Hope this helps
>
> Vanessa
>
> ----- Original Message -----
> From: "Niall Hannon (ext. 772)" <Niall.Hannon@f...>
> To: "professional vb" <pro_vb@p...>
> Sent: Friday, April 05, 2002 11:11
> Subject: [pro_vb] Simple problem thats driving me mad!
>
>
> > Hi,
> >
> > I am having what I think is a pretty simple error but I cant figure it
out
> > and its driving me nuts. I have a simple VB program (using ADO) and 2
> > command buttons. The first procedure works fine...it opens the
connection
> > and displays a piece of data. But the second one will not work and gives
> > this error..
> >
> > "Runtime error 3001. Arguments are of the wrong type, are out of
> acceptable
> > range, or are in conflict with one another"
> >
> > I shouldnt have to open the connection to the database in every module
but
> I
> > think it wants me to.
> >
> > If anyone can help then I would really appreciate it.
> > Thanks
> > Niall
> >
> > Private Sub Command1_Click()
> > ' Connect to SQL server through SQL Server OLE DB Provider.
> > Dim sqlcon As New ADODB.Connection
> >
> >     ' Set the ADO connection properties.
> >     sqlcon.ConnectionTimeout = 25  ' Time out for the connection
> >     sqlcon.Provider = "sqloledb"   ' OLEDB Provider
> >     sqlcon.Properties("Network Address").Value = "xxx.xxx.xxx.xxx"  '
set
> > the ip address of your sql server
> >     sqlcon.CommandTimeout = 10
> >
> >     sqlcon.Properties("Network Library").Value = "dbmssocn" ' set the
> > network library to use win32 winsock tcp/ip
> >     sqlcon.Properties("Data Source").Value = "xxxxx"
> >     sqlcon.Properties("Initial Catalog").Value = "sqlmigtest"
> >     sqlcon.CursorLocation = adUseServer ' For ADO cursor location
> >     sqlcon.Properties("User ID").Value = "sysdba"
> >      sqlcon.Properties("Password").Value = "Masterkey"
> >      sqlcon.Open
> >
> >     Dim rs As ADODB.Recordset
> >     Set rs = New ADODB.Recordset
> >
> >    SQLstatement = "SELECT * from tperson " ' Set a Simple Sql query to
> > return the servers time
> >     rs.Open SQLstatement, sqlcon  ' Lets open a connection with our new
> > SQLCON connection , and our SQL statement
> >     ' Move to first row.
> >     rs.MoveFirst
> >
> >     Dim r As String
> >     r = rs(6)
> >     MsgBox (r)
> >
> > End Sub
> >
> > Private Sub Command2_Click()
> > Dim rs As ADODB.Recordset
> > Dim r As String
> > Dim s As String
> >
> > s = "select * from tresourcetype"
> > Set rs = New ADODB.Recordset
> > rs.Open s, sqlcon
> > 'Error Occurs!!!
> >
> > r = rs(0)
> > MsgBox (r)
> >
> >
> > End Sub
> >
> >
> >
> > Niall Hannon
> > Management Information Systems
> >  ___________________________________________