bejhan
asked on
Copy Table From One Database To Another (Neither is CurrentDb)
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
ASKER
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?
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
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
ASKER
That works, much easier than what I am trying to do anyways.
Man ... that just came to me on the fly, but it seemed it *would* work. Cool.
mx
mx
ASKER
Small problem, how do you use transferdatabase on database with password on it?
ASKER
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
Not sure ... take a look at the Store Login parameter - the last one.
mx
mx
ASKER
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:
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.
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:
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.
Show me the code you have now - for the method I posted ...
thx
thx
ASKER
Okay, back at work tomorrow. Will post then.
ASKER
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 SubASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Partial - from the Help File:
The TransferDatabase method carries out the TransferDatabase action in Visual Basic.
expression.TransferDatabas
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