Class Problem

[BobBob10]

Hi there, I was wondering if any one could help.

I'm new to classes but I want to create a class which holds information about my database connection.

I've created a class called CONNECT. In my class I have the following code.

<vb>
Option Explicit

Private m_strServer As String
Private m_strDatabase As String
Private m_strUID As String
Private m_strPwd As String
Public con As ADODB.Connection
Public rs As ADODB.Recordset

Property Let Server(ByVal newvalue As String)
m_strServer = newvalue
End Property

Property Get Server() As String
Server = m_strServer
End Property

Property Let Database(ByVal newvalue As String)
m_strDatabase = newvalue
End Property

Property Get Database() As String
Database = m_strDatabase
End Property

Property Let UserId(ByVal newvalue As String)
m_strUID = newvalue
End Property

Property Get UserId() As String
UserId = m_strUID
End Property

Property Let PassWord(ByVal newvalue As String)
m_strPwd = newvalue
End Property

Property Get PassWord() As String
PassWord = m_strPwd
End Property

Public Function Connect(ByVal strServer As String, ByVal strDB As String, _
ByVal strUID As String, ByVal strPwd As String, ByVal strQuery As String)

Set con = New ADODB.Connection
Set rs = New ADODB.Recordset

con.Open "Driver={SQL Server};Server=" & strServer & ";Database=" & strDB & ";Uid=" & strUID & ";Pwd=" & strPwd & ";"
rs.Open strQuery, con

End Function

Public Function DisConnect()

rs.Close
con.Close

Set rs = Nothing
Set con = Nothing

End Function
</vb>

This class holds all the information about connecting to the database and opens a record set.

However, in a normal module I have the following code:


<vb>


Public Sub UserForm_Initialize()

Dim myField As Variant, cmdText As String

Set oCONNECT = New CONNECT

Let oTramps.Database = "Test"
Let oTramps.UserId = "Cool"
Let oTramps.PassWord = "pa55word"
Let oTramps.Server = "Hind"

'Prepare command string
cmdText = "SELECT DISTINCT REF FROM football"

oCONNECT.Connect oCONNECT.Server, oCONNECT.Database, oCONNECT.UserId, oCONNECT.PassWord, cmdText

While Not rs.EOF
For Each myField In rs.fields
With FrmArrReport.ComboRef
.AddItem myField
End With
rs.MoveNext 'Move to the next record
Next myField
Wend

End sub

</vb>

However, the macro debugs due to the line "While Not rs.EOF" where an object is required. Is there any way for the programm to remember the recordset for when the macro goes back into the normal program.

Any help would be greatly appreciated.

Thanks

Bob.

 

[rmikesmith]

Hi Bob,

The main problem seems to be the unqualified reference to rs in the "normal" module. Since this is a member of the CONNECT Class you need to qualify it using the class variable, oCONNECT, so:

While Not [b]oCONNECT.[/b]rs.EOF

and

[b]oCONNECT.[/b]rs.MoveNext

The Next myField line also appears to be in the wrong place; i.e, it should be before the MoveNext method to the recordset.

Observation: I don't know where oTramps comes from.

One additional suggestion: I don't like to use names (variables, module names, etc.) that are identical to language keywords, objects, methods, properties, etc. In this instance, I would rename your class since connect is the name of a method of the ADO Connection object.


Regards,
Mike

 

[BobBob10]

Cheers dude, I'll try and implement that tomorrow.

Cheers for the helpful advice.

Bob

 

[TonyJollans]

Hi Bob,

You can't use a public variable in a Class like that. The recordset variable should exist in each instantiated CONNECT object (you and I may know there can only be one of them but VBA doesn't - and in theory you may have more than one connection) and you need to refer to it with a property or method of the class.

Given what you've got so far I hope that makes sense; if not, come back.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[BobBob10]

If I change the public to private such as

Private m_con As ADODB.Connection
Private m_RS As ADODB.Recordset

Then if I create two properties such as:

Property Let RS(ByVal newvalue As ADODB.Recordset)
Set newvalue = New ADODB.Recordset
m_RS = newvalue
End Property

Property Get RS() As ADODB.Recordset
RS = m_RS
End Property

Property Let CON(ByVal newvalue As ADODB.Connection)
Set newvalue = New ADODB.Connection
m_con = newvalue
End Property

Property Get CON() As ADODB.Connection
CON = m_con
End Property

However, how do I set these properties in the first place?

Any ideas?

 

[TonyJollans]

Hi Bob,

You should really keep everything in the Class Private; use the Methods you already have and add a Property Get (you shouldn't need a corresponding Set) to retrieve it ..

Private con As ADODB.Connection
Private m_rs As ADODB.Recordset

' Use existing Connect and DisConnect Methods - but set m_rs instead of rs

Property Get rs() as ADODB.Recordset
    Set rs = m_rs
End Property

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[