New record using ADO link and AutoNumber in Access 
Author Message
 New record using ADO link and AutoNumber in Access

How do i proceed to get the next AutoNumber in an Access table. Is there a
way to no in the Dataset what is the next number in my AutoNumber ID.

I am sure it is easy but i am pretty new to Access database and to ADO.

Thx for a quick answer ...

Jean-Guy Levesque



Fri, 02 Dec 2005 22:31:11 GMT  
 New record using ADO link and AutoNumber in Access
you can try this function :

function TModule_base.last_value(field:string; table:string): integer;
var Q:TADOQuery;
  s:string;
  r:variant;
begin

 Q:=TADOQuery.create(self);

try
 s:='Select Max(' + field + ') AS ' + field + ' from ' + table;

 Q.Active := False;
 Q.Connection:=my_ADOConnection;        // adapt this line to your
connection
 Q.SQL.Clear;
 Q.SQL.Add(s);
 Q.Active := True;

 if Q.RecordCount >= 0 then
  r := Q.Fieldvalues[field];

 if varisnull(r) then result:=0 else result:=r;

finally
 Q.Free;
end;

end;

then use it to give your field an incremented value :

my_table.fieldvalues['some_field] := last_value('some_field','my_table') +
1;

there are other solutions

Denis



Fri, 02 Dec 2005 23:12:38 GMT  
 New record using ADO link and AutoNumber in Access
Thanks it was really helpful. Usually you should not touch the AutoNumber in
Access database, like they say in their help, but it hel to know which
number is the next one.



Quote:
> you can try this function :

> function TModule_base.last_value(field:string; table:string): integer;
> var Q:TADOQuery;
>   s:string;
>   r:variant;
> begin

>  Q:=TADOQuery.create(self);

> try
>  s:='Select Max(' + field + ') AS ' + field + ' from ' + table;

>  Q.Active := False;
>  Q.Connection:=my_ADOConnection;        // adapt this line to your
> connection
>  Q.SQL.Clear;
>  Q.SQL.Add(s);
>  Q.Active := True;

>  if Q.RecordCount >= 0 then
>   r := Q.Fieldvalues[field];

>  if varisnull(r) then result:=0 else result:=r;

> finally
>  Q.Free;
> end;

> end;

> then use it to give your field an incremented value :

> my_table.fieldvalues['some_field] := last_value('some_field','my_table') +
> 1;

> there are other solutions

> Denis



Tue, 06 Dec 2005 04:10:58 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Record locking with ADO Express, ADO 2.5 and Access 2000 .mdb files

2. ADO new record

3. Problems: Delete Records with ADO/ACCESS

4. Using ADO with Encrypted/Password Protected Access databases

5. Creating an MS Access DB using ADO Express/Delphi 5

6. Compact Access Database using ADO

7. Compress a access database using ADO

8. BDE, Access Autonumber Fields

9. Access AutoNumber type with Delphi 2.0 BDE?

10. trapping key violation error when inserting new record using SQL

11. Problem with using MEMO BLOB fields when creating a new record in INTERBASE

12. Using a Generator when creating a new record

 

 
Powered by phpBB® Forum Software