Array to Table in one easy move

[Guest]

Good afternoon all,

I have had a good response to some previous question asked so I thought I might see if someone could explain the easiest way to transfer the contents of a flat array of 46 fields (ie Array_Name(0,45)) to a new record in a Table.

I have been looking at DoCMD, .Execute, RecordSet. I have 11,000,000 rows of data that I am breaking down into the array 46 rows at a time, then when I get to each 46th row I want to place the contents of that array as a new record into the a Table.

The help and other responses to this newsgroup that I have been looking at give a lot of examples based on other types of enquiries, but don't really directly answer my question, and the help examples keep crashing on my machine, especially

' Open a connection.
Set cnn1 = New ADODB.Connection
strCnn = "Provider=sqloledb;" & _
"Data Source=srv;Initial Catalog=Pubs;User Id=sa;Password=;"
cnn1.Open strCnn

which the help shows in a number of examples but I can not get to work.

The basic set-up I have is

An Array - Array_Name(45,0)
A Table - Import_table (Which contains 46 columns of text data, each 255 characters)

In my mind, which I know doesn't equal the Microsoft mind, programmatically I want to say:

row_current = 0
row_last = 45

While row_current < row_last + 1

Import_Table(row_current) = Array_Name(row_current,0)
Row_current = row_current + 1

Wend

Is this possible ?

Sorry for the long winded question I just wanted you to have all the information. Any help you may be able to provide would be greatly appreciated.

Yours sincerely,

Brent McIntyre

 

[Guest]

Good evening all,

I finally worked out that most of my problems were due to the DAO Objects being missed in the References List. If anyone has ideas about the While Loop idea though I would love to hear them.

Yours sincerely,

Brent McIntyre

 

[spencer4]

You might have a insurmountable problem.

46 fields of 255 characters each will exceed the max record size - 2k or 4k.
That is if all 255 characters are used in each field. Hard to say how long
your data is.

How are your fields named? If they aren't named in some regular way (Field1,
Field2, ...) then you will have to build an array of the field names or make
sure your recordset is set up properly. If the fields are in the exact same
order as the array, then you could use something like

'CODE SNIPPET of ONE possible way to do this
Dim rstTarget as DAO.Recordset
Dim dbTarget as DAO.RecordSet
Dim intFieldNum as Integer

Set dbTarget = CurrentDb()
Set rstTarget = dbtarget.OpenRecordset ("SELECT * FROM Import_Table")

With rstTarget
.AddNew
For IntFieldNum = 0 to 45
'You'll need to test here for nulls and zero-length strings
'or unacceptable values
.Fields(intFieldNum) = ArrayName(intFieldNum)
next intFieldNum
.Update
End with 'rstTarget

With 11 million records to process this could take a while, so you might want
to include an update routine to let you know how things are going. The above
snippet of code only handles processing the data after the array is loaded.

 

[Guest]

Spencer,

Thanks for your help, this is going along what I thought, I realise it is going to be a long process, hopefully I can procure some more memory from my colleagues.

Thanks again for the help it is greatly appreciated.

Yours sincerely,

Brent McIntyre