Read-Only RecordSet

[tacieslik]

What code would open a record set based on a linked table and take a snapshot of the table. Then export the table contents to another table? I think it may help with the following problem.

I currently use this code which works fine, but the if I run a query based on the final table the data is exported to, I get 'Lock errors' because the table is being written to. This is a very big problem that I need to overcome this week. Any help from you guys would be very kind.

START CODE

Option Compare Database
Function ExportBlocks()


Dim rst As RecordSet
Dim newtable As RecordSet
Dim strSQL As String
Dim db As DAO.Database

Set db = CurrentDb
Set rst = db.openrecordset("BLOCKS")
Set newtable = db.openrecordset("ACCESS_BLOCKS")

' This will delete all records from the 'ACCESS_BLOCKS' table.
db.Execute "DELETE * FROM ACCESS_BLOCKS;"

If Not rst.EOF Then
rst.MoveFirst
Do
With newtable
.AddNew
!CRANE = rst!CRANE
!COL = rst!COL
!Row = rst!Row
!SIDE = rst!SIDE
!CELLSTATUS = rst!CELLSTATUS
!LOADNO = rst!LOADNO
!PARTNO = rst!PARTNO
!VENDOR = rst!VENDOR
!PACKAGING = rst!PACKAGING
!CONSOL = rst!CONSOL
!DERIVNO = rst!DERIVNO
!QTY_STORED = rst!QTY_STORED
!CONDITION = rst!CONDITION
!PRODSTATUS = rst!PRODSTATUS
!ISW = rst!ISW
!SEQNO = rst!SEQNO
!STIME = rst!STIME
!MIRROR = rst!MIRROR
!ORIGIN = rst!ORIGIN
!TELENO = rst!TELENO
!RTIME = rst!RTIME
!PRIORITY = rst!PRIORITY
!DEST = rst!DEST
!Source = rst!Source
!NOTES1 = rst!NOTES2
!NOTES2 = rst!NOTES2
!QTY_RETRV = rst!QTY_RETRV
!BATCHID = rst!BATCHID

.Update
End With
rst.MoveNext
Loop Until rst.EOF
DoCmd.TransferDatabase acExport, "Microsoft Access", "c:\projects\j1427\access\SSCSMirrorDataBlk.mdb", acTable, "ACCESS_BLOCKS", "BLOCKS", False
End If


End Function




TAC

 

[crosmill]

Can you not just create a query that exports it to a table?

 

[Lierduh]

The problem might be within the export step. You should be able to open two recordsets even from the same table.

Normally I open the source as:

Set source = Currentdb().OpenRecordSet ("...", dbOpenForwardOnly)

or dbOpenSnapshot if not forward only.

target as:

Set Target = Currentdb().OpenRecordSet("...", dbOpenDynaset, dbAppendOnly)


dbAppendOnly can speed things up if all you need is to .AddNew.