Link AppendQuery from ODBC table

B

Brian

I have a ODBC Table the contains over 300,000 records and I have created a
query that appends about 25,000 records based on a date criteria "This only
allows me to work with a smaller data set".

I have created this code below, but I can't get it to passed rstNEW.Update
it seems to bomb out. I think it is not able to open up my qryTimberline.


Private Sub NewInvoices()


On Error GoTo ErrorHandler

Dim rstOld As DAO.Recordset
Dim rstNEW As DAO.Recordset

Set dbs = CurrentDb
Set rstNEW = dbs.OpenRecordset("qryTimberline")
Set rstOld = dbs.OpenRecordset("qryMasterInvoice")
rstOld.MoveFirst
Do While Not rstOld.EOF
rstOld.Edit
rstNEW![Invoice] = rstOld![Invoice]
rstNEW.Update
rstOld.MoveNext
rstNEW.MoveNext
Loop

ErrorHandlerExit:
Exit Sub

ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & _
Err.Description
Resume ErrorHandlerExit

End Sub
 
B

Brendan Reynolds

You're putting rstOld into edit mode, but attempting to update rstNew ...
rstOld.Edit
rstNEW![Invoice] = rstOld![Invoice]
rstNEW.Update
Click to expand...

If it's rstNew you want to update, try ...
rstNEW.Edit
rstNEW![Invoice] = rstOld![Invoice]
rstNEW.Update
Click to expand...

--
Brendan Reynolds

Brian said:
I have a ODBC Table the contains over 300,000 records and I have created a
query that appends about 25,000 records based on a date criteria "This
only
allows me to work with a smaller data set".

I have created this code below, but I can't get it to passed
rstNEW.Update
it seems to bomb out. I think it is not able to open up my qryTimberline.


Private Sub NewInvoices()


On Error GoTo ErrorHandler

Dim rstOld As DAO.Recordset
Dim rstNEW As DAO.Recordset

Set dbs = CurrentDb
Set rstNEW = dbs.OpenRecordset("qryTimberline")
Set rstOld = dbs.OpenRecordset("qryMasterInvoice")
rstOld.MoveFirst
Do While Not rstOld.EOF
rstOld.Edit
rstNEW![Invoice] = rstOld![Invoice]
rstNEW.Update
rstOld.MoveNext
rstNEW.MoveNext
Loop

ErrorHandlerExit:
Exit Sub

ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & _
Err.Description
Resume ErrorHandlerExit

End Sub
Click to expand...
 
Top