0

I can't get the right primary key from MS-ACCESS using SQL command Select from query.

When i use a query that returns the last Auto number generated by access, sometimes it just returns a specific value, and not the actual LAST inserted auto number.

the problem persist until i repair the database.

The solution that i found was: when the auto number value isn't correct I try to "Compact and Repair the Database".

It solves the issue, and when i re-run my query it returns the last inserted value correctly.

But i can't be repairing the database every time that happens.

function GETID (): integer;
var
id: integer;
begin
 query1.SQL.Clear;
 query1.SQL.Add('INSERT INTO TABLE1 (CLIENTE,PEDIDO) values (0,0)');
 query1.ExecSQL;

 query1.SQL.Clear;
 query1.SQL.Add('select * from table1');
 query1.open;

 query1.last;

 id:=query1.Fields.Fields[0].AsInteger;

 return id;
end;

As shown in the code the variable ID should return the last auto number value from the TABLE1.

Sometimes it does, sometimes it get stuck in some random number

Example Last ID in the table: 120; run the function: inserts a row with success in the table and now has as last id 121; the function should return: 121; it returns 120;

run the function again: inserts a row with success in the table and now has as last id 122; the function should return: 122; it returns 120;

The issue maybe related to a internal communication between access and the SQL, because the table inside the database is correct, and show the right number of rows and auto numbers, but when i try to get those values via sql it just doesn't get it right.

Is there any way to avoid this recurring issue? in other words avoiding corrupting those internal auto numbers references?

Thanks.

Leonardo Floriano
  • 35
  • 7
  • Use the max function. – Rene Jul 16 '19 at 00:33
  • Use `SELECT MAX(YourIDField) FROM Table1`. – Ken White Jul 16 '19 at 00:37
  • now using the MAX function... let's see if the maximum ID doesn't get stuck, the function @@IDENTITY also work. But i will wait because the id getting stuck is at random and when it happens none of the functions work, they all return the same ID. – Leonardo Floriano Jul 16 '19 at 01:33
  • 2
    Your select query should include a order by clause. You can't assume table order in most databases, and that includes access. – Albert D. Kallal Jul 16 '19 at 02:12
  • 1
    See https://stackoverflow.com/questions/1628267/autonumber-value-of-last-inserted-row-ms-access-vba – donPablo Jul 16 '19 at 04:04
  • Single-user or multi-user database? In any case, you should find and fix whatever is causing the db corruption. – MartynA Jul 16 '19 at 11:18
  • Ok, as donPablo linked using @@IDENTITY should be safer than max... Answering MartynA, is a multi-user database ( 20 users Aprox.) And the corruption cause is unknown , also only one table gets "corrupted" , in other words it communicates the wrong auto number via sql, but with any other commands working ( select, update, insert, etc...). – Leonardo Floriano Jul 16 '19 at 18:09
  • Albert D. Kallal : thanks for the advice, it makes the query even more precise adding a order by ID in it. – Leonardo Floriano Jul 16 '19 at 18:10

0 Answers0