Automate Import of Form Object - one DB to another

[rubbernilly]

Hello all,

I am trying to roll changes to a production database by creating a procedure that a user can run in the Rollout DB to update the Live DB. I've got the table entry changes taken care of, and I've got the query updates taken care of. There is also one form that I need to get from the Rollout DB to the Live DB.

From some searching on the web, I found a suggestion to use automation to open the Live database, and using their (slightly modified) code, I am trying this:

Public Sub AddRemoveRemoteObject( _
     ByVal FileName As String, _
     ByVal ObjectType As AcObjectType, _
     ByVal ObjectName As String, _
     bRemove As Boolean)


     Dim a As Access.Application
     Set a = New Access.Application
     With a
         Select Case Right(FileName, 4)
             Case ".adp"
                 .OpenAccessProject FileName
             Case ".mdb"
                 .OpenCurrentDatabase FileName
             Case Else
                 MsgBox "Please, pass a valid file type!", , "Uh Oh"
                 .Quit
                 Exit Sub
         End Select
        If bRemove = True Then
          .DoCmd.DeleteObject ObjectType, ObjectName
        Else
          .DoCmd.TransferDatabase acImport, "Microsoft Access", FileName, ObjectType, ObjectName
        End If
         .Quit
     End With
End Sub

My call to this procedure looks like this:

AddRemoveRemoteObject sLiveDB, acForm, "MyForm", True
AddRemoveRemoteObject sLiveDB, acForm, "MyForm", False

But this opens the database visibly on the computer (in the first line) and just sits there (it doesn't even perform the DeleteObject command). Clicking back over to the Rollout DB code window, you get the error that the DeleteObject command was canceled.

Any suggestions on fixing this procedure or on doing this another way?

 

[IanAble]

It's a bit more reliable to open an instance of Access using the shell command like this:

Private Function OpenApplication(ByVal FilePath As String, Optional WindowStyle = vbMaximizedFocus) As Object
On Error GoTo Err_OpenApplication
Dim objAccess As Object
Dim strCommandLine As String

'Use Shell to open File or activates window if File already loaded.
strCommandLine = """" & Application.SysCmd(acSysCmdAccessDir) & "\msaccess.exe" & """ """ & FilePath & """"
strCommandLine = strCommandLine & " /wrkgrp """ & clnProperties("apSystemDb").Value & """"
strCommandLine = strCommandLine & " /user """ & clnProperties("apPreviousUser").Value & """"
strCommandLine = strCommandLine & " /pwd """ & clnProperties("apPassword").Value & """"
Shell strCommandLine, WindowStyle

'Wait for shelled process to finish.
Do
Err = 0
Set objAccess = GetObject(FilePath)
Loop While Err <> 0
Set OpenApplication = objAccess

Exit_OpenApplication:
Exit Function

Err_OpenApplication:
MsgBox "OpenApplication Error: " & Err.Number & ": " & Err.Description
Resume Exit_OpenApplication
End Function

If you pass WindowStyle=vbMinimizedFocus then the application won't be seen. Use the DoCmd.TransferDatabase function on the returned Object. Then run a compact/repair in code.

I haven't used this on an ADP Project, but if you can find the command line switches for it you can modify the function example to open ADP using the Shell Object.

Although this works, it comes wil a major health warning. Importing/Exporting/Deleting project objects, or making many changes over a period will inevitably lead to file corruption.

It would be easier to run an update routine checking the local copy against a master copy. Compare the two files, When the master has been updated, copy the whole file over. This could be run overnight, on start up, batch etc.