export from RecordSet in VBA to Access Tabe: How do I?

[asphynx]

I'm developing a vba routine that imports data from one table in Access into a recordset, manipulates the data, and exports the new data to a different table, but I don't know how to accomplish the last step.

Any suggestions?

 

[Junior1544]

please show the code you have so far.

--James
junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[asphynx]

Please see current code below. My thought is that the recordset will be sent to a new table (created earlier in the routine) almost at the end of the code (a couple of lines before the End Sub). The data in the "opt" array is what I need to send to the new table. Thanks in advance!!!


Option Compare Database
Public Sub test()

Dim dbs As Database
Dim maxquantifier, optquantifier, availquantifier, itemsize
Dim opt(70264, 2), item(610), ccount(610), temp(1 To 1, 1 To 4) As Double
Dim numrecs, count, count2, count3, count4 As Long
Dim misc As String
Dim quantifier, diff As Double
Dim tdf As TableDef



Set dbs = CurrentDb

Set tdf = dbs.CreateTableDef("NewResults&quot
With tdf
.Fields.Append .CreateField("Item", dbDouble)
.Fields.Append .CreateField("Item2", dbDouble)
.Fields.Append .CreateField("Quantifier", dbDouble)
.Fields.Append .CreateField("Margin", dbDouble)
dbs.TableDefs.Append tdf
End With

Set maxquantifier = dbs.OpenRecordset("ORQ&quot
If Not maxquantifier.BOF And Not maxquantifier.EOF Then
'get the number of records in the recordset need to movelast and move first to populate this property
maxquantifier.MoveLast
maxquantifier.MoveFirst
numrecs = maxquantifier.RecordCount
recarray = maxquantifier.GetRows(numrecs)
End If
maxquantifier.Close

Set availquantifier = dbs.OpenRecordset("sct&quot
If Not availquantifier.BOF And Not availquantifier.EOF Then
'get the number of records in the recordset need to movelast and move first to populate this property
availquantifier.MoveLast
availquantifier.MoveFirst
numrecs = availquantifier.RecordCount
'use "GetRows" method to assign to an array
recarray2 = availquantifier.GetRows(numrecs)
End If
availquantifier.Close

Set itemsize = dbs.OpenRecordset("ItemTotalQuantifier&quot
If Not itemsize.BOF And Not itemsize.EOF Then
'get the number of records in the recordset need to movelast and move first to populate this property
itemsize.MoveLast
itemsize.MoveFirst
numrecs = itemsize.RecordCount
'use "GetRows" method to assign to an array
recarray3 = itemsize.GetRows(numrecs)
End If
itemsize.Close

'recarray = optimizedresults table
'recarray2 = available quantifiers table
'recarray3 = item total quantifiers data
count = 0
For count = 0 To 610
ccount(count) = recarray3(2, count)
Next count

'The following loop populates the item array with item #
count = 0
For count = 0 To 610
item(count) = recarray3(0, count)
Next count


'The following loop populates the opt array with item #, linear quantifier, and diff
count = 0
For count = 0 To 69653
opt(count, 0) = recarray(0, count)
opt(count, 1) = recarray(1, count)
opt(count, 2) = recarray(2, count)
Next count

count = 0
count2 = 0
count3 = 0
quantifier = 0
'The following loop steps through each Item2 by item for current quantifier,
'avail. quantifier, min diff loss, etc. to populate each item with optimum
'quantifiers based on diff by item2 data
For count = 0 To 610
For count2 = 0 To ccount(count)
If item(count) = opt(count2, 0) Then 'matches item
quantifier = quantifier + opt(count2, 2)
End If
Next count2
Do While quantifier > recarray3(1, count)
diff = 1000000
For count3 = 0 To ccount(count)
For count4 = 0 To 2795
If recarray2(0, count4) = opt(count3, 1) Then 'matches Item2 #

If recarray2(1, count4) = opt(count3, 2) And count4 > 1 Then 'matches quantifier
If (diff > recarray2(3, count4) - recarray2(3, count4 - 1)) And recarray2(0, count4 - 1) = _
opt(count3, 1) Then
diff = recarray2(3, count4) - recarray2(3, count4 - 1)
temp(1, 1) = recarray2(0, count4 - 1)
temp(1, 2) = recarray2(1, count4 - 1)
temp(1, 3) = recarray2(3, count4 - 1)
temp(1, 4) = count3
End If
End If
End If
Next count4
Next count3
opt(temp(1, 4), 2) = temp(1, 2)
quantifier = 0
For count2 = 0 To ccount(count)
If item(count) = opt(count2, 0) Then 'matches item
quantifier = quantifier + opt(count2, 2)
End If
Next count2
Loop
Next count

dbs.Close

End Sub

 

[lestatdelioncourt]

just do some thing like:

dim stmt as string

stmt = "insert into " & table_created & " values(" & opt1 & &quot"

docmd.runsql stmt


this will insert a row into your new table. If the number of values in the array is dynamic then put a loop around the building of the statement.

ie
stmt = "insert into " & table_created & " values(" & opt1

for i = 1 to ubound(opt)
stmt = stmt & "," & opt(i)
next i

stmt = stmt & &quot"

docmd.runsql stmt

 

[lestatdelioncourt]

sorry missed some brackets


dim stmt as string

stmt = "insert into " & table_created & " values(" & opt(0) & &quot"

docmd.runsql stmt


this will insert a row into your new table. If the number of values in the array is dynamic then put a loop around the building of the statement.

ie
stmt = "insert into " & table_created & " values(" & opt(0)

for i = 1 to ubound(opt)
stmt = stmt & "," & opt(i)
next i

stmt = stmt & &quot"

docmd.runsql stmt

 

[asphynx]

Ok, I used the code you suggested, but am getting an error message:

Syntax error in INSERT INTO statement

Looks like the code you provided is exactly what I need, but may be missing a punctuation element (or something). Any additional suggestions? Thanks for you time and assistance!