[AccessD] Close current database

Jeanine,

    You may also like to try on the following lines.
    Let SourceDb and DestnDb be the string variables representing full paths of source & destination databases. Desired sequence of action -  Close SourceDb, Open DestnDb, take desired action within DestnDb, close DestnDb, return back to SourceDb. Two alternatives could be considered -

    Case-1 -- Hard coding
    (a) Exit Code in SourceDb -
        Dim acp As Access.Application        
        Set acp = New Access.Application
        acp.Visible = True
        acp.OpenCurrentDatabase DestnDb
        Application.Quit acQuitSaveAll

    (b) Code in startup form of DestnDb -
        << Code for action in DestnDb >>
        Dim acp As Access.Application        
        Set acp = New Access.Application
        acp.Visible = True
        acp.OpenCurrentDatabase SourceDb
        Application.Quit acQuitSaveAll

    Case-2 -- Flexible Response
    (a) Create a table (named "T_Status") in SourceDb.It will have two fields, both of text type - namely  DbFullPath(full path of source database) and Status("R" for return back to SourceDb, "S" for stay put in DestnDb).Values need not be filled manually.

    (b) Relevant command button in SourceDb would have following code in its click event -
        Dim rst1 As ADODB.Recordset
        Dim FPT As String, TBL As String
        Dim DestnDb As String, STS As String
        Dim acp As Access.Application
        
        DestnDb = "<Put here full path of destination database>"
        TBL = "T_Status"
        STS = "R"      ' R - for return back to Source Database, 
'                                S - for stay put in Destination Database.
        FPT = CurrentProject.FullName     ' Gives Full Path

        Set rst1 = New ADODB.Recordset
        Set rst1.ActiveConnection = CurrentProject.Connection
        rst1.CursorType = adOpenKeyset
        rst1.LockType = adLockOptimistic
        rst1.Source = TBL
        
        rst1.Open options:=adCmdTable
        If rst1.RecordCount > 0 Then
            rst1.MoveFirst
            rst1.Fields("DbFullPath") = FPT
            rst1.Fields("Status") = STS
        Else
            rst1.AddNew
            rst1.Fields("DbFullPath") = FPT
            rst1.Fields("Status") = STS
        End If           ' rst1.RecCount
        rst1.Update
        
        DoCmd.TransferDatabase acExport, "Microsoft Access", _
                DestnDb, acTable, TBL, TBL
                
        rst1.Close
        Set rst1 = Nothing

        Set acp = New Access.Application
        acp.Visible = True
        acp.OpenCurrentDatabase DestnDb
        Application.Quit acQuitSaveAll

    (c) Code in startup form of DestnDb -
        << Code for action in DestnDb >>
        Dim STS As String, FPT As String
        Dim acp As Access.Application
        STS = Nz(DLookup("Status", "T_Status"), "")
        FPT = Nz(DLookup("DbFullPath", "T_Status"), "")
        If STS = "R" And Len(FPT) > 0 Then
            Set acp = New Access.Application
            acp.Visible = True
            acp.OpenCurrentDatabase FPT
            Application.Quit acQuitSaveAll
        End If        ' STS

Regards,
A.D.Tejpal
--------------------------------

----- Original Message ----- 
From: Charlotte Foust 
To: accessd at databaseadvisors.com 
Sent: Friday, May 02, 2003 20:44
Subject: RE: [AccessD] Close current database

    Take a look at the OpenCurrentDatabase method.  Both databases stay open but you switch to the new one.

Charlotte Foust

  ----- Original Message ----- 
  From: Stuart McLachlan 
  To: Jeanine ; accessd at databaseadvisors.com 
  Sent: Friday, May 02, 2003 18:38
  Subject: Re: [AccessD] Close current database


  On 2 May 2003 at 7:29, Jeanine wrote:
  > 
  > I just can't get the darn thing to close itself and open another db.
  > 
  One way, of the top of my head would be:

  Public Declare Function ShellExecute Lib "shell32.dll" Alias _
  "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
  ByVal lpFile As String, ByVal lpParameters As String, ByVal _
  lpDirectory As String, ByVal nShowCmd As Long) As Long _

  Sub OpenTheOtherOne()
  ShellExecute(0,"Open","C:\Full\Path\To\YourSecondApp.mdb"","","",1)
  DoCmd.Quit
  End Sub

  Lexacorp Ltd
  http://www.lexacorp.com.pg
  Information Technology Consultancy, Software Development,System 
  Support.

  -----Original Message-----
  From: Jeanine [mailto:jscott at mchsi.com] 
  Sent: Friday, May 02, 2003 4:30 AM
  To: accessd at databaseadvisors.com
  Subject: [AccessD] Close current database

  I am running into some problems with something and I think I may just be
  making it too hard. I want to open one database from another - close the
  first and run some code in the second. Then close the second and open
  the first again.

  I have messed around with the open and close currentdb methods but they
  only seem to be for the purpose of automation.

  My objective is to check the current version when my users open their
  application. If the version is not current the routine will close the
  application and open a separate db that does the copy routine. Then the
  copy db will close itself and re-open the application. My theory is this
  will give the users a pretty seamless version control system.

  I just can't get the darn thing to close itself and open another db.

  Thanks!

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030505/2ae28f09/attachment-0001.html>