Who knows arrays?

[James Stanich]

Looking to perform this task:

from a database load two field values in an array like

rst!Field1
rst!Field2

Question 1: How to a load it into an array?

Question 2: Based of user typing in response, how can I look up the value from the user in the array(Field 1) and return contents of Field 2 (kinda of a vlookup table in VB).

Please help !!!

[funkyd77]

try something like this

'Declare a dynamic array
Dim iMyArray() As Integer

'Dynamically make the array big enough to hold the recordset data
ReDim iMyArray(1 To 2, 1 To rst.RecordCount)

'Populate the array
Dim i As Integer
For i = 1 To rst.RecordCount
    iMyArray(1, i) = rst!Field1
    iMyArray(2, i) = rst!Field2
Next i

'This next bit would normally be in another sub

'Ask the user the first value then provide the second value
Dim iUserInput As Integer
iUserInput = Val(InputBox("Enter the First Value", "Enter Value"))

'look thru the array to find the input value from the user
For i = 1 To UBound(MyArray, 2)
    If MyArray(1, i) = iUserInput Then
        MsgBox "Other value is " & iMyArray(2, i)
        Exit Sub
    End If
Next i

'If it was not found then this condition will be true
If i = UBound(iMyArray, 2) + 1 Then
    MsgBox "You entered something that doesn't exist"
End If

[James Stanich]

Thanks for your help. I will try it out.

In your opinion would this be faster than looking in a database for the value each time the use inputs?

[funkyd77]

Yes it would be substantially faster. If you want the user to do this "lookup" many times during the life of your application running then you are probably best setting up the array in the beginning and then just using the array lookup when needed. If the lookup is only happening once during the programs executiion then it might not be worth doing this.

In my apps the database access is BY FAR the slowest part of the whole thing. I'm using ADO with access databases. I heard a rumour that ADO is quite slow (but a newer version might be faster) but DAO used to be quite quick. Not sure if this is true or not - anyone?

Another point worth noting: if Field1 and Field2 are of different data types then the array may not work as is - you would need to declare the array of type "Variant" to handle the different data types (eg if Field1 was integer and Field2 was string).

Personally when i have a whole heap of related data i programatically set up a recordset because you can append fields of different data types and use that in the program. So this recordset doesn no come from the database and doesn't have anything to do with the database but is just used as an internal "table" structure.

[James Stanich]

That exactly what I am trying to do. I done the same functionality in Excel (a vlookup table). Thanks for your time.

Another question, since you use Access databases, when connecting to the database, must you connect, recordet, disconnect, or can I just make the connection once and then perform multiple recordesets based ff the same connection? I know I'm rambling, but this would help me a great deal...

[funkyd77]

I believe you could make a public "Connection" object variable and set up the connectionstring etc in the beginning and then just use this connection object to use with your recordsets.

I don't do this but thats mainly because the application was already half written before i started working on it.

I have worked on an app before that had a gDBConn connection object (public object) that was used everywhere - this might speed things up too as far as the database access is concerned if you are not always opening the connection to the database.

[Paul282]

I tend to use arrays of User types, if you have lots of fields it's easier to follow the code...

To bollow from the code above...

'Declare a dynamic array in a Module

Public Type Record
   Name as String   ' or whatever your fields are
   Address as String
End Type

Public iMyArray() As Record


'Dynamically make the array big enough to hold the recordset data
ReDim iMyArray(1 To rst.RecordCount)

'Populate the array
Dim i As Integer
For i = 1 To rst.RecordCount
    iMyArray(i).Name = rst!Field1
    iMyArray(i).Address = rst!Field2
Next i

'This next bit would normally be in another sub

'Ask the user the first value then provide the second value
Dim iUserInput As Integer
iUserInput = Val(InputBox("Enter the First Value", "Enter Value"))

'look thru the array to find the input value from the user
For i = 1 To UBound(MyArray)
    If MyArray(i).Name = iUserInput Then
        MsgBox "Other value is " & iMyArray(i).Address
        Exit Sub
    End If
Next i

'If it was not found then this condition will be true
If i = UBound(iMyArray) + 1 Then
    MsgBox "You entered something that doesn't exist"
End If

[jbart]

Regarding the connection. You should be able to create just one connection object and then open as many recordsets as you need from that connection. For each database you will need a separate connection.

The connection can remain open for the life of the application. You can open and close your recordsets as necessary.

Small tip: Use the recordset.state property to check whether a recordset is open before you attempt to open it. If a recordset is already open and you try to open it, you will get an error. Might not help in your case, but it has saved me a lot of time. You can also use the "state" property with the connection to make sure it was successful.

[James Stanich]

Thanks for all who answered. I appreciate your knowledge.