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]

What line of code returns the error?

 

[Golom]

This

rst2![employee] = getNumber(rst1![employee])
rst2![employee].Append

Should be

rst2.AddNew
rst2![employee] = getNumber(rst1![employee])

There is no "Append" method for a recordset or a field in a recordset.

 

[pgoulet]

Thanks for the Help Golom, but I am still getting an error

Run-time error '3251'
Object or provider is not capable of performing requested operation.

Any ideas.

 

[vbajock]

Change rst1.CursorType = adOpenStatic to adOpenDynamic or adOpenForwardOnly

 

[MichaelRed]

In general terms, it is not normal to 'append' records in htis manner. I (generally) import the 'entire' record set into a temp table with an added column (or several). The easiest ot 'understand' is a single additional column which is used for the status. V&V operations (update queries) are used to identify (and mark) non-conforming records, usually with a single letter code for each nonconformance. When all V&V is complete, all records which do not have a status column entry may be appended to the 'real' recordset (table) with either an update or append query, depending on the details / nature of the process. Transforms like stripping the alpha chars from the alpha-num fields can be accomplished in the final step.

Obviously, this entails a bit of thought, setting up the various V&V queries and generating a procedure to cycle through all of them -making the last one do the actual update / append to the 'real' recordset. The other side of it is that it becomes quite easy to modify the V&V issues when ever the business rules change.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[cmmrfrds]

You should be able to just run a query to accomplish. Assuming the number is always on the left in Employee and Machine.

INSERT INTO yourothertable
SELECT val(Employee), val(Machine), yourdate, Account
FROM yourtable

 

[vbajock]

It might work for him but, I spend 50% of my time writing barcode stuff, and I've done it for 10 years. For a barcode scanning app I'd be shy about using queries. They are a lot slower than ADO and if barcode data doesn't appear on the screen in .5 seconds the users start screaming. The tolerance of the user for any slowness at all in the barcode arena is very,very low. ADO with a SQL server BE are getting to be the only way to fly on barcode apps if you've got anymore than 4 clients. I didn't even consider using piggy Access for barcode until this year, now that the hardware has gotten so fast it can work.

I don't know how he's trying to use this. If he is just pulling downloaded barcode records, your right. If he is trying to do a user app, I might be.

 

[pgoulet]

vbjock and cmmrfrds, Thanks for the pointers.


I am still getting the error: Which seems to happen on rst2!addNew

Run-time error '3251'
Object or provider is not capable of performing requested operation

cmmrfrds I have not fully tested your suggestions yet. Will let you know.

Once the data is downloaded from the scanners (appended to an empty .dat file) I import the data and append the data to a working (Access)table. I then append the downloaded .dat (flatfile) to a .bak file and copy over a "header" .dat file that the scanner appends to.

The actual data that is used by the users is already in an Access table after the import and append process. You are right about the delay of reading a flat file by users. Admittedly it was a painfully slow process. The continous process of downloading the scanners, importing and appending solved that problem.

Does anyone have a different idea about how to:
read a file, verify the fields with current data, code it to be either accepted or rejected because of some value and append the data that has been accepted to the working file.

Thanks again for all the help.

 

[vbajock]

did you change your cursor type to adOpenDynamic?

 

[cmmrfrds]

Typically, you would not use the ! operator for a method. The dot operator is used to indicate methods of an object such as the recordset.
rst2.addNew
The ! is used for properties that cannot be identified at coding time. For example, in the Fields collection of the recordset object.
rst1![employee]

vbajock, why do you suggest other than a static cursor with Access. All the processing is handled in the client so it might as well be a client side cursor. The Access OLE-DB Provider forces the client side cursor to static regardless of how it is coded.

 

[vbajock]

cmmrfrds ,I don't know where the guys data is coming from. Some back ends don't support all the cursor types and you will get the error "Run-time error '3251'
Object or provider is not capable of performing requested operation". Sometimes just changing the cursor will cure it.

 

[pgoulet]

Thanks for the help.

I changed the cursur type to adOpenDynamic but the process still bails out on rst1.addNew.

 

[MichaelRed]

As far as I know AddNew applies to DAO recordsets, not ADO?



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[vbajock]

ok, we need to know more about where data is coming from. The 3251 error is trying to tell you something is not right with your data source. Where

First question, where is tbl_1, tbl_2 physically residing? Is it an access table or a what?

Next question, you have no lock type specified in your open statement. For the sake of effiency, you can combine all the recordset parameters into one statement. You might also try declaring a separate connection object. Example:

Dim rst2 As ADODB.Recordset
dim rst1 As ADODB.Recordset
dim cnn as ADODB.Connection

Set rst1 = New ADODB.Recordset
Set rst2 = New ADODB.Recordset
Set cnn = New ADODB.Connection

Set cnn = CurrentProject.Connection

'==get rid ofrst1.ActiveConnection
'CurrentProject.Connection
'rst2.ActiveConnection = CurrentProject.Connection
'=====================

'add this
rst1.Open "SELECT * FROM tbl_1", cnn, adOpenDynamic, adLockOptimistic, adCmdText

'===get rid rst2.ActiveConnection
'CurrentProject.Connection
'rst1.CursorType = adOpenStatic
'rst2.CursorType = adOpenStatic
'rst2.Open "SELECT * FROM tbl_2", options:=adCmdText
'========

'add this
rst2.Open "SELECT * FROM tbl_2", cnn, adOpenDynamic, adLockOptimistic, adCmdText

 

[pgoulet]

cmmrfrds

I got the Insert statement working.

Now I just need to code the various fields if they do not match current tables.

Thanks everyone for the help.

 

[cmmrfrds]

Although a query will work for what you showed, I went back and looked at the ADO code. The addnew is not a method with ADO it was a method with DAO. ADO knows whether a record is being inserted or updated, so just use update - get rid of the addnew.

 

[vbajock]

If you guys aren't right about addnew. It is an ADO method that has to be there to do an append. The one you don't need in ADO is .edit, which is needed in DAO.

 

[cmmrfrds]

Thank you for the correction vbajock. I went too fast on that one. Sorry, ignore my comment on addnew.

 

[MichaelRed]

but the ubiquitous {F1} (a.k.a HELP) does not show addnew as applying to ADO*, all "applies to" items are DAO*?




MichaelRed
m.red@att.net

Searching for employment in all the wrong places