locked
primary key autonumber reset access 2013 webapp RRS feed

  • Question

  • I am doing trials on a WebApp therefore creating records, I need to clean the database and start fresh form 1 on the primary key, how can I achieve that?

    thanks

    Agustin

    • Changed type Kalman Toth Friday, April 4, 2014 8:40 PM
    Friday, April 4, 2014 3:43 PM

Answers

All replies

  • If it uses an IDENTITY property you can reseed it by using TRUNCATE TABLE or

    DBCC  CHECKIDENT (dbo.tblname, RESEED, 0);


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Friday, April 4, 2014 4:07 PM
  • Sorry, I am using Office 365 with the Access WebApp on sharepoint online, no code issued. The intent is just to do a one time reset on the database so I can deliver it "clean" to the Customer.

    Should I pack the App  and  reinstall it? or how can I do it?

    thanks for your response

    Friday, April 4, 2014 4:27 PM
  • Are you using MS access database ? This is SQL Server forum.

    Try one of the option below,

    1. Remove the field and recreate it.

    2. Delete all rows from the table and compact the acess database.


    Regards, RSingh

    Friday, April 4, 2014 5:08 PM
    Answerer
  • Yes, I am in Access, however the WebApp runs on SQL and regretfully the field I want to restart is the primary key, I can delete all the registers (done that) but the key does not return to 1 neither I have an option to reset it from Access, that is why I took the liberty to write on this fórum.

    thanks

    Friday, April 4, 2014 5:13 PM
  • What about deleting the column and recreating ? You can set it as Primary Key after you recreate.

    Did you try compacting the access database ?


    Regards, RSingh

    Saturday, April 5, 2014 5:22 AM
    Answerer
  • http://support.microsoft.com/kb/812718

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Saturday, April 5, 2014 8:37 AM
  • Thank you for your responses, I think I haven't explained properly.

    In access2013 when you open a web database lots of functionality dissapears in a big difference with Access 2010. One difference is that when you créate a table an autonumber field is created regardless off your design and it is the only autonumber field allowed, the autonumber type is no longer on the menú.

    There is no such thing as a create table query nor an append data query, no repair database command either. Those are still available on the desktop databases but not on the web database.

    Another huge difference is that the process has changed, in Access 2010 you create the database and the you publish it, in 2013 it is not possible to do that, you have to supply a valid web place to create the database so all editing takes place at the site itself.

    So far I didn't mind to recreate the tables because they were simple and small but the one I am making right now has several lookup fields and in order to get my customers approval I need to show them test data. at the end I need to delete the test data and start from ID=1. I would like to do that without having to rewrite the table hoping it will work.

    Thank you in advance for your comments, I look forward to see more of them.

    Agustin

    Saturday, April 5, 2014 10:42 PM
  • Hello,

    What Uri has posted is correct, you'll need to either truncate the table or delete the data and reset the seed. This is not an Access help area, but for SQL Server.

    Since you said it has a SQL Server backend that's what we're going to give you advice on. If you need Access specific help I doubt you'll find much of it here. I suggest you connect to SQL Server and take Uri's advice.


    Sean Gallardy | Blog | Twitter

    • Marked as answer by tracycai Sunday, April 13, 2014 5:40 AM
    Saturday, April 5, 2014 11:37 PM