| 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@telus.net> wrote in message
> news:yq2ze.170676$El.87155@pd7tw1no...[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 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![/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.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
>
>
>
>[/color]
| 
