TexasCHLforum.com

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.

Here is the procedure for resetting and autonumber field in access: http://support.microsoft.com/kb/812718" onclick="window.open(this.href);return false;

I thought you could just do a TRUNCATE TABLE "tablename" in a SQL window.

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.

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.

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.

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.

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.

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.

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)!

Thanks for the input guys. My web host includes MySQL; is that an option as well?

Chas.

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.

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

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.

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.

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.