MoveLast on TableDef Recordset

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table whose primary key is an autonumber field. I have Access Basic code that is reading data from a .csv file and adding records to the database. The csv file is not tabular in nature. It contains comment lines that indicate the nature of the following line of data. The code uses the comment line to determine the table that the data is added to

The code sequence does the following with the data after reading the comment line

1. create TableDef object on Table
2. open recordset using tabledef objec
3. AddNew to record se
4. Update record se
5. close recordse
6. does some other cod
and then if finds a comment line that has additionalal data to add to record added in step 3 &
7. create TableDef for same table as step
8. open recordset using tablede
9. MoveLast on record se

At this point the recordset does not contain the record that was added in steps 3 & 4. The Table properties has order by set to the primary key. I can repeatedly perform the AddNew method to this table and the new records will show up if I open the table. However, all of the MoveLast methods against a record set for this table returns the same record all of the time. The record returned has a primary key that is well below all of the new records added

There is no change in this behavior if I close the Database and reopen it

I am using Access 2003

Does anyone have a suggestion to get around this problem with MoveLast?
 
What happens if you set the Bookmark of the recordset to LastModified
instead of using MoveLast?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Ed B said:
I have a table whose primary key is an autonumber field. I have Access
Click to expand...
Basic code that is reading data from a .csv file and adding records to the
database. The csv file is not tabular in nature. It contains comment lines
that indicate the nature of the following line of data. The code uses the
comment line to determine the table that the data is added to.
The code sequence does the following with the data after reading the comment line.

1. create TableDef object on Table.
2. open recordset using tabledef object
3. AddNew to record set
4. Update record set
5. close recordset
6. does some other code
and then if finds a comment line that has additionalal data to add to record added in step 3 & 4
7. create TableDef for same table as step 1
8. open recordset using tabledef
9. MoveLast on record set

At this point the recordset does not contain the record that was added in
Click to expand...
steps 3 & 4. The Table properties has order by set to the primary key. I
can repeatedly perform the AddNew method to this table and the new records
will show up if I open the table. However, all of the MoveLast methods
against a record set for this table returns the same record all of the time.
The record returned has a primary key that is well below all of the new
records added.
 
The bookmark does not work since I have closed the original recordset and opened a new one on the table name.
 
During debugging I noticed that the recordset 'record count' is higher than the tabledef 'record count'. In fact when the MoveLast method is executed the current record corresponds to the tabledef record count

Is there a method that I need to call to get the tabledef record count updated after the recordset Update method is called

Here is an example of the code I am using


' open a recordset in tableNam

Set rst = tbl.OpenRecordset(

' add a new recor

If lastTableName = "Tests" And fieldNames(1) <> "Test Type" The
' exception: Tests records have two lines, so add to existing record for second line..
rst.MoveLas
rst.Edi
Els
rst.AddNe
End I




' add the record to the database and close the recordse

rst.Updat
rst.Clos

Thanks
Ed
 
Your original post did say you closed the recordset, so you are correct:
Bookmark is not applicable. (Closing and reopening is actually quite
time-consuming, so I usually try to avoid that.)

One possibility is that the index for this table is corrupted, but before
going down that path, consider grabbing the primary key value of the new
record before closing, and then finding that record. The value of NoMatch
after the FindFirst could be helpful in debugging. Alternatively, just open
that record, and see if it exists.

The value of RecordCount will depend on the type of Recordset you opened. It
sounds like you are using a local JET table (not an attached table), so
OpenRecordset() returns a dbOpenTable type. I'm not sure how long it takes
for Access to bring the TableDef's RecordCount up to date, but opening just
the one record would return a dbOpenDynaset type, typically showing 1 if
records exist (the count accessed so far), or 0 if none (or perhaps -1 with
some non-JET linked tables where the result is undetermined).

Also noticed that you are using OpenRecordset on the table itself. If you
assign this variable, write records, and (without destroying and reassigning
tbl) expect the old variable to have the up to date count, I think you will
be disappointed. Opening against a Database may be better.

If you really must close the recordset and find the record again later, try
something like this:

Set rst = db.OpenRecordset(strTableName, dbOpenDynaset, dbAppendOnly)
rst.AddNew
'assign values for each field.
lngID = rst![ID]
rst.Update
rst.Close
'Do more stuff
Set rst = db.OpenRecordset("SELECT * FROM " & strTableName & " WHERE ID = "
& lngID & ";")

Please post back if that still does not give correct results.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Ed B said:
During debugging I noticed that the recordset 'record count' is higher
Click to expand...
than the tabledef 'record count'. In fact when the MoveLast method is
executed the current record corresponds to the tabledef record count.
Is there a method that I need to call to get the tabledef record count
Click to expand...
updated after the recordset Update method is called.
Here is an example of the code I am using.


' open a recordset in tableName

Set rst = tbl.OpenRecordset()

' add a new record

If lastTableName = "Tests" And fieldNames(1) <> "Test Type" Then
' exception: Tests records have two lines, so add to
Click to expand...
existing record for second line...
 
Back
Top