Access VBA Copy table fields into another table

[moorecurls]

I'm trying to copy two fields from SampleData ("AllFields", "FieldName") into SampleDataAll ("1099YN", "SetId"). All fields are text. This works if I just copy one field rstTableName.Fields("AllFields") and change the Insert Into just one field "Insert Into SampleDataAll (1099YN) ". However, when I add the second field it gives me a wrong number of arguments or invalid property assignment error. I have a feeling it is the syntax but I've tried several different and I still get this error. If you have any ideas on how to fix, please let me know. Thanks in advance.



Public Sub loadIntoArray()
Dim rstTableName As DAO.Recordset 'Your table
Dim myArray() As String 'Your dynamic array
Dim intArraySize As Integer 'The size of your array
Dim iCounter As Integer 'Index of the array
DoCmd.SetWarnings False


'Open your table
Set rstTableName = CurrentDb.OpenRecordset("SampleData")


If Not rstTableName.EOF Then


rstTableName.MoveFirst 'Ensure we begin on the first row


'The size of the array should be equal to the number of rows in the table
intArraySize = rstTableName.RecordCount - 1
iCounter = 0
ReDim myArray(intArraySize) 'Need to size the array


Do While Not rstTableName.EOF


myArray(iCounter) = rstTableName.Fields(" & AllFields & ", " & FieldName &")
'Fields ("AllFields")
DoCmd.RunSQL "Insert Into SampleDataAll (1099YN, SetId) " & _
"Values('" & myArray(iCounter) & "');"
iCounter = iCounter + 1
rstTableName.MoveNext
Loop
End If
If IsObject(rstTableName) Then Set rstTableName = Nothing
DoCmd.SetWarnings True
End Sub

[ranman256]

to copy the data to another table, run append query
or
to copy the table itself to another table, run make table query.

this is an sql database ,there's no need to loop thru records.

[moorecurls]

Well this is the first step that I was trying to accomplish. The final data needs to split the "AllFields" which is delimited by "|" unto many different fields the first two of which is 1099YN and SetId. This is why I was trying to copy via a loop.

[moorecurls]

Here is what I have so far:
Public Sub LoadArrayParseData()
Dim rstTableName As DAO.Recordset 'Your table
Dim myArray() As String 'Your dynamic array
Dim intArraySize As Integer 'The size of your array
Dim iCounter As Integer 'Index of the array
Dim NameArray() As String
Dim arrString() As String
Dim splitValues() As String


DoCmd.SetWarnings False


'Open your table
Set rstTableName = CurrentDb.OpenRecordset("SampleIT_Orig_Data")


If Not rstTableName.EOF Then


rstTableName.MoveFirst 'Ensure we begin on the first row
'The size of the array should be equal to the number of rows in the table
intArraySize = rstTableName.RecordCount - 1
iCounter = 0
ReDim myArray(intArraySize) 'Need to size the array


Do While Not rstTableName.EOF
myArray(iCounter) = rstTableName.Fields("AllFields")

splitValues = Split(myArray(iCounter), "|")
If UBound(splitValues) <> 0 Then
YN = splitValues(0)
SetId = splitValues(1)

DoCmd.RunSQL "Insert into SampleITDataAll (YN, SetId) & Values ('" & YN & "','" & SetId & "');"
End If

' DoCmd.RunSQL "Insert Into SampleITDataAll (1099YN, SetId) " & _
"Values('" & myArray(iCounter) & "');"
iCounter = iCounter + 1
rstTableName.MoveNext
Loop
End If
If IsObject(rstTableName) Then Set rstTableName = Nothing
DoCmd.SetWarnings True
End Sub

This loads the array into the Insert statement but I get a run-time error 3134 Syntax error in INSERT INTO statement. Any ideas on how to both fix the insert as well as add the extra field "FieldName" into the Insert statement? Thanks again.

[Welshgasman]

I have no idea why you are using an array?
Start using code tags when posting code, that keeps the indentation and hopefully you are using that?

When you are not sure of what you are doing, put the sql string/criteria whatever into a string variable and debug.print that until you get it correct. Then you can use that in your function/command whatever.
Then comment out the Debug.Print when you eventually get it working.

strSQL =  "Insert into SampleITDataAll (YN, SetId) Values ('" & YN & "','" & SetId & "');"
Debug.Print strSQL
DoCmd.RunSQL strSQL

In fact as you cannot use Split() in a query, you need to create a wrapper function to split the data and return the required indexed data.
Then you could just use a simple query created in the Query design window.

Much easier in my view.

[moorecurls]

Thank you Welshgasman for reminding me that sometimes queries are best.

I took care of this issue by putting function into a query:
Public Function GetValueFromDelimString(sPackedValue As String, nPos As Long, Optional sDelim As String = "|")


Dim sElements() As String


sElements() = Split(sPackedValue, sDelim)
If UBound(sElements) < nPos Then
GetValueFromDelimString = ""
Else
GetValueFromDelimString = sElements(nPos)
End If
End Function

With the query looking like this:

INSERT INTO ITDataAll ( FileName, [1099 Y/N], SetId, VendorId, Location, Name1, Name2, Address1, Address2, Address3, Address4, City, State, Zip, TIN_Type, TIN, Withhold_Name1, Withhold_Name2, WH_Address1, WH_Address2, WH_Address3, WH_Address4, WH_City, WH_State, WH_Zip, WH_Code, Paid_Amt, Pay_Date, [Check] )
SELECT IT_Orig_Data.FileName, GetValueFromDelimString([AllFields],0) AS 1099YN, GetValueFromDelimString([AllFields],1) AS SetId, GetValueFromDelimString([AllFields],2) AS VendorID, GetValueFromDelimString([AllFields],3) AS Location, GetValueFromDelimString([AllFields],4) AS Name1, GetValueFromDelimString([AllFields],5) AS Name2, GetValueFromDelimString([AllFields],6) AS Address1, GetValueFromDelimString([AllFields],7) AS Address2, GetValueFromDelimString([AllFields],8) AS Address3, GetValueFromDelimString([AllFields],9) AS Address4, GetValueFromDelimString([AllFields],10) AS City, GetValueFromDelimString([AllFields],11) AS State, GetValueFromDelimString([AllFields],12) AS Zip, GetValueFromDelimString([AllFields],13) AS TIN_Type, GetValueFromDelimString([AllFields],14) AS TIN, GetValueFromDelimString([AllFields],15) AS Withhold_Name1, GetValueFromDelimString([AllFields],16) AS Withhold_Name2, GetValueFromDelimString([AllFields],17) AS WH_Address1, GetValueFromDelimString([AllFields],18) AS WH_Address2, GetValueFromDelimString([AllFields],19) AS WH_Address3, GetValueFromDelimString([AllFields],20) AS WH_Address4, GetValueFromDelimString([AllFields],21) AS WH_City, GetValueFromDelimString([AllFields],22) AS WH_State, GetValueFromDelimString([AllFields],23) AS WH_Zip, GetValueFromDelimString([AllFields],24) AS WH_Code, GetValueFromDelimString([AllFields],25) AS Paid_Amt, GetValueFromDelimString([AllFields],26) AS [Date], GetValueFromDelimString([AllFields],27) AS [Check]
FROM IT_Orig_Data;

This issue is now resolved. Thanks again for all the help.