Iterate and Append one table to another table Using code

[pgoulet]

Our operation requires a lot of bar code scanning.

Right now the data is coming in and we are using the data without verifying the data. The scanners are periodically downloaded and appended to the working access table by use of a DOS shell command.
1. We need to verify the data in some of our other existing databases.
a. Verified (accepted) data will be appended to the working file.
b. Questionable data will be set aside for determination if there is any useful data in the scan or manual corrections.

I am using the following code to learn the process of appending the record from one table to the working table with out any luck.

Eventually we want to get to data that has the “E” stripped off the employee number. (The “E” directs the bar code scanner to accept data in only a certain pattern.) The Machine number needs to only be a number. The Date and time fields are being concatenated with a “+” sign. Player is simply numeric (did something right, probably on accident.)

Using data from tbl_1 and tbl_2, the data is structurally the same.
Employee, Machine, Date+Time, Account
204E 542VGTM 11/24/2003 5:36:57 PM 3935
204E 544VGTM 11/24/2003 5:36:45 PM 10872
343E 865MMTM 11/24/2003 5:14:16 PM 1007
343E 878MMTM 11/24/2003 5:13:37 PM 277

To force the data into the format we eventually want, I am using a getNumber function I think I found here and works like a charm.
204 542 11/24/2003 5:36:57 PM 3935
204 544 11/24/2003 5:36:45 PM 10872
343 865 11/24/2003 5:14:16 PM 1007
343 878 11/24/2003 5:13:37 PM 277

I am using the attached code to (figure out) how to append the file the existing working data. With the error message of “Method or Data member not found.” Am I using the wrong structure? Any suggestions.

Private Sub Command0_Click()

Dim rst1 As ADODB.Recordset
Dim rst2 As ADODB.Recordset
Set rst1 = New ADODB.Recordset
Set rst2 = New ADODB.Recordset
rst1.ActiveConnection = CurrentProject.Connection
rst2.ActiveConnection = CurrentProject.Connection
rst1.CursorType = adOpenStatic
rst2.CursorType = adOpenStatic
rst1.Open "SELECT * FROM tbl_1", options:=adCmdText
rst2.Open "SELECT * FROM tbl_2", options:=adCmdText
Do Until rst1.EOF
'Comment next line to NOT use the getNumber function.
'Debug.Print getNumber(rst1![employee]); getNumber(rst1![machine]); rst1![Date] + rst1![timems]; rst1![account]
'Comment next line to use the getNumber function.
Debug.Print rst1![employee]; rst1![machine]; rst1![Date] + rst1![timems]; rst1![account]
'Append to rst2... code here, error results.
'rst2![employee] = getNumber(rst1![employee])
'rst2![employee].Append

rst1.MoveNext
Loop

If rst1.EOF = True Then
MsgBox "Done Processing Records!" ' Wow! it worked!
End If

rst1.Close
rst2.Close

Set rst1 = Nothing
Set rst2 = Nothing

Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub

 

[vbajock]

This is what is in my help file, accessed by drilling Recordset, ADO Recordset, Methods:

AddNew Method


Creates a new record for an updatable Recordset object.

Syntax

recordset.AddNew FieldList, Values

Parameters

recordset A Recordset object.

FieldList Optional. A single name, or an array of names or ordinal positions of the fields in the new record.

Values Optional. A single value, or an array of values for the fields in the new record. If Fieldlist is an array, Values must also be an array with the same number of members; otherwise, an error occurs. The order of field names must match the order of field values in each array.

Remarks

Use the AddNew method to create and initialize a new record. Use the Supports method with adAddNew (a CursorOptionEnum value) to verify whether you can add records to the current Recordset object.

After you call the AddNew method, the new record becomes the current record and remains current after you call the Update method. If the Recordset object does not support bookmarks, you may not be able to access the new record once you move to another record. Depending on your cursor type, you may need to call the Requery method to make the new record accessible.

If you call AddNew while editing the current record or while adding a new record, ADO calls the Update method to save any changes and then creates the new record.

The behavior of the AddNew method depends on the updating mode of the Recordset object and whether you pass the Fieldlist and Values arguments.

In immediate update mode (in which the provider writes changes to the underlying data source once you call the Update method), calling the AddNew method without arguments sets the EditMode property to adEditAdd (an EditModeEnum value). The provider caches any field value changes locally. Calling the Update method posts the new record to the database and resets the EditMode property to adEditNone (an EditModeEnum value). If you pass the Fieldlist and Values arguments, ADO immediately posts the new record to the database (no Update call is necessary); the EditMode property value does not change (adEditNone).

In batch update mode (in which the provider caches multiple changes and writes them to the underlying data source only when you call the UpdateBatch method), calling the AddNew method without arguments sets the EditMode property to adEditAdd. The provider caches any field value changes locally. Calling the Update method adds the new record to the current Recordset and resets the EditMode property to adEditNone, but the provider does not post the changes to the underlying database until you call the UpdateBatch method. If you pass the Fieldlist and Values arguments, ADO sends the new record to the provider for storage in a cache; you need to call the UpdateBatch method to post the new record to the underlying database.

If the Unique Table dynamic property is set, and the Recordset is the result of executing a JOIN operation on multiple tables, then the AddNew method can insert fields only into the table named in the Unique Table pr