Populate table from multi-dimension array

[ghh3rd]

I am looking for a way to populate a table from an multi-dimension array. Any tips?

Thanks,


Randy

 

[Banana]

Without knowing more, I'm not quite sure what every element and dimensions are supposed to represent.

Describe the array structure, please.

 

[boblarson]

You could use a recordset to do it:

Dim rst As DAO.Recordset
Dim lngNum As Long

Set rst = CurrentDb.Open("TableNameHere")

For 1 to UBound(YourArray)
   With rst
     .AddNew
     !FieldName = YourArray(lngNum)
     .Update
   End With
   lngNum = lngNum + 1
Next

(Air code - not tested, but it should give you a start)

 

[ghh3rd]

The array is (7, 12), if that helps.

Thx - Randy

 

[ghh3rd]

Bob,

At Set rst2 = CurrentDb.Open("test") I receive the message "Method or datamember not found." I do have a reference set for the DAO 3.6 Object Library.

Thanks,


Randy

 

[boblarson]

Bob,

At Set rst2 = CurrentDb.Open("test") I receive the message "Method or datamember not found." I do have a reference set for the DAO 3.6 Object Library.

Thanks,


Randy

Did you create your table first?

 

[petehilljnr]

I think it should be:

Currentdb.OpenRecordset("test")

Pete.

 

[boblarson]

I think it should be:

Currentdb.OpenRecordset("test")

Pete.

good point - missed that

 

[ghh3rd]

Thanks Banana, Bob and Pete.

Let me give a bit more info about what I'm trying to do:

I just output the multi-dimensional array DataHold(7,5) to the debugger:

36705500 87476000 12910140 137091640 49 2797789
33825500 81368000 3991856 119185356 47 2535859
63365500 61040000 3991856 128397356 52 2469180
74141451 112974000 2952400 190067851 52 3655151
174054880 83559990 5134365 262749235 59 4453377

I am trying to write this data to a table (tbltest) that has 7 fields (named 1 through 7 for now).

I am currently trying:

i = 1
lngNum = 1
Dim rst2 As DAO.Recordset
Set rst2 = CurrentDb.OpenRecordset("test")
For i = 1 To UBound(DataHold())
   With rst2
     .OpenRecordset
     .AddNew
     FieldName = DataHold(lngNum, lngNum)
     .Update
   End With
   lngNum = lngNum + 1
Next

This does insert the rows into the table, but does not enter anything. Since this is a multi-dimensional array, I'm not sure how to update from field to field. I used DataHold(lngNum, lngNum) to at least pull one element from the array, but it still didn't write it.

Can anyone offer more clarity on this for me?


Thanks,

Randy

 

[WayneRyan]

Randy,

Dim i As Long
Dim rst2 As DAO.Recordset
Set rst2 = CurrentDb.OpenRecordset("test")
For i = 1 To UBound(DataHold())
   With rst2
     .OpenRecordset
     .AddNew
     .Field("Field1" = DataHold(i, 1)
     .Field("Field2" = DataHold(i, 2)
     .Field("Field3" = DataHold(i, 3)
...
     .Field("Field7" = DataHold(i, 7)
     .Update
   End With
Next

hth,
Wayne

 

[WayneRyan]

Randy,

Also I think your array is "zero-based".

Wayne