By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,505 Members | 1,517 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,505 IT Pros & Developers. It's quick & easy.

Help creating connection function

P: n/a
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!
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a

"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


Nov 13 '05 #2

P: n/a
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:da**********@nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com...

"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

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.