Solved

HELP: How to make two Access databases identical

Posted on 1998-08-01
13
137 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
 
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
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 process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

746 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

10 Experts available now in Live!

Get 1:1 Help Now