Automatically Updating Frontend when changes made 6

JARE,

Look at replication. You need to set this up carefully but there are no real monster problems. [sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]

What I am really looking for I guess is an Update program.
I don't want to replicate any data, just the Forms + modules in the front end. one of the draw-backs of a split
database is every time you update a form, module, etc you
have to redistribute the front end mdb or mde. something
that will check to see if there is a newer version and if there is update all forms,modules, etc [sig][/sig]

There's no way to automatically update the forms in Access. If you want to leave the users out of the loop when updating the forms database, create a script file when the users login. In the script file copy the new file over the old one.

Just make sure you remove the script file after the old file has been updated or else everytime they login this process will keep reoccuring.

Mr. Access,

You say that with conviction !

I suspect that this a conclusion you have come to based on your experience of not achieving this.

WP [sig]<p>Bill Paton<br><a href=mailto:william.paton@ubsw.com>william.paton@ubsw.com</a><br><a href= [/sig]

JARE,

The following 'works for me'.

You do need to &quot;link&quot; the &quot;MasterDB&quot; MSysObjects into the &quot;Slave&quot; datbases (it is the [MSysObjects1] table. in the query)

I have NOT tested this thoroughly, so you may need to adjust some of the criteria calculation (e.g. the dates), however it DOES copy the objects from an external (MS Access) db to the current (MS Access) db, based on the recordset generated by the SQL statement.

I have used the &quot;hidden&quot; system table (MSysObjects) to get the list, however there are other mechanisims which could achieve the same result. Also, I have chosen to generate the recordset of the objects to transfer via the Querydef/Sql statement while other methods may be more appropiate, however this allows me to show the entire process in a single module, rather than showing SQL statements seperatly.



Public Function basUpdateMDB()

'Function to Check the &quot;Master&quot; MDB and update the Local
'copy of Forms, Modules and Reports where the Date/Time
'stamp of the Local is NOT the same as the Master

Dim dbs As Database
Dim MastDbs As Database
Dim rst As Recordset
Dim qdf As QueryDef
Dim MyType As Integer
Dim strSQL As String

Set dbs = CurrentDb
Set MastDbs = DBEngine.Workspaces(0).OpenDatabase(&quot;C:\My Documents\BksByPubl.MDB&quot

strSQL = &quot;SELECT DISTINCTROW &quot;
strSQL = strSQL & &quot;[MSysObjects1].[DateUpdate]>IIf(IsNull([MSysObjects].[DateUpdate]),0,[MSysObjects].[DateUpdate]) AS New, &quot;
strSQL = strSQL & &quot;MSysObjects1.Name, MSysObjects1.Type &quot;
strSQL = strSQL & &quot;FROM MSysObjects1 &quot;
strSQL = strSQL & &quot;LEFT JOIN MSysObjects ON (MSysObjects1.Name = MSysObjects.Name) AND &quot;
strSQL = strSQL & &quot;(MSysObjects1.DateUpdate = MSysObjects.DateUpdate) &quot;
strSQL = strSQL & &quot;WHERE (((MSysObjects1.Type)=-32761) AND &quot;
strSQL = strSQL & &quot;(([MSysObjects1].[DateUpdate]>IIf(IsNull([MSysObjects].[DateUpdate]),0,[MSysObjects].[DateUpdate]))=True) &quot;
strSQL = strSQL & &quot;AND &quot;
strSQL = strSQL & &quot;(([MSysObjects1].[DateUpdate]>IIf(IsNull([MSysObjects].[DateUpdate]),0,[MSysObjects].[DateUpdate]))=True) &quot;
strSQL = strSQL & &quot;AND ((MSysObjects.DateUpdate) Is Null)) OR &quot;
strSQL = strSQL & &quot;(((MSysObjects1.Type)=-32768) AND &quot;
strSQL = strSQL & &quot;(([MSysObjects1].[DateUpdate]>IIf(IsNull([MSysObjects].[DateUpdate]),0,[MSysObjects].[DateUpdate]))=True) &quot;
strSQL = strSQL & &quot;AND ((MSysObjects.DateUpdate) Is Null));&quot;

Set qdf = dbs.CreateQueryDef(&quot;&quot;, strSQL)
Set rst = dbs.OpenRecordset(strSQL)

While Not rst.EOF
Select Case rst!Type
Case Is = -32761
MyType = acModule

Case Is = -32764
MyType = acReport

Case Is = -32768
MyType = acForm

End Select

DoCmd.TransferDatabase acImport, &quot;Microsoft Access&quot;, _
MastDbs.Name, MyType, rst!Name, rst!Name
rst.MoveNext
Wend

End Function

[sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]

JARE,

I do like this approach !

I'm not sure about the TransferDatabase statement though. If you import to the same name and the object exist - as it will if it is an amended object - then it will import as Name1 i.e. suffixed by a 1. You may have to delete the old one first.

I also missed any new objects but hey that's only a minor change.



WP [sig]<p>Bill Paton<br><a href=mailto:william.paton@ubsw.com>william.paton@ubsw.com</a><br><a href= [/sig]

WP,

OOps re the ammended. It is easy to insert a quick &quot;quick fix&quot;. Insert the code below in place of the original docmd.transferdatabase.


On Error GoTo ObjErr
DoCmd.DeleteObject MyType, rstName
On Error GoTo 0

DoCmd.TransferDatabase acImport, &quot;Microsoft Access&quot;, _
MastDbs.Name, MyType, rst!Name, rst!Name
rst.MoveNext
Wend

ObjErr:
If (Err = 3011) Then 'Can't find Object
Resume Next
End If


One of the few times where I think that letting the err occur is probably as efficient as checking the condition to avoid the error.

I believe the function - as posted - does get any new objects, re the date of a 'non-existant' object is set to Zero, and all of the object names are taken from the &quot;master&quot; db.

If I am mistaken about this, please let me know. It 'worked' on a simple test with new objects for me - but stuffff does happen and I did NOT test this thoroughly.
[sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]

Hmmm,

Now, I bet we run into the problem of trying to replace an objets which is &quot;open&quot; as well.

JARE,

This function will need to be called from/located in some object which - itself - will NEVER be updated. A &quot;Spalsh&quot; screen with JUST an advertisement/logo and NO controls or controlbox would be appropiate. Use this as the 'startup' screen, and wait for the function to finish before letting the USER actually do anything. The code below incorporates all of the above - from the while loop to the end of the function. &quot;MySplashScreenName is (obviously) a dummy which you replace w/ the name of your splash screen.

While Not rst.EOF

If (rst!Name = &quot;MySplashScreenName&quot Then
GoTo NoCopy
End If

Select Case rst!Type
Case Is = -32761
MyType = acModule

Case Is = -32764
MyType = acReport

Case Is = -32768
MyType = acForm

On Error GoTo ObjErr
DoCmd.DeleteObject MyType, rstName
On Error GoTo 0

DoCmd.TransferDatabase acImport, &quot;Microsoft Access&quot;, _
MastDbs.Name, MyType, rst!Name, rst!Name
rst.MoveNext

GoTo NoCopy:

Wend

ObjErr:
If (Err = 3011) Then 'Can't find Object
Resume Next
End If

NEVER update the startup screen (or do it manually)

WR, et al.

Hope this resolves the question?



[sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]

MichaelRed,

I will try your idea, I havent thought about
comparing system tables. I think this will work
much better than my original idea.

[sig][/sig]

MichaelRed,
This works great. this works much better than what
I was doing to update my frontend. here is the code
with all the bugs worked out

Public Function basUpdateMDB()

'Function to Check the &quot;Master&quot; MDB and update the Local
'copy of Forms, Modules and Reports where the Date/Time
'stamp of the Local is NOT the same as the Master

Dim dbs As Database
Dim MastDbs As Database
Dim rst As Recordset
Dim qdf As QueryDef
Dim MyType As Integer
Dim strSQL As String

Set dbs = CurrentDb
Set MastDbs = DBEngine.Workspaces(0).OpenDatabase(&quot;\\home\JessupDB\MyDataBase.mdb&quot ' path to master

strSQL = &quot;SELECT DISTINCTROW &quot;
strSQL = strSQL & &quot;[MSysObjects1].[DateUpdate]>IIf(IsNull([MSysObjects].[DateUpdate]),0,[MSysObjects].[DateUpdate]) AS New, &quot;
strSQL = strSQL & &quot;MSysObjects1.Name, MSysObjects1.Type &quot;
strSQL = strSQL & &quot;FROM MSysObjects1 &quot;
strSQL = strSQL & &quot;LEFT JOIN MSysObjects ON (MSysObjects1.Name = MSysObjects.Name) AND &quot;
strSQL = strSQL & &quot;(MSysObjects1.DateUpdate = MSysObjects.DateUpdate) &quot;
strSQL = strSQL & &quot;WHERE (((MSysObjects1.Type)=-32761) AND &quot;
strSQL = strSQL & &quot;(([MSysObjects1].[DateUpdate]>IIf(IsNull([MSysObjects].[DateUpdate]),0,[MSysObjects].[DateUpdate]))=True) &quot;
strSQL = strSQL & &quot;AND &quot;
strSQL = strSQL & &quot;(([MSysObjects1].[DateUpdate]>IIf(IsNull([MSysObjects].[DateUpdate]),0,[MSysObjects].[DateUpdate]))=True) &quot;
strSQL = strSQL & &quot;AND ((MSysObjects.DateUpdate) Is Null)) OR &quot;
strSQL = strSQL & &quot;(((MSysObjects1.Type)=-32768) AND &quot;
strSQL = strSQL & &quot;(([MSysObjects1].[DateUpdate]>IIf(IsNull([MSysObjects].[DateUpdate]),0,[MSysObjects].[DateUpdate]))=True) &quot;
strSQL = strSQL & &quot;AND ((MSysObjects.DateUpdate) Is Null));&quot;

Set qdf = dbs.CreateQueryDef(&quot;&quot;, strSQL)
Set rst = dbs.OpenRecordset(strSQL)

While Not rst.EOF
Select Case rst!Type
Case Is = -32761
MyType = acModule

Case Is = -32764
MyType = acReport

Case Is = -32768
MyType = acForm

End Select

On Error GoTo ObjErr
docmd.DeleteObject MyType, rst!NAME
On Error GoTo 0

docmd.TransferDatabase acImport, &quot;Microsoft Access&quot;, _
MastDbs.NAME, MyType, rst!NAME, rst!NAME
rst.MoveNext
Wend

ObjErr:
If (Err = 3011) Then 'Can't find Object
Resume Next
End If



End Function


[sig][/sig]

I would like to do the same myself. JARE, will this work for me? I think my situation is similar. A back-end database on the network server with one data table only. A front-end with the forms, queries and macros on five to six pcs.

Todd

There is another way worth thinking about. It's not as elegant as the approaches already listed here but in the right situations, it is much simpler. If your users are all in the same NT usergroup, just edit their login scripts to include a line that copies the front end code across the network using the /d option of the xcopy command. This will only copy the file across if the modified date of the source file is later than the modified date of the destination. This way you can keep a copy of the code in a central location and every time you update it the users will automatically be updated. Still, like I said, this method may not be appropriate for all situations Durkin
alandurkin@bigpond.com

This question comes up rather frequently, and this thread has three good answers (replication, code to look at the system table, and using NT or Novell scripting.) I think it should have the rough edges knocked off and be made a FAQ. MichaelRed, you submitted two of the three answers, and did a lot of the coding, how about it?

I faced a similar problem with a networked Access database with 25 users. We found a very simple solution:

1) Split the database into a Front-end and a Back-end using the Access database splitter. Place both the Front-end and the Back-end on the server.

2) Create a folder on each user's C: or D: drive. Write a short Dos script to copy the Server Front-end to the folder you created on the C: or D: drive. The second line of the Dos script points to local user's MS-Access, then to the Server Front-end, and then to the Workgroup file (if you are using Access Workgroup Security). Place this .bat file in the folder you created on the users C: or D: drive.

3) I place an icon on the users desktop that points to the .bat file.

Put simply, the database is split into the &quot;heavy&quot; backend (tables) and a relatively &quot;light&quot; frontend (queries, forms, reports, modules). Each time a user logs on, their .bat file copies the Server Front-end and places it on their local hard drive (with whatever changes you have made to the Server Front-end). The local front end then starts up, linking to the Server Back-end.

Key point: The Server Front-end is not used by anyone in their daily work, it is simply a template that is copied anytime they login. This means that you can make changes to the Server Front-end while users are working. If a user needs something changed on a form/report. Make the change on the Server Front-end, and then ask the user to log out and log in. Voila! The local user's frontend is updated.

Paron,
I've done both shamaor's suggestion and Michael's at different times in the past. Shamaor's is the most straightforward, but in many cases I cache data locally--not just short-term cacheing but long term, so this data would be gone in this case, which is a big disadvantage. Yes there are ways around it, but then the solution becomes less straightforward.

The transferdatabase solution has worked well for me, but there are a few gotchas, most particularly Global Variables. If you try to transfer a module containing Globals, this will cause highly unpredictable results. Access is trying to replace a module with globals, now when the 'new' module comes in (even if the changes are minor and in some obscure procedure), access will try to re-declare the 'new' globals, and the module will come in under a new name (with a '1' appended), and you'll have 'duplicate declaration' errors all over.

The obvious solution for the above problem is to put all globals in a separate module, and when *that* one needs replacing, then it must be done 'manually'. Otherwise, my choice is the transferdatabase solution.
--Jim

Hi
I have the same situation,where I am still building the front end. I found a small software called Second Copy 2000
This Program can be set up to upgrade on boot up or shut down or every hour at a certain time of the day etc.
When I make alterations to my copy of the Front end I then upgrade the Active copy used by the other users. The next time the users bootup they have the latest release.
The file name must remain the same so that the multi users shortcuts dont need to be changed.
The only draw back is all users must be off the system for the update process to work. or set the updates to work during the night when everyone has closed off from Access.

This may seem like a stupid question but I thought that updating your Forms, Modules etc was exactly what synchronizing your Replicas did? Have I been mistaken? I've been relying on synchronization to keep my replica F-E updated, if it hasn't been doing that then I've got problems.

:-(

Hi aexley,

I think Replication and Synchronization works ok for Records but not for Objects.

Take a look at this thread705-551230

Sorry that it's taken more than a year to reply to your question.

Bill

I know I'm a little late posting this, but I wanted to share how I approached this in my company.

I keep a table on the front-end part of the database with a version history. When a user opens his/her copy of the front end, the startup module compares the highest version number on the local front end with with version number on the server copy of the front end.

If the server copy of the front-end has a higher version number, a BAT file is created and executed that copies the entire server front-end database to the same place that the local copy resides, closes the current local front-end and restarts the new local front-end.

This is much simpler in that any changes to tables, queries, forms, macros or modules are included without having to walk through the MSysObjects. Also, new or deleted objects are handled more easily.

Another feature of my code is that, if a user starts the server version of the front-end, a copy is automatically copied to the user's local drive, the server version is closed and the local version is opened. This makes it easy to roll out new applications.

Cruising the Information Superhighway
_{(your mileage may vary)}