Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

copying a database

Posted on 2003-03-24
8
Medium Priority
?
147 Views
Last Modified: 2010-04-07
I have a database, but the tables are views.
So I want to create a destination database which has all the records which my source database has, except that the records should not be views, but should be actual records.

I know that the logic should not be all that hard, but I don't know exactly how to PROGRAMATICALLY create a database and tables with the exact same information as in the source.  Relationships are not importnant.  We can assume that the destination database will have no relationships defined....

ado is prefreble, but dao is acceptable...
0
Comment
Question by:thanks_wise1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 4

Expert Comment

by:P1
ID: 8197491
If the Front End is Access App, this will do.

DoCmd.CopyObject "Other.MDB","MyNewTableName",acTable ,"MyOldTableName"

Regards,  P1
0
 

Author Comment

by:thanks_wise1
ID: 8197532
Dear P1,

I am using Access, yes.
I'm not sure I follow your comments though.
What is DoCmd declared as?
"Other.MDB" -- is this the path to my newly programatically created database?

Which parameter houses the Source database?

What is acTable suppose to be?

Thanks,
km
0
 
LVL 4

Expert Comment

by:P1
ID: 8197676
I am using Access, yes.
I'm not sure I follow your comments though.
What is DoCmd declared as?

DoCmd is a VBA syntax to Execute Access Commands.  You put this into the VBA code in a module, that will execute the maintenance that you schedule.

"Other.MDB" -- is this the path to my newly programatically created database?

Yes,DrivePathFile ( "C:\My Documents\Other.MDB" ) or
    UNC ( "\\MyServerName\MyDirectory\Other.MDB" will work.
I can not remember, if it will do a create & copy.  I know that it works on existing databases.  BTW, I cheated for speed on several apps, create a blank database and use VBA file commands ( copy source to dest. ), it's easier and you get a speed boost.

Which parameter houses the Source database?
The Current Database from which the command is executing from is always assume as source.

What is acTable suppose to be?

This let's DoCmd what kind of object it's copying.  Just in case you have a table = "this" & query = "this" & report = "this".

Regards,  P1
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:thanks_wise1
ID: 8198644
I'm looking for a solution using vb code.

Here is the code I have so far, and what I have is SUPPOSE TO copy the structure, but it doesn't do that:


Assuming we have a form, and lblSrcFileName.Caption contains the source database path, and lblDestFileName.Caption contains the destination path:

Private Sub cmdDuplicate_Click()
   
    Dim tbl As TableDef
    Dim tblDest As TableDef
   
   
    If lblSrcFileName.Caption <> "" And lblDestFileName.Caption <> "" Then
        Set dbSrc = OpenDatabase(lblSrcFileName.Caption, False, False, ";PWD=Freddie")
       
        Set dbDest = CreateDatabase(lblDestFileName.Caption, dbLangGeneral)
       
        Set tbls = dbSrc.TableDefs
        For Each tbl In tbls
            Set tblDest = dbDest.CreateTableDef(tbl.Name, tbl.Attributes, tbl.SourceTableName, tbl.Connect)
        Next
       
        dbSrc.Close
        dbDest.Close
       
    End If


I need to 1) get the above code to work and

          2) add code which would also copy the data


When I try to open the database created, it says that I have an "Unrecognized database format"

0
 
LVL 4

Expert Comment

by:P1
ID: 8198720
You can still run the DoCmd from within VB, provided that, either Access or Access RunTime is installed on the workststation that will run the VB code.

"Unrecognized database format" - Either it failed or you have more than one version of Access loaded.  And you have the wrong references checked in VB.

Regards,  P1

0
 
LVL 3

Accepted Solution

by:
happel earned 1200 total points
ID: 8201900
You probably have the wrong provider:

Access 2000 requires :
    Provider=Microsoft.Jet.OLEDB.4.0  >> Mdac 2.5 or higher
Access 97 (or less):
    Provider=Microsoft.Jet.OLEDB.3.51  >> Mdac 2.1

You could use Jet Replication Objects to create a copy/replica of your database.
See http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/jrorep.asp
Include 'Microsoft Jet andReplication Objects 2.x library' in project - references


Private Sub Command1_Click()


    Dim xJr As JRO.Replica
    Dim strConn As String
    Set xJr = New JRO.Replica
   
    Set xJr.ActiveConnection = GetConnection(True)
    strConn = xJr.ActiveConnection
    If xJr.ReplicaType <> jrRepTypeDesignMaster Then
        Set xJr.ActiveConnection = Nothing
        Call xJr.MakeReplicable(strConn, False)
    End If
   
    Call xJr.CreateReplica("e:\testdb\blankreplica.mdb", "Replicablank.mdb", jrRepTypeFull, jrRepVisibilityGlobal)
End Sub

Private Function GetConnection(Optional bExclusive As Boolean = False) As ADODB.Connection
    Dim acon As ADODB.Connection
    Dim aCat As ADOX.Catalog
   
    Set acon = New ADODB.Connection
    Set GetConnection = acon
   
    acon.Provider = "Microsoft.Jet.OLEDB.4.0"
    acon.CursorLocation = adUseClient
    acon.Mode = IIf(bExclusive = True, adModeShareExclusive, adModeReadWrite)
    Call acon.Open("e:\testdb\blank.mdb", "Admin", "")
   
    If acon.State <> adStateOpen Then
        Call MsgBox("Failed to open database.", vbInformation)
        Exit Function
    Else
        Set GetConnection = acon
    End If

End Function

0
 

Author Comment

by:thanks_wise1
ID: 8240794
Looks like an interesting reply...  I'll try it out, but it could be awhile (1 week or so) , because I got thrown into something else right now....
0
 

Author Comment

by:thanks_wise1
ID: 8342356
In truth, I have not yet evaluated your answer...  but I hate to keep tickets open too long, and I don't think I will be going back to this problem anytime soon...  but your answer looks to be what I was looking for when I asked the question...

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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…
Suggested Courses

704 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