Inserting in Access-tables ...

Hi folks,

I'm using Delphi 5 + an Ms-Access97 database.
In the database there are several tables.
Every table has a unique key (KeyField) of type AUTONUMBER.
Between Table1 and Table2 there is a one-to-many relation.


Table1                   Table2
------                   ------
KeyField    1 --|        KeyField
DataField1      |-- ...  DataField1
DataField2               DataField2
DataField3               DataField3
...                      ...


I my application I use TTable-components.

This is what I have to do :
 1) Insert a record in Table1 (unique key = autonumber-field)
 2) Insert a record in Table2 (unique key = autonumber-field)
    Before the Insertion in Table2 I have to fill in the value
    of the KeyField (autonumber) of Table1


I have the following problem :
  1) Works Fine;
  2) when I'm trying to retrieve the value of the autonumber-field of Table1
     I get the value '0', so I can't insert a record in Table2.


Is there a way to solve my problem ?

Thanx,

The Mayor ;-)
LVL 6
wimmeyvaertAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rwilson032697Commented:
Are you attempting to retrieve the value of the field after you post the record to table1?

Cheers,
Raymond.
0
wimmeyvaertAuthor Commented:
Hello rwilson,


Yes, I do.

Hereby I send a code-snippet of the procedure iw which I insert a record in the first table, and then insert a record in the second table.

{ Insert record in table1 }
With DataModule.Table1 do
begin
  Insert;
  FieldByName('Field1Table1').AsInteger := lngData1;
  FieldByName('Field2Table1').AsInteger := lngData1;
  Post;
  {Retrieve the value of the autonumber-field }  
  lngField1Table2 := FieldByName('KeyFieldTable1').AsInteger;
end;

{ Insert record in table2 }
With Datamodule.Table2 do
begin
  Insert;
  FieldByName('Field1Table2').AsInteger := lngField1Table2
  Post;
end;


If I put a breakpoint and look at the value of 'lngField1Table2' then the value is 0.


Hope I give you enough information to give me an answer.

Best regards,
The Mayor.
0
bozo7Commented:
In the BDE 5.10 there was a bug that it could not read AutoNumber fields correctly. In my experience the fields would be given a auto incrment number that was way out of sequence.
I would not use an autoincement fields with Access tables. I found the best way to do it was in code.
We used a seperate table that held a field for the next number. Then on insert of the main table grab that number then increment it and edit the number as the incremented number.

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

wimmeyvaertAuthor Commented:
Hello Bozo7,

Isn't there really another way to solve my problem? I've written pretty much code so far that it will take me a lot of time/work to adjust the code.

Altought, I think your answer isn't that bad. We use the same method at work in our Delphi applications working with SQL-Server.

So, when I have to write another program that works with Ms-Access, than I'll consider using your method.

But first I want to be sure there is no other solution to my problem.

Therefor, I will wait a couple of days for another answer, but if there is nobody who can give me a solution, than I'll accept your answer.

Cheers,

The Mayor.
0
kretzschmarCommented:
hi all,

i guess, you use odbc to access access2000.

first to bozo: there is no bug

the bde is loosing the contact to the new inserted record if the primary key is an autoincrement Field (happens also on access97 and lower versions and on bde 5.01 and lower versions).

it is so:

two workarounds:
-forget the autoincrement-field and do self numbering.
-get the comercial bde replacement diamond access at http://www.islamov.com/ 
get there the components for dao3.6, trial is there also available.

meikl

0
bozo7Commented:
Maybe the word bug was the wrong word. But it doesn't work properly(which could be interpreted as a bug).
0
OpitzSCommented:
Why don't you search for the correct dataset in table 1 after posting, or is the ID really the only field with unique values?

Sven
0
wimmeyvaertAuthor Commented:
Hello OpitzS,

Table1 has an ID-field which is the unique key (autonumber). So, after posting the record, I should be able to retrieve the value of the ID-field of Table1 and then use it for later use to post a record in Table2.

And thats where te problem is. After posting the record in Table1, I can't retrieve the value of the ID-Field. The value is than always '0'.



The Mayor.
0
OpitzSCommented:
No, I mean reset the cursor to the right dataset, when you look for the ID-Value.

Sven
0
bozo7Commented:
If you are using D5 why not try the ADO Objects.
That could be nicer than the BDE. I have not used them myself yet. I'm sure I will in the near future, though.

Bozo
0
OpitzSCommented:
Because ADO is only Enterprise version, or buying it for professional version?

Sven
0
wimmeyvaertAuthor Commented:
Hello Bozo7,

Apparently there doesn't seem to be a solution for my problem, so I'm going to give the points to you (because you were the first to put a comment + you gave me an alternative way of working).

Cheers,

The Mayor.

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.