Database Lookups with Classes

[Mindcrime]

As you can tell from my previous posts I am new to COM.

As I am the only VB programmer at work I need to try and do things to the best of my ability and how others might do it.

So this is what I want to do...

I have added a new class to my In-server process to return data from certain tables. For now I want my client app to request ALL Account Names from a table on the database.

I know how to do the SQL bit.

I have decided to use a Collection for this held in my class.

Q. Is this right?

Q. If this is correct can someone guide me further.
Q. If its not has anyone got a better solution?

Hope this makes sense.

[simonm]

Do you mean you want to use a collection to return the account names from your server component? If so, why not use a recordset?

[Mindcrime]

Would I need to access the recordset from the class or direct from my code?

Should I use UDT's instead of the class?

[simonm]

OK, I see what you're getting at now. You are returning your class to your client project. Your class needs to contain a variable number of account names.

You could use a collection and just expose it via a property of your class. Instantiate it in your class.Initialiize event and release it in your class.Terminate event.

What exactly do you need help with? You may need to provide more details and explain exactly what you're having problems with if you require more help.

[Mindcrime]

I feel we are getting somewhere now.

OK.
My recordset (in my class) will bring back TCI_ACCOUNT_NAME from the database n times.
TCI_ACCOUNT_NAME will need to be added to the collection from within the form.

My client project will do something like

Dim Acc as Account
For each Acc in ?
    Combo1.Additem Acc.Name
Next

What I am trying to do is minimize the code in my Client Project and maintaining a highly robust class/Project.

I have looked at the VB help on Collection with regards to the House of Straw/Sticks/Bricks examples, but these seem to be overkill for what I want to do.

I need help with generally the whole process from client to class to populating the class.

Thanks for your time.

[simonm]

The exmples you found in the VB help (House of Straw/Sticks/Bricks) are what you need to do if you want to itterate through the account names in the way that you have illustrated but I can see why you think it's overkill because your Account class only has one property: Name.

Still, you might as well set it up as it won't take long (if you know what to do).

Your Account class should look like this:

Option Explicit

Private mstrName As String

Public Property Get Name() As String
    Name = mstrName
End Property

Public Property Let Name(ByVal strNewValue As String)
    mstrName = strNewValue
End Property

Then you need a class called Accounts:

Option Explicit

Private mcolAccounts As Collection

Public Function AddNew(Optional intIndex As Integer) As Account
    Dim cAccount As Account
    
    Set cAccount = New Account
        
    If intIndex > 0 Then
        mcolAccounts.Add cAccount , , intIndex
    Else
        mcolAccounts.Add cAccount 
    End If
    
    Set AddNew = cAccount 
End Function

Public Function Item(Index) As Account
    Set Item = mcolAccounts.Item(Index)
End Function

Public Function Count() As Integer
    Count = mcolAccounts.Count
End Function

Public Sub Clear()
    Set mcolAccounts = New Collection
End Sub

Public Sub Remove(Index)
    mcolAccounts.Remove Index
End Sub

Private Sub Class_Initialize()
    ' Instantiate Accounts collection
    Set mcolAccounts = New Collection
End Sub

Private Sub Class_Terminate()
    Set mcolAccounts = Nothing
End Sub

' NewEnum must return the IUnknown interface of a
' collection's enumerator.
Public Function NewEnum() As IUnknown
   Set NewEnum = mcolAccounts.[_NewEnum]
End Function

Don't forget to set the "Procedure ID" of the NewEnum function to -4 and check "Hide this member". Also make the Item function the default property.

Now, in your main class, you will need a property that exposes the Accounts class:

Private mcAccounts

Private Sub Class_Initialize()
    ' Instantiate Accounts collection
    Set mcAccounts = New Accounts
End Sub

Private Sub Class_Terminate()
    Set mcAccounts = Nothing
End Sub

Public Property Let Accounts() As Accounts
   Set Accounts = mcAccounts
End Property

How to populate it should be pretty staight forward but if you have any further questions, let me know.

[Mindcrime]

Originally posted by simonm
Public Property Let Accounts() As Accounts
Set Accounts = mcAccounts
End Property[/CODE]

I assume you meant Get instead.

Thanks very much for your help.