Can Delphi Use Access Autoincrement?

Hello,

I am accessing an Access 2000 database and was wondering how I would go about utilizing the Auto-incremment field.  When I insert a record, the required field checks are made, however, Delphi allows me to leave the Auto-Increment field (which happens to be the primary key) blank and does not assign a new one number.  If I can't have BDE/Access generate a new ID each time a record is inserted, I'll have to waste time using Visual Basic to build this program.  

One note for any perspective answer-er.  This MDB file will be used in a multi-user environment and already has 65,000 records - so answers suggesting background queries or first/end loops would be extremely slow.

Any help or comments are appreciated as I really need this resolved as soon as possible.

Thanks in Advance.
LVL 3
d4jaj1Asked:
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.

karooCommented:
hi,

Is your PK Autoincremented after a succesful post?

Regards Ben.
0
karooCommented:
d4,

Just tested a small app with an Access AutoIncrementing PK - works fine.

Delphi will leave your PK empty on the insert of a new record, also the BDE does not generate it.
Access will generate a new key the moment a record is successfully posted, only then will you see the new key on the client side.

Are you using cached updates?
0
kretzschmarCommented:
hmm d4jaj1,

thats the feature of autoincremented fields, it is filled by the databasesystem, delphi or any other prog-language have nothing to do with it.

well, if u use an odbc-driver, the bde will lost the contact to the new inserted record, you have after-post to locate the record again, therefore i would recommend to use the native access-drivers. the native access-driver shows after post the given autoinc-number and the bde will not loose the record.

meikl

hi karoo ;-)
0
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

d4jaj1Author Commented:
Unfortunately, the database my users will be connecting to is an Access 2000 database, so I'm confused as to if the native 3.5 driver can connect to the datasource.  

While I was looking around the Database Administrator, I noticed some things that confused me even further.  Many of the drivers in the Native and ODBC in the BDE aren't listed as options in my query component.  Infact, it only lists the default ones installed with Delphi and all of the ones in teh User DSN tab in the Control Panel.  The Native Access driver isn't even an option - only the 2 ODBC Access drivers?????  Also, even if I get teh BDE native Access driver in the list, can it connect to Access 2000 tables?

What's going on?  I'm Using D4.003.  As for your question karoo, The record was posted okay - but without a primary key. No, I'm not using Cached Updates.

Is there a way to get this to work with with Access 2000?  Please Help!!!
0
karooCommented:
d4,

>>Many of the drivers in the Native and ODBC in the
>>BDE aren't listed as options in my query component

If you are talking about the driver's under the Configuration tab in the BDE:
They won't be listed as aliases, you have to add an Alias for your database under the Database tab in the BDE. (Would also be a good idea to use a TDatabase component to connect to your database instead of directly connecting with the Query)

>>can it connect to Access 2000 tables
yes

d4, if you open your DB in Aceess and add a record, does the outonumber PK work correctly?

hi meikl:) lots of Access probs eh?
0
d4jaj1Author Commented:
Yep, works fine in Access itself, just not when connecting via Delphi.  After I posted my question, I added the the native access driver via the Database tab in BDE Adminsitrator.  When I tried to connect to teh Access 2000 table, I got an error "Unrecognized Database Format".  Then I attempted to connect to an eariler version of the same database (Access 95 I think), it connected okay.

So, the only way I can see connecting to the Access 2000 table - is via the ODBC driver.  Are you guys saying teh Auto-Increment won't work if the ODBC driver is used?
0
karooCommented:
>>Are you guys saying teh Auto-Increment
>>won't work if the ODBC driver is used
it should still work, i'll try it out
i've tested autoincrementing with the native driver and it works, native will also be the way to go because ODBC adds overhead and limits one somewhat.

follow:
http://www.experts-exchange.com/jsp/qShow.jsp?ta=delphi&qid=10231517 
we are discussing the connection problems with Access 2000 there, i've already posted a couple of links to pages that will provide help you your with configuration.
0
karooCommented:
posted a couple of links to pages that will help you with your with configuration.

sorry:)
0
kretzschmarCommented:
hi  karoo,
i never said that the autoinc-field not work with access-odbc,
i said the bde looses the contact to the record.

try self
in a dbgrid
insert a new record
post the record
edit the record again
post the record
an rror message comes up and
tells you that the record is deleted by another user

you must refresh the dataset and relocate the record
before you edit this record again

by use of the native-driver don't need this step

this happens to me with bde 4.0,
maybe a higher bde may handle this now
or the odbc-driver will be better now
(not rechecked)

meikl ;-)
0
karooCommented:
sorry meikl, i didn't imply that what you said was wrong, i apologize if it came out in that way.

tried it and i agree, you have to refresh the dataset with ODBC to get the correct values.
0
kretzschmarCommented:
hi karoo,
not at all, was just for clarify,  for d4jaj1 also
>lots of Access probs eh?
no, its not my primary database

well, maybe i try tomorrow
to configure a native connection
to access 2000
(this references also the other q)

good night

meikl ;-)
0
karooCommented:
>>lots of Access probs eh?
oh,
what i meant by that statement is that there is suddenly a lot of q's on Access problems in EE. came out the wrong way.
0
kretzschmarCommented:
hi,

i'm back again, and have done some tests. i was not able to configure a native connection to access2000 with the bde. it works with odbc-driver, also with ado through odbc-driver.

so we also have to wait, until borland will provide a native-sql-link to access2000.

well, in the meantime, and if it from interest to invest $120, then i would prefer this bde-replacement:

http://www.cybermagic.co.nz/winsoft/dao.htm

download the trial and test it
(easy to use and fast)

http://www.cybermagic.co.nz/winsoft/download/daods36.zip

meikl ;-)
0
kretzschmarCommented:
whats going on ?
0
kretzschmarCommented:
hello?
0
d4jaj1Author Commented:
Apparently, it does work - it just doesn't display until I move off of the record and come back.  If I try to call:

query1.refresh;  //Live Result is true

I get an error stating the query is not unquiely indexed - BUT if I use the DBNavagator component's Refreash, it works fine.  Is there another way to refresh the view?
0
kretzschmarCommented:
hi d4jaj1,

close,
open,
locate

meikl
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
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.