Solved

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

Posted on 2008-11-02
14
1,718 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
  • 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
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…

744 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

15 Experts available now in Live!

Get 1:1 Help Now