Results 1 to 9 of 9
  1. #1
    Brightspark98 is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2016
    Posts
    57

    Refresh Database

    Hi,

    I have been developing a database, (with a lot of assistance from you guys!), and have test data in the tables.

    Is there a way to reset all tables & auto numbers?



    Also If I wanted to use the same db year on year, would it be better to create a copy, wipe all data and rename? Or is there a better way to accomplish this?

    Many Thanks

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you must empty the table
    compact db
    now autonum starts at 1
    OR
    have an empty table that is never used, (mine is tTable_MT)
    simply copy tTable_MT and overwrite tTable
    autonum starts at 1

  3. #3
    Brightspark98 is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2016
    Posts
    57
    Thanks for that.

    For subsequent years

    Would you use a copy of the database and change the name db2017 to db2018 for example?

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    simply copy tTable_MT and overwrite tTable
    but don't copy the ID, just all the other fields

  5. #5
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Depends,
    some I make a new copy and rename 2017
    some I keep every year in the same db,same table,to query across all years.

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Is there a way to reset all tables & auto numbers?
    To clear the tables, I write a sub that is basically "DELETE * FROM TblName" for each table I want to delete all records.
    If you want to clear all tables, you can loop through the tables collection deleting the records as above.

    To "reset" "AutoNumbers", do a "Compact and Repair".



    Would you use a copy of the database and change the name db2017 to db2018 for example?
    I am curious as to why you would do this?
    I don't......I have a dB that has 10 years of data in it. When looking at form or reports, a year, a year range, month/year or a date range (depending on the report) can be selected/specified to display/report the required data. But the table structures must be designed correctly.

  7. #7
    Brightspark98 is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2016
    Posts
    57
    Quote Originally Posted by ssanfu View Post
    I am curious as to why you would do this?
    I am totally new to databases and was looking for advice. I thought archiving each year would be the way to go to save space - I currently use excel for storing data and the size of the spreadsheet would become unmanageable if it was added to each year.

    Thanks

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Excel and Access are two different animals (object models).

    If you are familiar with Excel, when you work with Access, you pretty much have to forget whatever you know about Excel.
    Designing a spreadsheet is VERY different than designing tables and relationships.

    You might want to see the attached text file. After reading the sites, begin with paper and pencil, whiteboard, cardboard, etc designing tables. Try manually adding data to see how the tables work. Then (and only then) start Access and create the tables.
    Attached Files Attached Files

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    if creating a new db, rather than copy and pasting the file, create a new db and import all the required objects from the old db - ensuring you have selected Definition Only for tables in options

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 05-03-2014, 08:53 AM
  2. How to refresh a DB?
    By adacpt in forum Programming
    Replies: 1
    Last Post: 12-02-2012, 02:45 PM
  3. Refresh All
    By gg80 in forum Programming
    Replies: 3
    Last Post: 01-12-2012, 03:39 PM
  4. Access 2010 Refresh VS Refresh ALL
    By Snwboarder1982 in forum Access
    Replies: 1
    Last Post: 09-09-2011, 04:07 PM
  5. Refresh Form
    By tpcervelo in forum Forms
    Replies: 7
    Last Post: 02-07-2011, 08:03 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums