Help creating connection function

[Jozef]

Hello,

I'm trying to create a central function that runs a connection to an SQL
Server database. The connection etc works, but when I try to call it, I get
an error saying "Runtime-Error 91: Object variable or with block variable
not set". I have pasted the code below. The first section is the function
that I'm using, the second function is only the test that calls teh
genErrorColl function
'************** *** Start Code
*************** *************** *************** **
Private Const strServerAddres s = "MyDbServer "
Private Const strDatabaseName = "MyDatabase "
Private Const strUserName = "MyUserName "
Private Const strPassword = "MyPassword "

Public Function genErrorColl(st rTableName As String) As ADODB.Recordset

Dim cnn1 As ADODB.connectio n
Dim strCnn As String
Dim varDate As Variant

' Open connection.
strCnn = "Provider=SQLOL EDB.1;Persist Security Info=False;User ID=" &
strUserName & ";password= " & strPassword & ";Initial Catalog=" &
strDatabaseName & ";Data Source=" & strServerAddres s & ";Use Procedure for
Prepare=1;Auto Translate=True; Packet Size=4096;Use Encryption for
Data=False;Tag with column collation when possible=False"
Set cnn1 = New ADODB.connectio n
cnn1.Open strCnn

' Open the Requested Table or View
Set OpenRST = New ADODB.Recordset
OpenRST.CursorT ype = adOpenKeyset
OpenRST.LockTyp e = adLockOptimisti c
OpenRST.Open strTableName, cnn1, , , adCmdTable
cnn1.Close


End Function

Private Sub testConn()
Dim rst As ADODB.Recordset

Set rst = genErrorColl("v iewErrorCollect ion")

Do While Not rst.EOF
Debug.Print rst!kErrorLogID
rst.MoveNext
Loop

End Sub
'************* End of Code *************** *************** ******

Any help would be great.

Thanks!

[Justin Hoffman]

Re: Help creating connection function


"Jozef" <SPAM_O_NATOR@t elus.net> wrote in message
news:yq2ze.1706 76$El.87155@pd7 tw1no...[color=blue]
> Hello,
>
> I'm trying to create a central function that runs a connection to an SQL
> Server database. The connection etc works, but when I try to call it, I
> get an error saying "Runtime-Error 91: Object variable or with block
> variable not set". I have pasted the code below. The first section is
> the function that I'm using, the second function is only the test that
> calls teh genErrorColl function
> '************** *** Start Code
> *************** *************** *************** **
> Private Const strServerAddres s = "MyDbServer "
> Private Const strDatabaseName = "MyDatabase "
> Private Const strUserName = "MyUserName "
> Private Const strPassword = "MyPassword "
>
> Public Function genErrorColl(st rTableName As String) As ADODB.Recordset
>
> Dim cnn1 As ADODB.connectio n
> Dim strCnn As String
> Dim varDate As Variant
>
> ' Open connection.
> strCnn = "Provider=SQLOL EDB.1;Persist Security Info=False;User ID=" &
> strUserName & ";password= " & strPassword & ";Initial Catalog=" &
> strDatabaseName & ";Data Source=" & strServerAddres s & ";Use Procedure for
> Prepare=1;Auto Translate=True; Packet Size=4096;Use Encryption for
> Data=False;Tag with column collation when possible=False"
> Set cnn1 = New ADODB.connectio n
> cnn1.Open strCnn
>
> ' Open the Requested Table or View
> Set OpenRST = New ADODB.Recordset
> OpenRST.CursorT ype = adOpenKeyset
> OpenRST.LockTyp e = adLockOptimisti c
> OpenRST.Open strTableName, cnn1, , , adCmdTable
> cnn1.Close
>
>
> End Function
>
> Private Sub testConn()
> Dim rst As ADODB.Recordset
>
> Set rst = genErrorColl("v iewErrorCollect ion")
>
> Do While Not rst.EOF
> Debug.Print rst!kErrorLogID
> rst.MoveNext
> Loop
>
> End Sub
> '************* End of Code *************** *************** ******
>
> Any help would be great.
>
> Thanks![/color]


Your function genErrorColl is supposed to return a recordset, but I don't
see it doing it anywhere - it doesn;t seem to return anything. I can't see
that you've dimmed OpenRST - does this mean you are not using Option
Explicit to ensure your variables are dimensioned. You also close the
connection, before you have used the recordset.

If I were you, I would separate the code which gets a connection to the part
which gets a recordset. Although, there are any number of ways you could
structure your code, the first function might prove useful to re-use -it
either returns a database connection or returns nothing, allowing you to
give your users a non-nerdy error message.


Function DbConnection() As ADODB.Connectio n

On Error GoTo Err_Handler

Dim strCnn As String
Dim blnError As Boolean
Dim cnn As ADODB.Connectio n

strCnn = "Provider=sqlol edb;" & _
"Data Source=MyServer ;" & _
"Initial Catalog=MyDatab ase;" & _
"User Id=MyUser;" & _
"Password=MyPas sword;"

Set cnn = New ADODB.Connectio n

cnn.ConnectionS tring = strCnn

cnn.Open

Exit_Handler:

If blnError Then
Set cnn = Nothing
End If

Set DbConnection = cnn

Exit Function

Err_Handler:
blnError = True ' No error message here
Resume Exit_Handler

End Function



Public Sub TestConnection( )

On Error GoTo Err_Handler

Dim cnn As ADODB.Connectio n
Dim rst As ADODB.Recordset

Set cnn = DbConnection()

If Not cnn Is Nothing Then
Set rst = New ADODB.Recordset
rst.Open "SELECT COUNT(*) FROM tblLogins", cnn, adOpenForwardOn ly
While Not rst.EOF
MsgBox rst.Fields(0)
rst.MoveNext
Wend
Else
MsgBox "Cannot establish a database connection", vbCritical
End If

Exit_Handler:

On Error Resume Next

If Not rst Is Nothing Then
rst.Close
Set rst = Nothing
End If

If Not cnn Is Nothing Then
cnn.Close
Set cnn = Nothing
End If

Exit Sub

Err_Handler:
MsgBox Err.Description , vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub

[Jozef]

Re: Help creating connection function

DOH!

Thanks Justin....Never code when you're tired and having a glass of wine. I
had changed the name of the function, but obviously didn't check the rest of
it.

Thanks for the pointers on splitting it up. I usually try to do that in a
round about way, get the thing working first, then try and break it apart
(the original code had everything together, connection, record set, setting
new record, updating database) and this is my first step, pulling the
connection and record set items out into separate functions.

Thanks for your help!


"Justin Hoffman" <j@b.com> wrote in message
news:daiqmq$nv8 $1@nwrdmz01.dmz .ncs.ea.ibs-infra.bt.com...[color=blue]
>
> "Jozef" <SPAM_O_NATOR@t elus.net> wrote in message
> news:yq2ze.1706 76$El.87155@pd7 tw1no...[color=green]
>> Hello,
>>
>> I'm trying to create a central function that runs a connection to an SQL
>> Server database. The connection etc works, but when I try to call it, I
>> get an error saying "Runtime-Error 91: Object variable or with block
>> variable not set". I have pasted the code below. The first section is
>> the function that I'm using, the second function is only the test that
>> calls teh genErrorColl function
>> '************** *** Start Code
>> *************** *************** *************** **
>> Private Const strServerAddres s = "MyDbServer "
>> Private Const strDatabaseName = "MyDatabase "
>> Private Const strUserName = "MyUserName "
>> Private Const strPassword = "MyPassword "
>>
>> Public Function genErrorColl(st rTableName As String) As ADODB.Recordset
>>
>> Dim cnn1 As ADODB.connectio n
>> Dim strCnn As String
>> Dim varDate As Variant
>>
>> ' Open connection.
>> strCnn = "Provider=SQLOL EDB.1;Persist Security Info=False;User ID=" &
>> strUserName & ";password= " & strPassword & ";Initial Catalog=" &
>> strDatabaseName & ";Data Source=" & strServerAddres s & ";Use Procedure
>> for Prepare=1;Auto Translate=True; Packet Size=4096;Use Encryption for
>> Data=False;Tag with column collation when possible=False"
>> Set cnn1 = New ADODB.connectio n
>> cnn1.Open strCnn
>>
>> ' Open the Requested Table or View
>> Set OpenRST = New ADODB.Recordset
>> OpenRST.CursorT ype = adOpenKeyset
>> OpenRST.LockTyp e = adLockOptimisti c
>> OpenRST.Open strTableName, cnn1, , , adCmdTable
>> cnn1.Close
>>
>>
>> End Function
>>
>> Private Sub testConn()
>> Dim rst As ADODB.Recordset
>>
>> Set rst = genErrorColl("v iewErrorCollect ion")
>>
>> Do While Not rst.EOF
>> Debug.Print rst!kErrorLogID
>> rst.MoveNext
>> Loop
>>
>> End Sub
>> '************* End of Code *************** *************** ******
>>
>> Any help would be great.
>>
>> Thanks![/color]
>
>
> Your function genErrorColl is supposed to return a recordset, but I don't
> see it doing it anywhere - it doesn;t seem to return anything. I can't
> see that you've dimmed OpenRST - does this mean you are not using Option
> Explicit to ensure your variables are dimensioned. You also close the
> connection, before you have used the recordset.
>
> If I were you, I would separate the code which gets a connection to the
> part which gets a recordset. Although, there are any number of ways you
> could structure your code, the first function might prove useful to
> re-use -it either returns a database connection or returns nothing,
> allowing you to give your users a non-nerdy error message.
>
>
> Function DbConnection() As ADODB.Connectio n
>
> On Error GoTo Err_Handler
>
> Dim strCnn As String
> Dim blnError As Boolean
> Dim cnn As ADODB.Connectio n
>
> strCnn = "Provider=sqlol edb;" & _
> "Data Source=MyServer ;" & _
> "Initial Catalog=MyDatab ase;" & _
> "User Id=MyUser;" & _
> "Password=MyPas sword;"
>
> Set cnn = New ADODB.Connectio n
>
> cnn.ConnectionS tring = strCnn
>
> cnn.Open
>
> Exit_Handler:
>
> If blnError Then
> Set cnn = Nothing
> End If
>
> Set DbConnection = cnn
>
> Exit Function
>
> Err_Handler:
> blnError = True ' No error message here
> Resume Exit_Handler
>
> End Function
>
>
>
> Public Sub TestConnection( )
>
> On Error GoTo Err_Handler
>
> Dim cnn As ADODB.Connectio n
> Dim rst As ADODB.Recordset
>
> Set cnn = DbConnection()
>
> If Not cnn Is Nothing Then
> Set rst = New ADODB.Recordset
> rst.Open "SELECT COUNT(*) FROM tblLogins", cnn, adOpenForwardOn ly
> While Not rst.EOF
> MsgBox rst.Fields(0)
> rst.MoveNext
> Wend
> Else
> MsgBox "Cannot establish a database connection", vbCritical
> End If
>
> Exit_Handler:
>
> On Error Resume Next
>
> If Not rst Is Nothing Then
> rst.Close
> Set rst = Nothing
> End If
>
> If Not cnn Is Nothing Then
> cnn.Close
> Set cnn = Nothing
> End If
>
> Exit Sub
>
> Err_Handler:
> MsgBox Err.Description , vbExclamation, "Error No: " & Err.Number
> Resume Exit_Handler
>
> End Sub
>
>
>
>[/color]