Solved

Copy Table From One Database To Another (Neither is CurrentDb)

Posted on 2008-11-02
14
1,727 Views
Last Modified: 2012-05-05
I have created a procedure to copy a table from one database to another (when neither is the current db) following this Microsoft Article http://support.microsoft.com/kb/217011. I also added an SQL statement to the end to transfer the data. I keep getting a not valid password error when I try to execute that line (the dbs are password protected). How can I change it to connect properly?
'This procedure copies the table with the specified name from source_db to destination_db
Public Sub CopyTable(dbSource As Database, dbDestination As Database, strTableName As String, blnDeleteTable As Boolean)
Dim tdfSource As TableDef
Dim tdfDestination As TableDef
Dim prpProperty As Property
Dim fldField As field
Dim fldNew As field
Dim indIndex As Index
Dim indNew As Index
    
    If blnDeleteTable Then dbDestination.TableDefs.Delete (strTableName)
    
    Set tdfSource = dbSource.TableDefs(strTableName)
    Set tdfDestination = dbDestination.CreateTableDef()
    
    InitializeMinorProgressBar "Copying Table", tdfSource.Properties.Count + tdfSource.Fields.Count + tdfSource.Indexes.Count + 1
    
    On Error Resume Next
    For Each prpProperty In tdfSource.Properties
        tdfDestination.Properties(prpProperty.name).Value = prpProperty.Value
        IncrementMinorProgressBar
    Next
    
    Err.Clear
    On Error GoTo 0
    
    For Each fldField In tdfSource.Fields
        If (fldField.Attributes And dbSystemField) = 0 Then
            Set fldNew = tdfDestination.CreateField()
            
            On Error Resume Next
            For Each prpProperty In fldField.Properties
                fldNew.Properties(prpProperty.name).Value = prpProperty.Value
            Next prpProperty
            
            Err.Clear
            On Error GoTo 0
            
            tdfDestination.Fields.Append fldNew
            
            Set fldNew = Nothing
        End If
        
        IncrementMinorProgressBar
    Next fldField
    
    For Each indIndex In tdfSource.Indexes
        If Not indIndex.Foreign Then
            Set indNew = tdfDestination.CreateIndex()
            
            On Error Resume Next
            For Each prpProperty In indIndex.Properties
                indNew.Properties(prpProperty.name).Value = prpProperty.Value
            Next prpProperty
            
            Err.Clear
            On Error GoTo 0
            
            For Each fldField In indIndex.Fields
                Set fldNew = tdfDestination.CreateField(fldField.name, tdfDestination.Fields(fldField.name).Type)
                indNew.Fields.Append fldNew
                Set fldNew = Nothing
            Next fldField
            
            tdfDestination.Indexes.Append indNew
            
            Set indNew = Nothing
        End If
        
        IncrementMinorProgressBar
    Next indIndex
        
    dbDestination.TableDefs.Append tdfDestination
    
    Set tdfDestination = Nothing
    
    CurrentDb.Execute "INSERT INTO [" & ";PWD=" & STR_DATABASE_PASSWORD & ";DATABASE=" & strTableName & "] IN '" & ";PWD=" & STR_DATABASE_PASSWORD & ";DATABASE=" & dbDestination.name & "' SELECT * FROM [" & strTableName & "] IN '" & dbSource.name & "'"
    IncrementMinorProgressBar
End Sub

Open in new window

0
Comment
Question by:bejhan
[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
  • 8
  • 5
14 Comments
 
LVL 75
ID: 22863043
Why not just use the TransferDatabase method ?

Partial - from the Help File:

The TransferDatabase method carries out the TransferDatabase action in Visual Basic.

expression.TransferDatabase(TransferType, DatabaseType, DatabaseName, ObjectType, Source, Destination, StructureOnly, StoreLogin)
expression    Required. An expression that returns one of the objects in the Applies To list.

TransferType   Optional AcDataTransferType.

AcDataTransferType can be one of these AcDataTransferType constants.
acExport
acImport default
acLink
If you leave this argument blank, the default constant (acImport) is assumed.

Note  The acLink transfer type is not supported for Microsoft Access projects (.adp).
 

DatabaseType   Optional Variant. A string expression that's the name of one of the types of databases you can use to import, export, or link data.

Types of databases:
Microsoft Access (default)
Jet 2.x
Jet 3.x
dBase III
dBase IV
dBase 5.0
Paradox 3.x
Paradox 4.x
Paradox 5.x
Paradox 7.x
ODBC Databases
WSS
In the Macro window, you can view the database types in the list for the Database Type action argument of the TransferDatabase action.
 
 

DatabaseName   Optional Variant. A string expression that's the full name, including the path, of the database you want to use to import, export, or link data.

ObjectType   Optional AcObjectType.

AcObjectType can be one of these AcObjectType constants.
acDataAccessPage
acDefault
acDiagram
acForm
acFunction
acMacro
acModule
acQuery
acReport
acServerView
acStoredProcedure
acTable default
This is the type of object whose data you want to import, export, or link. You can specify an object other than acTable only if you are importing or exporting data between two Microsoft Access databases. If you are exporting the results of a Microsoft Access select query to another type of database, specify acTable for this argument.

If you leave this argument blank, the default constant (acTable) is assumed.

Note  The constant acDefault, which appears in the Auto List Members list for this argument, is invalid for this argument. You must choose one of the constants listed above.
 

Source   Optional Variant. A string expression that's the name of the object whose data you want to import, export, or link.

Destination   Optional Variant. A string expression that's the name of the imported, exported, or linked object in the destination database.

StructureOnly   Optional Variant. Use True (1) to import or export only the structure of a database table. Use False (0) to import or export the structure of the table and its data. If you leave this argument blank, the default (False) is assumed.

StoreLogin   Optional Variant. Use True to store the login identification (ID) and password for an ODBC database in the connection string for a linked table from the database. If you do this, you don't have to log in each time you open the table. Use False if you don't want to store the login ID and password. If you leave this argument blank, the default (False) is assumed. This argument is available only in Visual Basic.


mx
0
 
LVL 1

Author Comment

by:bejhan
ID: 22863067
If I'm not mistaken the transferdatabase method cannot be used on two external databases. I read that in a forum somewhere anyway. I would love to be able to use transferdatabase. If it can be done with two external databases what is the syntax?
0
 
LVL 75
ID: 22863400
I thought I had done that once, but I can seem to find the code.

However, how about this - say you want to copy Table 1 from Db1 to Db2

Db1
Db2
CurrentDb (where you will run the code)

Using the TransferDatabase command

1)  Import Table 1 into the CurrentDb from Db1
2)  Export Table 1 to Db2
3) Delete Table1 in the Current Db

3 lines of code

mx
0
Learn by Doing. Anytime. Anywhere.

Do you like to learn by doing?
Our labs and exercises give you the chance to do just that: Learn by performing actions on real environments.

Hands-on, scenario-based labs give you experience on real environments provided by us so you don't have to worry about breaking anything.

 
LVL 1

Author Comment

by:bejhan
ID: 22863412
That works, much easier than what I am trying to do anyways.
0
 
LVL 75
ID: 22863587
Man ... that just came to me on the fly, but it seemed it *would* work.  Cool.

mx
0
 
LVL 1

Author Comment

by:bejhan
ID: 22863592
Small problem, how do you use transferdatabase on database with password on it?
0
 
LVL 1

Author Comment

by:bejhan
ID: 22863675
I tried http://accessblog.net/2006/04/how-to-export-objects-to-secured.html method but it won't work for me, maybe I am implementing it wrong? When I run the below code I still am prompted for a password.
Public Sub CopyTable()
Dim dbSource As Database
Dim ws As Workspace
Dim strTableName As String
 
    strTableName = "itblBusinessUnits"
    
    Set ws = DBEngine.Workspaces(0)
    Set dbSource = ws.OpenDatabase(BACKEND_PATH, False, True, dbLangGeneral & ";pwd=" & STR_DATABASE_PASSWORD)
 
    DoCmd.TransferDatabase acImport, "Microsoft Access", dbSource.name, acTable, strTableName, strTableName & "_temp", False
End Sub

Open in new window

0
 
LVL 75
ID: 22864334
Not sure ... take a look at the Store Login parameter - the last one.

mx
0
 
LVL 1

Author Comment

by:bejhan
ID: 22871657
Sorry for reopening, chose your solution before actually implementing the idea.

I thought storelogin was for linked tables:
Use True to store the login identification (ID) and password for an ODBC database in the connection string for a linked table from the database. If you do this, you don't have to log in each time you open the table. Use False if you don't want to store the login ID and password. If you leave this argument blank, the default (False) is assumed. This argument is available only in Visual Basic.

"database in the connection string for a linked table from the database"

When I try to use a connection string for the database name:
";PWD=password;Database=F:\mydb.mdb"

But I get a networking error when I try to use that one.

In my previous post I found a method that show to open the database object with the password with opendatabase method then use transferdatabse with that databases path and since you already had a connection open it doesn't prompt for password. But when I use that (as in my code in that post) I am still prompted for a password.
0
 
LVL 75
ID: 22871691
Show me the code you have now - for the method I posted ...

thx
0
 
LVL 1

Author Comment

by:bejhan
ID: 22871737
Okay, back at work tomorrow. Will post then.
0
 
LVL 1

Author Comment

by:bejhan
ID: 22880210
Here is the procedure, as well as a procedure I invoke it with.
'This procedure copies the table with the specified name from dbSource to dbDestination
Public Sub CopyTable(strSourceDB As String, strDestinationDB As String, strTableName As String)
     DoCmd.TransferDatabase acImport, "Microsoft Access", ";pwd=" & STR_DATABASE_PASSWORD & ";database=" & strSourceDB, acTable, strTableName, strTableName & "_temp", False
     DoCmd.TransferDatabase acExport, "Microsoft Access", ";pwd=" & STR_DATABASE_PASSWORD & ";database=" & strDestinationDB, acTable, strTableName & "_temp", strTableName, False
     DoCmd.DeleteObject acTable, strTableName & "_temp"
End Sub
 
'This procedure backs up important database tables
Private Sub BackupDatabase()
Dim strArchiveFullPath As String
Dim dbDestination As Database
Dim ws As Workspace
 
    strArchiveFullPath = CurrentProject.Path & "\Archive\NSF Archive " & Format(Now(), "yyyy-mmm-dd hh00") & ".mdb"
    If Dir(strArchiveFullPath, vbReadOnly) <> "" Then Exit Sub
 
    Set ws = DBEngine.Workspaces(0)
    'Create a new mdb file
    Set dbDestination = ws.CreateDatabase(strArchiveFullPath, dbLangGeneral & ";pwd=" & STR_DATABASE_PASSWORD)
        
    Set dbDestination = Nothing
    Set ws = Nothing
    
    '5 main components
    EnableProgressBars 5, "Backup"
                        
    CopyTable BACKEND_PATH, strArchiveFullPath, "itblBusinessUnits"
    IncrementMajorProgressBar
    
    CopyTable BACKEND_PATH, strArchiveFullPath, "itblMinistryBankAccounts"
    IncrementMajorProgressBar
    
    CopyTable BACKEND_PATH, strArchiveFullPath, "itblReasons"
    IncrementMajorProgressBar
    
    CopyTable BACKEND_PATH, strArchiveFullPath, "itblSystems"
    IncrementMajorProgressBar
    
    CopyTable BACKEND_PATH, strArchiveFullPath, "tblNSFs"
    IncrementMajorProgressBar
       
    DisableProgressBars
 
End Sub

Open in new window

0
 
LVL 1

Accepted Solution

by:
bejhan earned 0 total points
ID: 22924684
Okay found the problem with the below code, when using the OpenDatabase method I was setting the read-only property to true. This will cause problems.
Public Sub CopyTable()
Dim dbSource As Database
Dim ws As Workspace
Dim strTableName As String
 
    strTableName = "itblBusinessUnits"
    
    Set ws = DBEngine.Workspaces(0)
    Set dbSource = ws.OpenDatabase(BACKEND_PATH, False, True, dbLangGeneral & ";pwd=" & STR_DATABASE_PASSWORD)
 
    DoCmd.TransferDatabase acImport, "Microsoft Access", dbSource.name, acTable, strTableName, strTableName & "_temp", False
End Sub

Open in new window

0

Featured Post

Learn by Doing. Anytime. Anywhere.

Do you like to learn by doing?
Our labs and exercises give you the chance to do just that: Learn by performing actions on real environments.

Hands-on, scenario-based labs give you experience on real environments provided by us so you don't have to worry about breaking anything.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

717 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