INSERT into last row of a table

[chris-uk-lad]

Hi,

Hitting an issue with an Access table, im trying to insert a value into the last line of a table as a customised trailer but can't seem to enter it into the very last row.

Im currently trying the criteria below but how do i link the recordset, having found the last 'occupied' row, to the insert query using the next blank row?

    Set recordNum = CurrentDb.OpenRecordset("SELECT * FROM Table1", dbOpenDynaset)
    recordNum.MoveLast
    lastNum = recordNum.RecordCount
    CurrentDb.Execute "INSERT INTO Table1 () VALUES ('T" & Format(DateTime.Now, "yyyymmddhhmmss") & "')"

Many Thanks

 

[dcb]

Im currently trying the criteria below but how do i link the recordset, having found the last 'occupied' row, to the insert query using the next blank row?

Help
Do you meen you want to insert a new row of data into your table - thats all? Not following the link to query thing

 

[chris-uk-lad]

Yes but i need to be sure its in the last free row of the currently populated table, hence specifying it with the recordcount.

 

[Galaxiom]

Tables don't work that way. You have no control over the position of the record. Tables only store the data. Every manipulation of that data needs to be done in queries and forms. Despite outward appearances they are not like spreadsheets at all.

 

[chris-uk-lad]

well with that, is there a way to edit the table once exported to CSV to specifically ammend the first and last row?

 

[Galaxiom]

Export the data as a query. These can be ordered in whatever way you can imagine.

 

[gemma-the-husky]

if order is important, you can store internally a sequence number.

if you can generate that order based on someother criteria eg time entered, then use that.

the issue is that access treats a set of data, as just that - a set with no order - so we often need to find a way to sort the data, to give the appearance of a true order. but access queries will still PROCESS any item in the set in the same way as any other item.

hence you cant insert into a last row as such - you just insert a new into a table - and then for any particular purpose you determine the active sort order you require.