Copy AND rename database objects to another database

[jasonpho]

Hi,

I'm creating an auto-update program for MS Access, the idea is that you add all objects to one database, we'll call it "Updater.mdb", and then you open "updater.mdb". It will then connect to another database, we'll call it "program.mdb", and copy all database objects.

This is easy to do, but the catch is that if the object already exists, then I want to rename the object before copying, i.e. if "report" already exists, I'll rename it to "report1" and then copy report.

I've had a lot of problems renaming objects in a foreign database. I tried using the documents.properties("name") property, but that seems to be read-only. It seems like the ONLY way to do it is to use docmd. So, I tried that: I used automation to create a new application and connect to my target database, and used docmd to rename the objects. This may work - I ran into one problem here but I think I can overcome it - but then my code to copy the object no longer works, since the database is opened by another process.

Bottom line: I guess I could close and re-open the databases for every name change/copy pair, but that seems like a hack and scares me a bit, plus I wouldn't want users seeing the database close and open repeatedly, plus suppose that I have several hundred of these, that starts to get pretty crazy.... Is there a better way? I really wish there was a way to rename a database object (report, form, macro, etc.) without having to use Application.docmd... Is there some way that I don't know of? If there is a way to do it inside DAO.database, - or maybe even with another object model? - then I'm golden. Otherwise, I'm screwed

Jason

 

[MichaelRed]

DoCmd.TransferDatabase See help for the args



MichaelRed

 

[jasonpho]

Thanks for the reply.

Sorry if I didn't make this clear enough, but docmd.transferdatabase won't work (I've tried using it for the copy object part). Let me see if I can explain why in a nutshell:

Effectively, I need a function called "docmd.copydboject", that accepts the same parameters as transferdatabase, however the source and target objects both reside in the SAME database. Or, to put it another way, I need a docmd.rename function that works on an external database...

Here is my code. The line where I currently get an error is "DoCmd.TransferDatabase acImport, "Microsoft Access", strPathDestDB, acForm, strName, strName" . The error is ""Could not use 'C:\Blah\Blah.mdb'; file already in use." This is due to the fact that I've opened the databasebase separately for the rename action.

Now, If I could come up with a way to re-name objects without having to open up a separate instance, that would solve my problem and it would be really swell.

One more caveat: For some reason, the rename doesn't seem to be working, so it's useless in its current form anyways, which just reduces to the same problem: How can I use an object model, either DAO or something else, that will allow me to rename AND import?

Option Compare Database
Option Explicit

Private mStrSourcedbPath As String
Private mSourcedb As DAO.Database
Private mTargetdb As DAO.Database
Private objWrkJet As DAO.Workspace
Dim mAppAccess As Access.Application

Private Const SYSTEM_OBJECT_PREFIX As String = "ZZ_SYSTEM_"

Public Function CopyAllObjects(ByVal strPathDestDB As String, strFileNames As String)

If Right(strPathDestDB, 1) <> "\" Then
strPathDestDB = strPathDestDB & "\"
End If

Dim arrFileNames() As String, i As Integer, strFileName As String, strPathAndNameDestDB As String
arrFileNames = Split(strFileNames, ";")
For i = LBound(arrFileNames) To UBound(arrFileNames)
strFileName = Trim(arrFileNames(i))
strPathAndNameDestDB = strPathDestDB & strFileName
Call CopyAllObjectsOfAType(strPathAndNameDestDB, acForm)
Call CopyAllObjectsOfAType(strPathAndNameDestDB, acMacro)
Call CopyAllObjectsOfAType(strPathAndNameDestDB, acModule)
Call CopyAllObjectsOfAType(strPathAndNameDestDB, acQuery)
Call CopyAllObjectsOfAType(strPathAndNameDestDB, acReport)
Call CopyAllObjectsOfAType(strPathAndNameDestDB, acForm)
Call CopyAllObjectsOfAType(strPathAndNameDestDB, acForm)
Next

mAppAccess.Quit
End Function

Public Function CopyAllObjectsOfAType(strPathAndNameDestDB As String, SourceObjectType As AcObjectType) As String
On Error GoTo error_handler

If mSourcedb Is Nothing Then
Set mSourcedb = CurrentDb
End If

If (mTargetdb Is Nothing Or mStrSourcedbPath <> strPathAndNameDestDB) Then
mStrSourcedbPath = strPathAndNameDestDB
Set mAppAccess = New Access.Application
Call mAppAccess.OpenCurrentDatabase(strPathAndNameDestDB, True)
Set mTargetdb = mAppAccess.CurrentDb
End If

If mTargetdb Is Nothing Then
Logger.WriteLine "Unable to open database " & strPathAndNameDestDB
GoTo exit_function
Else
Logger.WriteLine "Successfully opened database " & strPathAndNameDestDB
End If

Dim objCollectionDocuments As Object

Select Case SourceObjectType
Case acTable
Set objCollectionDocuments = mSourcedb.TableDefs
Case acQuery
Set objCollectionDocuments = mSourcedb.QueryDefs
Case acForm
Set objCollectionDocuments = mSourcedb.Containers("Forms").Documents
Case acModule
Set objCollectionDocuments = mSourcedb.Containers("Modules").Documents
Case acReport
Set objCollectionDocuments = mSourcedb.Containers("Reports").Documents
Case acMacro
On Error Resume Next
Set objCollectionDocuments = mSourcedb.Containers("Scripts").Documents 'Documentation says name is "Macros", but in my tests the name is "Scripts", so I'm going to support both
If objCollectionDocuments Is Nothing Then
Set objCollectionDocuments = mSourcedb.Containers("Macros").Documents
End If
On Error GoTo error_handler
End Select

Dim objDocument As Object, strObjectName As String
For Each objDocument In objCollectionDocuments
strObjectName = objDocument.Name
If Left(strObjectName, Len(SYSTEM_OBJECT_PREFIX)) <> SYSTEM_OBJECT_PREFIX Then
Call CopyObject(strPathAndNameDestDB, SourceObjectType, strObjectName, mTargetdb)
End If
Next

exit_function:
Exit Function

error_handler:
Dim strErrDesc As String
strErrDesc = "Error upgrading objects. Error was: " & Err.Description
Logger.WriteLine strErrDesc
Resume exit_function

End Function

Private Function CopyObject(strPathDestDB, SourceObjectType As AcObjectType, strName As String, objTargetDB As DAO.Database)
On Error GoTo error_handler

Dim strErrDesc As String

Const FIRST_RENAMED_SUFFIX As Long = 1

Dim objDocuments As Object
Select Case SourceObjectType
Case acForm
Set objDocuments = mTargetdb.Containers("Forms").Documents
Case acModule
Set objDocuments = mTargetdb.Containers("Modules").Documents
Case acQuery
Set objDocuments = mTargetdb.QueryDefs
Case acReport
Set objDocuments = mTargetdb.Containers("Reports").Documents
Case acTable
Set objDocuments = mTargetdb.TableDefs
Case acMacro
On Error Resume Next
Set objDocuments = mTargetdb.Containers("Scripts").Documents
If objDocuments Is Nothing Then
Set objDocuments = mTargetdb.Containers("Macros").Documents
End If
On Error GoTo error_handler
End Select

Call RenameIfExists(strName, strName, SourceObjectType, objDocuments, FIRST_RENAMED_SUFFIX)

'Now, the object has been renamed, if necessary, so we will now copy it
DoCmd.TransferDatabase acImport, "Microsoft Access", strPathDestDB, acForm, strName, strName
Logger.WriteLine "Successfully copied " & strName & " to target database."
exit_function:
Exit Function

error_handler:
strErrDesc = "Error copying " & strName & " to target database. Error was: " & Err.Description
Logger.WriteLine strErrDesc
Resume exit_function

End Function

Private Function RenameIfExists(strOriginalNewName As String, ByVal strCurrentNewName As String, _
SourceObjectType As AcObjectType, objDocuments As Object, nCount As Long)

On Error Resume Next
Dim objItem As Object, mytest As Document

Set objItem = objDocuments(strCurrentNewName)

If Not objItem Is Nothing Then
Dim strNextCurrentNewName As String
strNextCurrentNewName = strOriginalNewName & nCount
Call RenameIfExists(strOriginalNewName, strNextCurrentNewName, SourceObjectType, objDocuments, nCount + 1)
mAppAccess.DoCmd.Rename strNextCurrentNewName, SourceObjectType, strCurrentNewName
End If
End Function
Public Function CloseObjects()
Set mSourcedb = Nothing
Set mTargetdb = Nothing
End Function

 

[MichaelRed]

hmmmmmmmmmmmmmmmmmmmmmmmm ... mmmmmmmmmmmmmmmmm

I'm a bit confused. The concept of

" ... add all objects to one database ... "

seems a bit moronic - particularly when you mention objects with the same name needing to be re-named. as this seems like it can (will?) cause objects which as renammed and then referenced in code to no longer be available (in that code), and the code will in fact then reference a differnt object?

So, if you avoid the generic concatenation of all objects into an amorphus mass, you also avoid the need to rename any objects or loosing the correct references.

From these rambalings, I am guessing that you are attempting to consolidate several (related?) databases into one (less?) manageable db? I would do this only with much ado about everthing!

In particular, the same named objects need to be identified and corrected before you even start to get ready to think about beginning to do the concatenation. FIX THESE FIRST - in their 'native' environment, then -again- you avoid the renamiing issue.

On one additional level, you need to examine and correct as necessary each and every PROCEDURE for the same namming issue at least in the general modules and public procedures in Forms and Report modules.

A review of general principals of database organization seems to be appropiate.



MichaelRed

 

[jasonpho]

Michael,

Thanks for the reply and the analsysis. Let me explain the problem statement, and maybe you could suggest a better way to solve it.

The problem statement here is: How to deliver updates to our clients of custom reports AND customizations (80% of the time this will be custom reports, but in some cases there are custom tables, queries and/or forms).

Our program consists of a single Access Database (and a back-end database, but we won't worry about that for simplicity), let's call it "program.mdb", and we have over 100 clients that use this program.

Now, most clients use the standard reports. But, around 20 clients pay us to deliver them custom reports, and throughout the years they ask us to make changes, etc., at which point in time we deliver updated custom reports.

Now, considering that our clients are very basic computer users, and furthermore we want to release these reports in a professional, single-click fashion, what is the best way to deliver these updated reports to their access databases?

The solution I came up with is the one I'm working on: A process to copy all objects from an "updater.mdb" to "program.mdb". We are using an Installshield type product, so all the user has to do is click "Install.exe", it will launch the "updater.mdb", display a splash screen where they say "Ok" to do the upgrade, and then it will upgrade their reports to the appropriate version.

However, as a part of this process, my boss wants to make sure that we don't overwrite any existing reports (or other objects), you know, in the name of "just in case". So, for that reason I am renaming them prior to doing the copy. So, if a client asks for a custom version of the "RptInventory", then we'll ship it to them, and they'll end up with a "RptInventory1" in their database, which is the old version "just in case", and the program will from thenceforth use the custom "RptInventory" for this client.

So, how do you suggest we solve this problem?

Sincerely,

Jason