"Jozef" <SP**********@t elus.net> wrote in message
news:yq2ze.1706 76$El.87155@pd7 tw1no...
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!
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
