Linked Table Error: Object variable or With Block variable not set

[pgoulet]

This DB was originally developed with all the tables internal to the program. I am attempting to mirgrate the raw data to a separate Access DB tables. The process worked fine using the orginal table inside the current Access program. When I attempt to use the external table (tbl_Drops), I get the error Object vailable or With Block variable not set. When I modify the name of the external linked table (tbl_Drops_X)and rename the internal named back to tbl_Drops, the process works fine.

I can create and run queries on the external table fine. Do I need to establish a connection to the external table? Thank you.

### Program Snip
'This is from a linked Access table.
'When the table is local (within the current Access program,
'this process works correctly.

Dim db As DAO.Database
Dim RsIn As DAO.Recordset
Dim RsOut As DAO.Recordset

Set RsIn = db.OpenRecordset("qry_Vendor_Drop_Export") 'Requires no parameters
On Error Resume Next

With RsIn
.MoveLast ' Populate the recordset
If Err = 3021 Or .RecordCount = 0 Then
MsgBox "No records were found to be ready for storage. Please input your data."
db.Close
RsIn.Close
Set db = Nothing
Set RsIn = Nothing
Exit Sub
End If
End With

Set RsOut = db.OpenRecordset("tbl_Drops", dbOpenTable)
On Error GoTo Err_StoreRecords_Click

RsIn.MoveFirst
i = 0
tempTicket = 0
tempAmount = 0

While Not RsIn.EOF

With RsOut
MsgBox 51 'This msgbox is displayed, then the error message. No processing past this point

.AddNew
.Fields("[machine #]") = RsIn.Fields("[Machine #]")
.Fields("Amount") = RsIn.Fields("Amount")
.Fields("Ticket_Amount") = RsIn.Fields("Ticket_Amount")
.Fields("DropComment") = RsIn.Fields("DropComment")
.Fields("bus_Date") = Me!Bus_Date
.Fields("DTS") = Now()
i = i + 1

'Collect Data Totals
If IsNumeric(RsOut.Fields("Amount")) Then
tempAmount = tempAmount + RsOut.Fields("Amount")
End If
If IsNumeric(RsOut.Fields("Ticket_Amount")) Then
tempTicket = tempTicket + RsOut.Fields("Ticket_Amount")
End If

.Update
End With

RsIn.MoveNext
Wend

MsgBox "There were " & i & " " & Me!Text0 & " records stored, for $" & tempAmount & " dollars cash and $" & tempTicket & " dollars worth of tickets."


########

Thank you again.

 

[sostek]

Ah

Standard way to bring in external data is as follows:

Dim dbs as DAO.Database
Dim rst as DAO.Recordset
Dim strSQL as String

set dbs="C:\Path\dbname.mdb"
strSQL = "SELECT * FROM tablename WHERE conditon"

set rst = dbs.OpenRecordSet(strSQL)

And then use your With rst clause to manipulate the data.

 

[RoyVidar]

Change the dbOpenTable to, for instance dbOpenDynaset. I don't think dbOpenTable is available on linked tables.

Roy-Vidar

 

[pgoulet]

Roy-Vidar ...

Great catch!!! You hit that nail right on the head!!! I am going to make sure that I understand the properties of the different options. Thanks

I had been watching the error prompt come up for so long, that when the data finally stored msgbox displayed, I was thinking something was wrong.

Sostek, thanks for the comments.

Paul