Transform an array into a table and vice versa

[meneerdekoekepe]

Hi there,

I'm relatively new to VBA and Access 2k but I am getting the hang of it.

I wonder if anyone of you know how to put data, held by a 2D array, into a table

The array data is read from a text file, produced by another app. which I cannot change. I can read the textfile and get the data into the array but I don't know how to get the data into a table so I can work with it and display it.

Oh, avoiding working with array's is not an option. I need the array to perform matrix calculations on it.

Hope anyone can help me out here...

 

[WayneRyan]

m,

Need more info:

HowMany - HowMany array elements
YourArray - The Name of your Array (zero based?)
Array Values - Strings in this example - remove single-quotes if numeric

All array elements are inserted, without checking for their presence.

Just a quick idea:

Dim i As Long
For i = 1 to HowMany
   DoCmd.RunSQL "Insert Into YourTable (FieldA, FieldB) " & _
                "Values('" & YourArray(i, 1) & "', '" & YourArray(i, 2) & "');"
   Next i

Wayne

 

[meneerdekoekepe]

Hi there Wayne,

Thanks for the quick response. I haven't tried it yet, I'm only taking a peek at home...

I understand what you're doing, pretty clever. It never crossed my mind to use a runsql statement. I guess I still underestimate the powers of docmd

The array which i build from the input file (it contains an event tree, or decission diagram, made up from text characters) can take all kinds of dimensions,
it's always 2D,
I use option base 1,
it contains chars but i might change that into integers, doesn't matter, i can handle that...

How would you handle the reverse problem? Say I got a table as a source and I need to get every field from all records in a 2D array.
I thought I was going to use some sort of DLookup code but I got a funny feeling telling me that it's not going to be the most efficient piece of code ever written....

Thanks again for your time,

Michiel

 

[WayneRyan]

Michiel,

Copy from table to array. Can get fancier with "ReDim Preserve"

Dim aryTemp(100, 4) As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim lngHowMany As Long

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("YourTable")

lngHowMany = 0
While not rst.EOF And Not rst.BOF
   lngHowMany = lngHowMany + 1
   aryTemp(1) = rst!FieldA
   aryTemp(2) = rst!FieldB
   aryTemp(3) = rst!FieldC
   aryTemp(4) = rst!FieldD
   rst.MoveNext
   Wend

I think I've seen a GetData function call somewhere, will research ...

Wayne

 

[allan57]

Hi, I use the following method for populating arrays from recordsets

The following example uses the GetRows method to return a two-dimensional array containing all rows of data in a Recordset object:

Sub RowsArray()
Dim dbs As Database, rst As Recordset, strSQL As String
Dim varRecords As Variant, intI As Integer, intJ As Integer

' Return reference to current database.
Set dbs = CurrentDb
' Build SQL statement that returns specified fields.
strSQL = "SELECT FirstName, LastName, HireDate " _
& "FROM Employees"
' Open dynaset-type Recordset object.
Set rst = dbs.OpenRecordset(strSQL)
' Move to end of recordset.
rst.MoveLast
' Return to first record.

rst.MoveFirst
' Return all rows into array.
varRecords = rst.GetRows(rst.RecordCount)
' Find upper bound of second dimension.
For intI = 0 To UBound(varRecords, 2)
Debug.Print
' Find upper bound of first dimension.
For intJ = 0 To UBound(varRecords, 1)
' Print data from each row in array.
Debug.Print varRecords(intJ, intI)
Next intJ
Next intI
rst.Close
Set dbs = Nothing
End Sub

Example taken from ACCESS help

 

[meneerdekoekepe]

Thanks for all your help and sorry for getting back to you so late... Troubles at work.

I got my code up and running and it works like a dream I must say. It took me a while though to get the DAO library running ...