Page 1 of 1

Microsoft Access key field

Posted: Sun Dec 12, 2010 12:34 pm
by Charles L. Cotton
Is there a way to reset an Access auto-generated primary key field to "0?" While setting up a database and doing testing, the primary key field has advanced and I need it reset, if at all possible.

Thanks,
Chas.

Re: Microsoft Access key field

Posted: Sun Dec 12, 2010 1:27 pm
by dalto
Here is the procedure for resetting and autonumber field in access: http://support.microsoft.com/kb/812718" onclick="window.open(this.href);return false;

Re: Microsoft Access key field

Posted: Sun Dec 12, 2010 1:38 pm
by suthdj
I thought you could just do a TRUNCATE TABLE "tablename" in a SQL window.

Re: Microsoft Access key field

Posted: Sun Dec 12, 2010 3:26 pm
by dalto
suthdj wrote:I thought you could just do a TRUNCATE TABLE "tablename" in a SQL window.
That will delete all the data in the table.

Re: Microsoft Access key field

Posted: Sun Dec 12, 2010 5:46 pm
by OldSchool
dalto wrote:
suthdj wrote:I thought you could just do a TRUNCATE TABLE "tablename" in a SQL window.
That will delete all the data in the table.
Yes, one has to be careful with SQL calls to MS Access, since it uses non-standard SQL (MS has done this kind of thing regularly) -- one of the reasons I dumped it years ago. Access is actually only a front-end to a non-standard back-end file.

Re: Microsoft Access key field

Posted: Sun Dec 12, 2010 5:59 pm
by pbwalker
OldSchool wrote:
dalto wrote:
suthdj wrote:I thought you could just do a TRUNCATE TABLE "tablename" in a SQL window.
That will delete all the data in the table.
Yes, one has to be careful with SQL calls to MS Access, since it uses non-standard SQL (MS has done this kind of thing regularly) -- one of the reasons I dumped it years ago. Access is actually only a front-end to a non-standard back-end file.
:iagree:

Be very careful with TSQL statement in Access. It uses the JET DB engine and even with literals and like strings, the syntax is different.

Charles - Some of the previous links cover it, but just wanted to throw my vote in for SQL Express. It's free and very easy to use. Might want to give that a look. :tiphat:

Re: Microsoft Access key field

Posted: Sun Dec 12, 2010 8:03 pm
by OldSchool
pbwalker wrote:
OldSchool wrote:
dalto wrote:
suthdj wrote:I thought you could just do a TRUNCATE TABLE "tablename" in a SQL window.
That will delete all the data in the table.
Yes, one has to be careful with SQL calls to MS Access, since it uses non-standard SQL (MS has done this kind of thing regularly) -- one of the reasons I dumped it years ago. Access is actually only a front-end to a non-standard back-end file.
:iagree:

Be very careful with TSQL statement in Access. It uses the JET DB engine and even with literals and like strings, the syntax is different.

Charles - Some of the previous links cover it, but just wanted to throw my vote in for SQL Express. It's free and very easy to use. Might want to give that a look. :tiphat:
True, it's never too soon to plan on converting to another database, even if you think it's years away. Anything can happen to the database engine you're using now, and Access has many built-in limitations that eventually show up.

Just recently made a project of converting a large MS Access database (that I inherited) because Access was dying due to the file size (6MB, even though it's advertised to handle hundreds of times larger than that), and our IT group had decided to "upgrade" to the new Office version (which is always a disaster, and never entirely backwards compatible).

Decided on SQLite, since that also uses a single-file database (very portable), is free (no seat licenses), and very reliable (this was an important database for my work).
ETA: Oh, yeah, almost forgot: It uses standard SQL with some extensions.
Ended up, after doing significant research, that the easiest way to do it was to convert the Access file to MS Excel, then to CSV, then imported into SQLite using one of the many tools out there. While the way I did it took a long time to complete, I am thoroughly pleased with the results (which I use every day)!

Re: Microsoft Access key field

Posted: Sun Dec 12, 2010 10:12 pm
by Charles L. Cotton
Thanks for the input guys. My web host includes MySQL; is that an option as well?

Chas.

Re: Microsoft Access key field

Posted: Sun Dec 12, 2010 10:52 pm
by dalto
Charles L. Cotton wrote:Thanks for the input guys. My web host includes MySQL; is that an option as well?

Chas.
Absolutely, but one thing to consider is how you are using access. Most of the things mentioned including mysql are database backends. If you are using Access to develop forms or software you would need to find another way to do that.

It really depends what you are trying to accomplish and what skills and time you have to bring to the table.

Re: Microsoft Access key field

Posted: Sun Dec 12, 2010 11:02 pm
by pbwalker
Charles L. Cotton wrote:Thanks for the input guys. My web host includes MySQL; is that an option as well?

Chas.
100%. It's my favorite platform, hands down! (I only recommended SQL Express because it's MS and I figured there would be some migration tool)

MySQL provides great documentation, how-to's, and wikis. It was an easy transition for me (from SQL 2000).

http://dev.mysql.com/tech-resources/art ... osoft.html

Dalto is right though...MySQL can do a lot of stuff, but it is really a command line DB. It all depends on what you need. There are front end GUI's, but I haven't used any to make a recommendation. If you are using a lot of the GUI builders in MS Access, you may find MySQL to be too much of a hassle. Then again, there may be some nice open source tools that can do this...

Re: Microsoft Access key field

Posted: Mon Dec 13, 2010 3:00 pm
by suthdj
dalto wrote:
suthdj wrote:I thought you could just do a TRUNCATE TABLE "tablename" in a SQL window.
That will delete all the data in the table.
Yes but since it is test data it should not matter. If it is not test data then resetting could lead to other issues.

Re: Microsoft Access key field

Posted: Tue Dec 14, 2010 12:07 am
by Charles L. Cotton
suthdj wrote:
dalto wrote:
suthdj wrote:I thought you could just do a TRUNCATE TABLE "tablename" in a SQL window.
That will delete all the data in the table.
Yes but since it is test data it should not matter. If it is not test data then resetting could lead to other issues.
One of the tables contains only test data, but others have live data and a lot of queries and forums have been created. If I can delete all the test data in one table and reset the auto number at the same time, then that's fine.

Thanks,
Chas.

Re: Microsoft Access key field

Posted: Tue Dec 14, 2010 11:04 pm
by suthdj
Charles L. Cotton wrote:
suthdj wrote:
dalto wrote:
suthdj wrote:I thought you could just do a TRUNCATE TABLE "tablename" in a SQL window.
That will delete all the data in the table.
Yes but since it is test data it should not matter. If it is not test data then resetting could lead to other issues.
One of the tables contains only test data, but others have live data and a lot of queries and forums have been created. If I can delete all the test data in one table and reset the auto number at the same time, then that's fine.

Thanks,
Chas.
Like dalto said it will wipe out the data in that table and reset the auto number, however if any other tables is referencing the data in that table then you might get some broken records. It all depends on how it is all connected.