MS Access Auto incrementing Key fields 
Author Message
 MS Access Auto incrementing Key fields

How can you use access tables that have an auto-incrementing key (via ODBC).

When I try to add a record I get an error that means the Key is
invalid. It does not appear to increment before the post !

Any suggestions



Thu, 28 Jan 1999 03:00:00 GMT  
 MS Access Auto incrementing Key fields

Delphi 2.0 doesn't support auto-increment fields.

See ya...
Joe Healy - Tampa / St Pete FL


Quote:

>How can you use access tables that have an auto-incrementing key (via ODBC).

>When I try to add a record I get an error that means the Key is
>invalid. It does not appear to increment before the post !

>Any suggestions

--
Joe Healy - Tampa / St Petersburg FL


Sat, 30 Jan 1999 03:00:00 GMT  
 MS Access Auto incrementing Key fields

: Delphi 2.0 doesn't support auto-increment fields.

WRONG, I've done it and it works.
(Besides: You should read the help-files, there is a new field type
TAutoInc or so)

I created a table with Access 7.0 with an autoincrement field, set up the
BDE and tried to open a table with Delphi.
When you open the field editor, you should first add all the fields you want
and then have a look at the field type Delphi assigns to the autoinc field.
It's a LongInt, that's shurely wrong but here comes the workaround:
Delete the Field and the click on the right button and then open something
like "create new field" or so (I've got the german version so I don't know
the entry for the english version) and a dialog will be opened.
Now enter the field name and Delphi instantly looks up the name and the
type LongInt will show up. Change it to TAutoInc and apply with 'OK'.
That's all. Now you have a AutoInc Field like you wanted to have one.

Note: As you create a new record, you will not be given the autoinc-value so
if you really want to know it (e.g. for references to this new record) you
should first call AppendRecord, Post it, Refresh, read the value and the
call Edit.

Hope this helped you

oli



Sat, 30 Jan 1999 03:00:00 GMT  
 MS Access Auto incrementing Key fields

You should, though, be aware that such TAutoIncs cannot be used with
master/detail linking (at least in Delphi 2.00). This is a VCL problem.

peo



Quote:

> : Delphi 2.0 doesn't support auto-increment fields.

> WRONG, I've done it and it works.
> (Besides: You should read the help-files, there is a new field type
> TAutoInc or so)



Sun, 31 Jan 1999 03:00:00 GMT  
 MS Access Auto incrementing Key fields

Set the field in the MS Access table to type COUNTER.   When you append a
record  using the "With Ttable1 do   FieldByName ... post" do not add or
reference a value for the counter field.  Access automatically updates that
for you.  You can do the same type of thing with dates that are storing the
transaction datetime by setting the default in the Access table to Now and
not posting anything for that field with the new record.



Quote:
> How can you use access tables that have an auto-incrementing key (via
ODBC).

> When I try to add a record I get an error that means the Key is
> invalid. It does not appear to increment before the post !

> Any suggestions



Mon, 01 Feb 1999 03:00:00 GMT  
 MS Access Auto incrementing Key fields

Quote:

> How can you use access tables that have an auto-incrementing key (via ODBC).

> When I try to add a record I get an error that means the Key is
> invalid. It does not appear to increment before the post !

> Any suggestions

The problem I have seen, I believe you are having is this:  THe BDE
posts the record - then reads it back in and compares the 2 records.
If they match, then the BDE is happy - if they don't then the BDE
figures the record didn't post correctly.  So when Access adds the
increment value on the post, the record fails to match.  This can
easily be checked: does the record show up in the database?  If its
there - then it posted fine and we can work around the error.  If the
record is not there (assume no transactions or the like) then something
else strange is happening.

I posted this same(almost) code snipet on another thread today.  The
code here will{*filter*}out that error (For any reason) - you could add
schemes
to only{*filter*}it out when you want it to...

Good luck with it
Bill Fisher

  TForm1 = class(TForm)
    DBGrid1: TDBGrid;
    DataSource1: TDataSource;
    Table1: TTable;
    DBNavigator1: TDBNavigator;
    DivByZero: TButton;
    procedure FormCreate(Sender: TObject);
    procedure DivByZeroClick(Sender: TObject);
  private
    { Private declarations }
  public
    procedure AppException(Sender: TObject; E: Exception);
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.DFM}
procedure TForm1.AppException(Sender: TObject; E: Exception);
var
  i : integer;
begin
 {E.message must match exactly to the message in the Exception
  dialog box}

 if E is EDBEngineError then begin
    if E.message = 'Key violation' then
             ; //{*filter*}it out!
 end else
   MessageDlg(E.Message, mtError, [mbOk], 0);
end;

procedure TForm1.FormCreate(Sender: TObject);
begin
    Application.OnException := AppException;
end;

procedure TForm1.DivByZeroClick(Sender: TObject);
var
 a,b : integer;
begin
  b := 0;
  caption := floattostr(a/b);
end;

end.



Tue, 02 Feb 1999 03:00:00 GMT  
 MS Access Auto incrementing Key fields

Quote:

>How can you use access tables that have an auto-incrementing key (via ODBC).
>When I try to add a record I get an error that means the Key is
>invalid. It does not appear to increment before the post !

I have been having may difficulties using TAutoInc with Paradox files
also.  How are you trying to add the record?  Only way I have been
able to add record was to use TTable with insert and post.  Insert SQL
will not work.  I believe, I could be wrong,  that usually Autoinc
field is the primary key and is required so BDE doesn't even try to
insert since it knows that it is required and there is no data.

I did not know that Autoinc fields were not supported very well in
Delphi so I started my project using it extensively for primary key.
I am now slowly moving away from it but it is hard to change the data
structure on a program that is already deployed.

I wished Borland either supported autoinc fields better or document
somewhere not to use autoinc fields.

Good luck.
-Jay



Fri, 05 Feb 1999 03:00:00 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. Auto-increment field in MS SQL-Server 6.x and DBGrid

2. How to return auto-increment key??

3. Need Help With Auto-Increment Primary Key Errors

4. Auto-Increment Gaps in MS-SQL?

5. Auto increment field

6. Master-detail and auto-increment fields

7. Need help defining auto-incrementing field using TTable.Create

8. Auto Increment Fields and Master Details Relationship

9. Question about auto increment field

10. Delphi 2 SQL Server 6 problem with identifier (auto-increment) fields

11. Auto-increment fields - Should I Use Them?

12. Obtaining value of Paradox's Auto-Incremented Field Type

 

 
Powered by phpBB® Forum Software