• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 381
  • Last Modified:

Making New Access Database from Old One

Hi,

I have an Access database that I created for a survey in 2010 with data already entered.  My company has decided to make some changes to the 2011 survey, so I need to make those changes in the database, including tables and forms. The main changes are the addition and deletion of a few questions and changes to some of the question's response options.

My question is: what is the best most efficient way to create the new database? Should I copy all the forms and tables over to a new database or should I delete the old data in the 2010 database and then make changes?  

thanks,
carlynne
0
Carlynne
Asked:
Carlynne
  • 6
  • 5
  • 2
  • +1
3 Solutions
 
Gustav BrockCIOCommented:
Make a simple file copy of the database.
In the copy, delete all survey data from the tables.
Change any reference (query, filter, code) to year 2010 to 2011.

/gustav
0
 
CarlynneAuthor Commented:
Hi /Gustav,

Thanks for your response.

 I have done as you suggested but the IDs are not starting over from 1. How do I get the IDs to start over, rather than starting off where the last record in the old database left off? I want to start with a clean slate.

Also, when I update the responses in the tables, Access doesn't seem to update automatically in the forms. Do I have to update forms separately? What a pain! Urgh.

Thanks,

carlynne
0
 
Gustav BrockCIOCommented:
IDs are for uniqueness only and should be meaningless and not displayed for the user as you cannot (and should not) guarantee a sequence. This is a very fundamental design of relational databases.

Yes, if you change data in table that is a source for a form, you may need to refresh/requery the form to reflect the changes.

/gustav
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
hnasrCommented:
"I want to start with a clean slate"
Copy table and paste to new table "Structure only"
Then append the data to the new table. Autonumber starts from 1.

VBA can be used to loop through table.
Hold table names in a dynamic array
Rename table  to old_table
Create a new table structure from old_table

You may append required data.

Here is the code to rename tables, create table structures with the original names.
Try the code from a button click event, using a copy of the database.


Private Sub Command6_Click()
    Dim t As String ' to hold current table name
    Dim tr As String ' to hold renamed table name
    Dim i As Integer 'integer to loop through tabledefs
    
    Dim tarray() As String ' Dynamic array to hold current table names
    Dim j As Integer 'integer to use for tarray elements
    j = -1
    DoCmd.SetWarnings False
    For i = 0 To CurrentDb.TableDefs.Count - 1 ' fill tarray
        If Left(CurrentDb.TableDefs(i).Name, 4) = "MSys" Then
        Else
            j = j + 1
            ReDim Preserve tarray(j)
            tarray(j) = CurrentDb.TableDefs(i).Name
        End If
    Next
    For j = 0 To UBound(tarray) 'use tarray to rename and recreate table structure
        t = tarray(j)
        tr = "Old_" & t
        DoCmd.Rename tr, acTable, t
        DoCmd.RunSQL "Select * into " & t & " from " & tr & " where false"
    Next
    DoCmd.SetWarnings True
End Sub

Open in new window

0
 
hnasrCommented:
You may expand the code to delete the old tables.
Warning: alway have an extra backup before trying new comments.
0
 
GRayLCommented:
If after you delete the data, you do a Compact and Repair, autonumber fields will reset to 1.
0
 
CarlynneAuthor Commented:
GRayL,

How do I do a "compact and repair?"

carlynne
0
 
GRayLCommented:
Click Tools, Database Utilities,  Compact & Repair.
0
 
CarlynneAuthor Commented:
GRayL,

Hmmm, I don't have a Tools in my menu bar. I have Home, Create, External Data, Database Tools, Add-Ins. Do I need to add a tool's tool bar?

Sorry.

carlynnne
0
 
GRayLCommented:
Click on Database tools - is it there?  You must be using A2007 or A2010
0
 
CarlynneAuthor Commented:
Yep,I'm using 2007.

carlynne
0
 
GRayLCommented:
Were you able to get to Compact & Repair?
0
 
CarlynneAuthor Commented:
thanks for everyone's input.
0
 
GRayLCommented:
it would sure be nice to know what worked!
0
 
CarlynneAuthor Commented:
GRayL,

No, I couldn't get it to work.

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 6
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now