Updating Local Database Copies 1

[GummowN]

We have a number of large databases that are used by a few number of users. When I have updated a development copy of a database I copy it into the "live" directory. When the user opens their local copy of the database it runs through the queries, reports, and forms and copies over any that have changed and any new ones. This saves a huge amount of admin time.

HOWEVER, how the f*c* can you do the same thing with modules? They dont exist as upopened forms do in a container so any suggestions how would rip through all modules and in one database and copy them into another would be most welcome?

I have almost pulled all my hair out on this

Thanks

Neil

 

[jrbarnett]

Neil,

I came across this problem a couple of years ago when I was writing my access documenter addin and found that the following would do the trick.

Thus:

Public Function listmodules()
' Purpose: Function to list modules in an Access database.
' Written in response to thread705-600381 Author: John Barnett (tek-tips handle jrbarnett) 
' Date: 11 July 2003.

    Dim intCount As Integer
    Dim blnOpen As Boolean
    
    Dim cnt As DAO.Container
    Dim db As DAO.Database
    Dim doc As Document
    
    Set db = CurrentDb
    Set cnt = db.Containers!Modules
    
    For Each doc In cnt.Documents
        blnOpen = False
        If SysCmd(acSysCmdGetObjectState, acModule, doc.Name) = 0 Then ' module is closed.
            DoCmd.OpenModule doc.Name  ' Some of the module properties we want are only available if it is open, so we have to open it...
            blnOpen = True ' we opened it from code...
        End If
        Debug.Print doc.Name & vbTab & IIf(Modules(doc.Name).Type = acClassModule, "Class Module", "Normal Module")
        If blnOpen = True Then DoCmd.Close acModule, doc.Name ' ... so we have to then close it afterwards.
    Next doc
    Set db = Nothing
End Function

This will display a list of all the ordinary and class modules within the database, but it will not cover any of the modules behind forms and reports (although these should get copied when the form or report itself is copied.

You will obviously need to add a reference to the DAO object library if using Access 2000 or XP.

John

 

[sumoalex]

I'm not sure if this will help at all, but I've come close to pulling my hair out with other things so I'd like to try to help.

Can you try accessing "yourModule" using something like:

With Application.VBE.ActiveVBProject. _
VBComponents("yourModule&quot.CodeModule

In my VBA book , it has several examples of using this method to access the properties and contents of the module...

Let me know if this might lead you somewhere.

 

[sumoalex]

GummowN or anyone that can help:
I would LOVE to know how to do what is mentioned in the first post, as it would save me from running around installing new local .mdbs when I've made a change.

Can anyone steer me in the right direction?

Thanks!

 

[GummowN]

I will take a sharp in take of breath.

In the database that will be stored locally set a macro as autoexec to runcode - Updateapp()

Public Function UpdateApp()
' Compare the last updated date for all components in database
Dim dbs As Database, tdf As TableDef, qdf As QueryDef, frm As Form, fld As Field
Dim dbs1 As Database, tdf1 As TableDef, qdf1 As QueryDef, frm1 As Form
Dim app As Workspace, rst1 As Recordset
Dim strfilename As String
Dim doc As Document, doc1 As Document
Dim strdoc As String, strdoc1 As String
Dim rst As Recordset
Dim x As Boolean, i As Integer
Dim BAS As Module, BAS1 As Module

'DoCmd.Close acForm, "frmconfig"
strfilename = "\\bctdev\releases\collections\staticarrears.mdb"
Set dbs = CurrentDb
Set app = DBEngine.Workspaces(0)

DoCmd.SetWarnings False

Set dbs1 = app.OpenDatabase(strfilename)
Set rst = dbs.OpenRecordset("tbl_updates&quot
For Each doc In dbs.Containers!Forms.Documents
For Each doc1 In dbs1.Containers!Forms.Documents
If doc.Name = doc1.Name Then
If doc1.LastUpdated > doc.LastUpdated Then
With rst
.AddNew
.Fields("docname&quot = doc.Name
.Update
End With
End If
End If
Next doc1
Next doc

If rst.RecordCount > 0 Then
rst.MoveFirst
Do While rst.EOF = False
DoCmd.DeleteObject acForm, rst("docname&quot
DoCmd.TransferDatabase acImport, "Microsoft Access", strfilename, acForm, rst("docname&quot, rst("docname&quot, False
rst.MoveNext
Loop
End If

For Each qdf1 In dbs1.QueryDefs
i = 0
For Each qdf In dbs.QueryDefs
If qdf1.Name = qdf.Name Then
i = 1
Exit For
End If
Next qdf
If i = 0 Then
DoCmd.TransferDatabase acImport, "Microsoft Access", strfilename, acQuery, qdf1.Name, qdf1.Name, False
End If
Next qdf1

rst.Close
DoCmd.RunSQL "DELETE * FROM tbl_updates"

DoCmd.SetWarnings True

Set dbs = Nothing
Set dbs1 = Nothing
DoCmd.OpenForm ("frmConfig&quot
End Function

In the form frmconfig the open event has

Private Sub Form_Open(Cancel As Integer)
' THIS CODE UPDATES THE ALL MODULES
' Compare the last updated date for all components in database
Dim dbs As Database, tdf As TableDef, qdf As QueryDef, frm As Form, fld As Field
Dim dbs1 As Database, tdf1 As TableDef, qdf1 As QueryDef, frm1 As Form
Dim app As Workspace, rst1 As Recordset
Dim strfilename As String
Dim doc As Document, doc1 As Document
Dim strdoc As String, strdoc1 As String
Dim rst As Recordset
Dim x As Boolean, i As Integer
Dim BAS As Module, BAS1 As Module
Dim app1 As New Access.Application
Dim cnt As Container, cnt1 As Container

'DoCmd.Close acForm, "frmconfig"
strfilename = "\\bctdev\releases\collections\staticarrears.mdb"

'MsgBox CurrentDb.Name
If CurrentDb.Name = strfilename Then
Else
Set dbs = CurrentDb
Set app = DBEngine.Workspaces(0)
Set dbs1 = app.OpenDatabase(strfilename)
Set cnt = dbs.Containers!Modules
Set cnt1 = dbs1.Containers!Modules

' Update if exists
For Each doc In cnt.Documents
For Each doc1 In cnt1.Documents
If doc.Name = doc1.Name Then
If doc1.LastUpdated > doc.LastUpdated Then
DoCmd.DeleteObject acModule, doc.Name
DoCmd.TransferDatabase acImport, "Microsoft Access", strfilename, acModule, doc1.Name, doc1.Name
Exit For
End If
End If
Next doc1
Next doc

' Add if not exists
For Each doc1 In cnt1.Documents
i = 0
For Each doc In cnt.Documents
If doc.Name = doc1.Name Then
i = 1
Exit For
End If
Next doc
If i = 0 Then
DoCmd.TransferDatabase acImport, "Microsoft access", strfilename, acModule, doc1.Name, doc1.Name
End If
Next doc1

DoCmd.SetWarnings True

Set dbs = Nothing
Set dbs1 = Nothing
'app1.CloseCurrentDatabase
'app1.Quit
End If

Me.fraMeasure = Me!DefaultMeasure
Me.chkIncAddOns = Me!DefaultIncAdds
Me.fraShow = Me!DefaultView
Me.fraDisplay = Me!DefaultDisplay
fraMeasure_AfterUpdate
End Sub

You can also add tables and macros in.

I rather like this - it is quicker when the database to be stored locally is large - otherwise you might want to create a small app that when opened copies the server copy to the local machine and then opens it.

 

[sumoalex]

Well I almost fell off my chair at your quick response! How might I give you a gazillion stars for this? I really appreciate it.

Thanks a million, my friend!