"Jozef" <SP**********@telus.net> wrote in message
news:yq2ze.170676$El.87155@pd7tw1no...
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 strServerAddress = "MyDbServer"
Private Const strDatabaseName = "MyDatabase"
Private Const strUserName = "MyUserName"
Private Const strPassword = "MyPassword"
Public Function genErrorColl(strTableName As String) As ADODB.Recordset
Dim cnn1 As ADODB.connection
Dim strCnn As String
Dim varDate As Variant
' Open connection.
strCnn = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=" &
strUserName & ";password=" & strPassword & ";Initial Catalog=" &
strDatabaseName & ";Data Source=" & strServerAddress & ";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.connection
cnn1.Open strCnn
' Open the Requested Table or View
Set OpenRST = New ADODB.Recordset
OpenRST.CursorType = adOpenKeyset
OpenRST.LockType = adLockOptimistic
OpenRST.Open strTableName, cnn1, , , adCmdTable
cnn1.Close
End Function
Private Sub testConn()
Dim rst As ADODB.Recordset
Set rst = genErrorColl("viewErrorCollection")
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.Connection
On Error GoTo Err_Handler
Dim strCnn As String
Dim blnError As Boolean
Dim cnn As ADODB.Connection
strCnn = "Provider=sqloledb;" & _
"Data Source=MyServer;" & _
"Initial Catalog=MyDatabase;" & _
"User Id=MyUser;" & _
"Password=MyPassword;"
Set cnn = New ADODB.Connection
cnn.ConnectionString = 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.Connection
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, adOpenForwardOnly
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
