Passing Array to Table

[Excel_Kid1081]

Hello-

I have a code that creates a dynamic array. I would like to now put this array in a table...how would I do this in VBA? Any links with examples are much appreciated. Thanks!

EK

 

[DJkarl]

More information is needed.

Is this a single or multi dimensional array?
Are you adding records to an existing table or do you want to dynamically build the table with all of its fields from the array?

 

[MarkK]

Table access is provided by recordsets, which use SQL statements to SELECT records which you can then update. There are two flavours of recordsets ADO and DAO, each with slightly different purposes and syntax. Do a search in VBA Help or search this forum.

Alternatively you can execute SQL Statements which UPDATE, INSERT INTO, or DELETE FROM a table directly. This second method is faster, in terms of execution, but also somewhat more difficult to code, IMO.

Once you work with recordsets you may find that storing the results of your processing into an array and then writing these results to a table is cumbersome. I expect you'll be tempted to open a recordset and store the results of your processing directly, without using the array at all.

Cheers,
Mark

 

[Excel_Kid1081]

Thanks for the replies. I would like to build the table dynamically. The array is mulitdimensional (currently 16x16)... I have tried passing the array to another sub to create the field names like this....

Am I going in the right direction? Thanks!

Sub DefineTableFieldNames(ByRef arrDataHold() As String, ByRef arrIndexNames() As String)
Dim dbs As Database
Dim tdf As DAO.TableDef
Dim IndexID As DAO.Field
Dim IndexColTitles As DAO.Field
Dim TableName As String
Dim i As Long
Dim k As Long
'Name the output table
TableName = "Test"
On Error Resume Next
'if table already exists, delete it
dbs.TableDefs.Delete TableName
On Error GoTo 0
'Create table definition in memory
Set tdf = dbs.CreateTableDef(TableName)
'Loop through array and Set Field Definitons
    For i = 0 To UBound(arrIndexNames())
            Set IndexID(i) = tdf.CreateField
               With IndexID
                   .Name = IndexNames(i)
                   .Type = dbText
                   .Size = 20
                   .AllowZeroLength = False
                   .Required = True
               End With
    Next
'Append Fields to table
    For i = 0 To UBound(arrIndexNames())
       tdf.Fields.Append IndexID
    Next
Debug.Print arrIndexNames()
End Sub

 

[Excel_Kid1081]

The real issue with the code is that I cannot add a variable (ie. the counter) to the DAO.Recordset once its declared for instance...

Sub DefineTableFieldNames(ByRef arrIndexNames() As String)
Dim dbs As Database
Dim tdf As DAO.TableDef
Dim IndexID As DAO.Field
 
'Create table definition in memory
Set tdf = dbs.CreateTableDef(TableName)
 
'Loop through array and Set Field Definitons
    For i = 0 To UBound(arrIndexNames())
 
'Code fails right here...the &(i) cannot be added for
            Set IndexID&(i) = tdf.CreateField()
               With IndexID&(i)
                   .Name = arrIndexNames(i)
                   .Type = dbText
                   .Size = 20
                   .AllowZeroLength = False
                   .Required = True
               End With
    Next
end sub

So the only thing I need help with is getting the loop to work for IndexID then I can get the rest.

Thanks!

EK

 

[MarkK]

Whether the direction is 'right' depends on the destination, which you have not articulated. What happends when you run the code?

Things that might be problems. You assign the new DAO.Field to a subscripted variable IndexID, but I don't see where this is ever declared or ReDim'med as an array. dbs is used before it is assigned a value. IndexColTitles is never used. The way you use TableName, I would use a constant instead. You create the field in one loop and append it to the fields collection in a different loop. This can all be done in the same loop.

But apart from all that, to my mind a database system is like a house for data and the tables are the foundation, and the ancient wisdom goes, "The wise man built his house upon the rock." Dynamic tables--tables whose structure can change easily--are more like sand.

 

[MarkK]

Design a table first. Then open a recordset, which will assume the structure of that table. Then work with data.

 

[DJkarl]

Since you use arrays elsewhere why not use an array of fields?

Dim IndexID() As DAO.Field

Redim the field array to the same size as the arrays of names then

set IndexID(i) = tdf.CreateField()