We help IT Professionals succeed at work.
Get Started

Run SQL on VB arrays

Last Modified: 2010-04-30
The following is an extract of a file

CODE      BDATE            NAME
682803      26/01/01      CROATIA OSIGURANJE
685750      09/10/98      CROATIA OSIGURANJE PREF.
51215F      24/08/06      HOTEL DUBROVNIK
414415      01/12/06      INA INDUSTRIJA NAFTE
682813      14/06/94      ISTRATURIST UMAG
50939L      06/08/07      MAGMA
32522N      02/04/04      MEDIKA
673272      25/01/95      PLIVA
286613      05/01/96      VARTEKS
36180J      31/05/06      VIRO TVORNICA SECERA
281107      23/08/96      ACI OTC
27218X      24/06/03      AD PLASTIK
30464X      02/01/07      ADRIS GRUPA
30462C      31/07/03      ADRIS GRUPA PREF
26777T      06/02/03      AGROLAGUNA
32330V      03/11/05      AGROMEDIMURJE

I use this code to ingest into a vb6 array.

Option Explicit
Dim AryData() As String, DataCt As Integer, MaxDataCt As Integer
Dim AryTitles() As String, TitlesCt As Integer
Private Sub Form_Load()
ReDim AryTitles(1 To 3) As String
DataCt = 1
MaxDataCt = 50
ReDim AryData(1 To 3, 1 To MaxDataCt)
Open "C:\test.csv" For Input As #1
Input #1, AryTitles(1), AryTitles(2), AryTitles(3)
Do Until EOF(1)
    Input #1, AryData(1, DataCt), AryData(2, DataCt), AryData(3, DataCt)
    DataCt = DataCt + 1
    If DataCt = MaxDataCt Then
        MaxDataCt = MaxDataCt + 50
        ReDim Preserve AryData(1 To 3, 1 To MaxDataCt)
    End If
ReDim Preserve AryData(1 To 3, 1 To DataCt - 1)
End Sub

I would like to use some features of SQL on this array e.g to sort it and to group it (much easier in SQL than VB).  Please can you amend the above code, so I can put this into a recordset and run some SQL.  For example give me a count of how many codes are all numeric and how many codes end with a letter.
Watch Question
This problem has been solved!
Unlock 1 Answer and 19 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE