Solved

HELP: How to make two Access databases identical

Posted on 1998-08-01
13
139 Views
Last Modified: 2010-05-03
Hello Gurus !

I should like to now if there is any simple way to make two Access databases identical withote changeing the data.

My problem is when i now develope I do databasesechanges in one database
and when I give my Beta-testers the new exe-file, I also want to update their database. I may have add Tables, fields and Questions....
Today I have made som code to compair two databases with eatch other, but it should simplyfy my work if I diden´t need to make this changes manually.

Sourcecode PLS...
--
/Janne, SWEDEN  janne.a@mailbox.swipnet.se
0
Comment
Question by:jannea
  • 7
  • 5
13 Comments
 
LVL 1

Expert Comment

by:DQ
ID: 1467415
What you can do is copy the original to the 2nd every time you exit your program (or update, or when ever)

Function fMAke2DBSame(theOrignal as string, the2ndCopy as String)as Boolean

'theOriginal is the name of your 1st DB file with path
'the2ndCopy is the name of the identical database with path

Dim Dummy as String

On error goto Noluck

Dummy = Dir(the2ndCopy, vbNormal)
If Dummy >"" then kill Dummy

FileCopy theOriginal, the2ndCopy
fMake2DBSame = True
Exit Function

NoLuck:

'Deal with error

End function




 
0
 
LVL 1

Author Comment

by:jannea
ID: 1467416
Thanks DQ for trying but:

The orginal DB dosen´t contain any data, I just want to get the same DB-structure as this one.
The database the Beta-tester got have got lots of data they still want to use so i can´t just owerwrite it.

I want some code to to make the Beta-DB to be identical to the original DB i have change.

/Janne


0
 
LVL 6

Accepted Solution

by:
anthonyc earned 100 total points
ID: 1467417
If you are just adding fields, it's not difficult.  When you begin deleting fields, it becomes hard.  To add acolumn, do this (assuming DB1 is the source, and DB2 is the beta test db)

public sub AddColumns
  dim tbl1 as tabledef
  dim tb12 as tabledef
  dim fld1 as field
  dim fld2 as field

  for each tbl1 in db1.tabledefs
     on local error resume next
     set tbl2 = db2.tabledefs(tbl1.name)
     if err > 0 then
        err = 0
        set tbl2 = db2.createtabledef(tbl1.name)
     end if

     for each fld1 in tbl1
        set fld2 = tbl2.fields(fld1.name)
        if err > 0 then
           err = 0
           set fld2 = tbl2.createfield(fld1.name, fld1.type, fld1.size)
           fld2.allowzerolength = fld1.allowzerolength
           fld2.required = fld2.required
           tbl2.fields.append fld2
           set fld2 = nothing
        end if
     next fld1

     db2.tabledefs.append tbl2 'add the table if we have to
  next tbl1
end sub


0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 6

Expert Comment

by:anthonyc
ID: 1467418
I made a couple bugs.. oops.  

Public Sub AddColumns()
    Dim tbl1 As TableDef
    Dim tbl2 As TableDef
    Dim fld1 As Field
    Dim fld2 As Field
    Dim idx1 As Index
    Dim idx2 As Index
 
    For Each tbl1 In db1.TableDefs
        If StrComp(Left$(tbl1.Name, 4), "MSYS", vbTextCompare) <> 0 Then
            On Local Error Resume Next
            Set tbl2 = db2.TableDefs(tbl1.Name)
            If Err > 0 Then
                Err = 0
                Set tbl2 = db2.CreateTableDef(tbl1.Name)
            End If

            For Each fld1 In tbl1.Fields
                Set fld2 = tbl2.Fields(fld1.Name)
                If Err > 0 Then
                    Err = 0
                    Set fld2 = tbl2.CreateField(fld1.Name, fld1.Type, fld1.Size)
                    fld2.AllowZeroLength = fld1.AllowZeroLength
                    fld2.Required = fld2.Required
                    tbl2.Fields.Append fld2
                    Set fld2 = Nothing
                End If
            Next fld1

            For Each idx1 In tbl1.Indexes
                Set idx2 = tbl2.Indexes(idx1.Name)
                If Err > 0 Then
                    Err = 0
                    Set idx2 = tbl2.CreateIndex(idx1.Name)
                End If

                For Each fld1 In idx1.Fields
                    Set fld2 = idx2.Fields(fld1.Name)
                    If Err > 0 Then
                        Err = 0
                        Set fld2 = idx2.CreateField(fld1.Name)
                        idx2.Fields.Append fld2
                    End If
                Next fld1
                idx2.Primary = idx1.Primary
                idx2.Required = idx1.Required
                idx2.Unique = idx1.Unique
                tbl2.Indexes.Append idx2
            Next idx1
            db2.TableDefs.Append tbl2 'add the table if we have to
        End If
    Next tbl1
End Sub


0
 
LVL 1

Author Comment

by:jannea
ID: 1467419
Thanks anthonyc, Youre closer but:

As I did Wrote above:  
I may have add Tables, fields and Questions....
So how aboute Questions and relations

/Janne
0
 
LVL 6

Expert Comment

by:anthonyc
ID: 1467420
QUESTIONS?  What in the world are questions?   and it does not say anything about relations.  You can do relations JUST as easily.  

Add these declares
dim rel1 as relation
dim rel2 as relation

add this code after NEXT TBL1

    For Each rel1 In db1.Relations
        db2.Relations.Delete rel1.Name
        Set rel2 = db1.CreateRelation(rel1.Name, rel1.Table, _
                                      rel1.ForeignTable, rel1.Attributes)
        db2.Relations.Append rel2
    Next rel1

0
 
LVL 1

Author Comment

by:jannea
ID: 1467421
anthonyc, and how to change field type/size if have changed ?

If fld2.Size <> fld1.Size Then ?

If fld2.Type <> fld1.Type Then ?

Can´t use Append property to change a existing field.
0
 
LVL 1

Author Comment

by:jannea
ID: 1467422
anthonyc,

1.) QUESTIONS?  What in the world are questions?:

I have got the Swedish version of Access so I don´t know the english name for it but:
If You open Access there is some tab´s there
Table, Question, Form, Report, Macro, Module
I ment the Questions I Have here in my database.
   
2.) and it does not say anything about relations.
Part of my question was:  I should like to now if there is any simple way to make two Access databases 'identical' withote changeing the data.
And I guess this things also have to be done before I get the databases IDENTICAL.

Thanks so far...
0
 
LVL 6

Expert Comment

by:anthonyc
ID: 1467423
QUERY!  Questions are queries.. Ok that is easy!!!

Relations are not necessary to make a DB identical...

Ok for queries... add this to the top

dim qry1 as querydef
dim qry2 as querydef

after NEXT REL1

for each qry1 in db1.querydefs
    db2.querydefs.delete qry1.name
    set qry2 = db2.createquerydef(qry1.name, qry1.sql)
next qry1
0
 
LVL 1

Author Comment

by:jannea
ID: 1467424
anthonyc,

1.) Relations are not necessary to make a DB identical...

Can´t get your Relationscode to work properly, and I need this to make the .exe work properly.

After the line db2.Relations.Append rel2
I get the error: Can't append a relation with no fields defined.

I have tryed with this 2 following lines before the line db2.Relations.Append rel2

rel2.Fields.Append rel2.CreateField(rel1.Name)
rel2.Fields(rel1.Name).ForeignName = rel1.ForeignTable

But then I get the error: Invalid field definition 'Faktura_Rader' in definition of index or relationship.

Do you know why, and how to solve this ?

2.) Aboute my 1:st comment:

How to change field type/size if changed ?

If fld2.Size <> fld1.Size Then ?

If fld2.Type <> fld1.Type Then ?

Can´t use Append property to change a existing field.


Thanks again, Janne...
0
 
LVL 1

Author Comment

by:jannea
ID: 1467425
anthonyc,  are you still there ?

I have solve the problem with relations my self so now I just have this last problem before my code is completed.

How to change field type/size ?

If fld2.Size <> fld1.Size Then ?

If fld2.Type <> fld1.Type Then ?

Can´t use Append property to change a existing field.

Any ideas ?

 /Janne
0
 
LVL 6

Expert Comment

by:anthonyc
ID: 1467426
I solved relations for you.  I am not sure you can modify field sizes without deleting the field.  This is what you do to do that.

Do this AT THE VERY END AFTER EVERYTHING ELSE!

for tbl1 in db1.tabledefs
for fld1 in tbl1.fields
   set fld2 = tbl2.fields(fld1.name)
   if not fld2 is nothing then
      if fld2.size <> fld1.size then
         set fld3 = tbl2.createfield(fld1.name & "_temp", fld1.type, fld1.size)
         tbl2.fields.append fld3
         szSQL = "update " & tbl2.name & " set " & fld3.name & " = " & fld2.name
         db2.execute szSQL
         tbl2.fields.delete fld2.name
         set fld2 = tbl2.createfield(fld1.name & fld1.type, fld1.size)
         fld2.required = fld1.required
         if fld2.type = dbText then
            fld2.allowzerolength = fld1.allowzerolength
         end if
         tbl2.fields.append fld2
         szsql = "update " & tbl2.name & " set " & fld2.name & " = " & fld3.name
         tbl2.fields.delete fld3.name
      end if
   end if
next fld1
next tbl1
0
 
LVL 1

Author Comment

by:jannea
ID: 1467427
Thanks...
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Excel object stays open 19 73
using Access 8 68
bit defender blocks good applications 2 93
Determine Range to Select 5 48
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now