Wait for Sync!

[GohDiamond]

All right, I 've found thru searching other forums that the acCmdSynchronizeNow command doesn't work from VB because it's a known issue that it hangs in the process and this issue apparently has never been fixed.

The suggested work around is to write the code in longhand, if you will, that runs when acCmdSynchronizeNow is executed. I don't know what that is so I'd appreciate help on that if anyone knows the code.

I've tried other means like having a vbYesNo Message Box ask if you'd like to Synchronize, then based on the answer try to either run the command or use SENDKEYS to mimic manual selection of the command. The only thing I've found out for sure is that this event has to be completed before the actual CLOSE/QUIT event for access or it won't work either. And I haven't found a way to pre-empt the close. If anyone has suggestions for pre-empting the CLOSE/QUIT event let me know. That may work.

Thanks in advance for your help

Goh

 

[GohDiamond]

A+ Possible SYNC Solution / Work Around

OK

I've been hacking away at this problem for more than a month now and I think I may have at least one solution to this conundrum. Since it appears that the command acCmdSynchronizeNow doesn’t work without hanging up, I decided to pursue the SENDKEYS idea. Admittedly this is not the most secure way to approach the matter, but it is fairly secure because of the way I have set up the database and security by User levels. I don’t have enough hairs to be splitting hairs so I’m going with it.

The database I’m working with is on a shared directory on a LAN, it has been split and the security wizard used to set up User Level Security. The backend is password protected. There is a single Data Master which 14+ replicas being used will synchronize to. All Replicas are on wired desktops, no offline, and no wireless. The workgroup file is also resident on the LAN shared directory and is targeted by a shortcut specific to each user within their profile on their assigned portion of the C:\ drive of their assigned CPU. User Level doesn’t allow full menus and the database window is hidden. Nothing can be changed in the replicas because that’s how replicas work.

I’ll break down the procedure to the basics. I’ve used a few more screens for my own purposes but they are not requirements for the principle to work. Here’s the premise:

Fire the event when a common screen/form loads. In this case I use the Switchboard
On_Load check with the SyncDate Table
Verify if today’s date is equal to or later than the date for the event to fire
Verify if the Replica has already been synchronized by comparing the boilerplate with the revision number
If Synchronization is needed then use a macro to send the commands to the tool bar and subsequent dialog boxes

This required:
1 Table to set the date for the event to fire and the revision to be updated to
2 Forms w/ vba to evaluate and restrict user options
1 Macro to launch the Synchronize Now command as if selected manually by user

The table (hidden) called SyncDate (just my preference) has 3 fields and contains only 1 record for my purposes [SyncID] = 1, [SyncDate] = <any date>, [Service Pack] = <assigned revision number>


The Switchboard has a boilerplate with a hard-coded revision number. This is a bit of a pain because it has to be updated each time; this was the most effective at present, so, I’m going with it for now. I tried using a variable field but it caused too many problems.

FORM1: Here is what I have in the switchboard form property:


Private Sub Form_Load()

'VarQ is ServicePack from the SyncDate table as Text'
'VarX is SyncDate from the SyncDate table as date'
'TodayzDate is the system date from the OS'

Dim VarQ As String
Dim VarX As Date


VarQ = DLookup("[ServicePack]", "SyncDate", "[SyncID] = '1'")
VarX = DLookup("[SyncDate]", "SyncDate", "[SyncID] = '1'")
TodayzDate = Date

'Is the current date later than or equal to the date set in the table for the event to fire?'

If TodayzDate >= VarX Then

'If so, then, is the SvcPkFieldSB (Service Pack Field on the Switchboard) equal to the Service Pack from the SyncDate Table?'

If VarQ = SvcPkFieldSB Then

'If equal then let the Switchboard open as usual'
'You may like to indicate that the replica is up to date at this point with a MsgBox, but I decided not to'
'Just exit the sub'

Exit Sub

Else
'But if the HardCoded SvcPkField content is not the same as the Service Pack identifier from the table then display the NSYNC Splash screen'
'(the name really has nothing to do with the boy band, I just liked the sound of it)'
'Having this screen display in front of everything else as a Popup, modal with no other options but the Central OK button'
'it prevents anyone from proceeding with an out of date replica.'
'Since nothing can be changed at a Replica except by synchronization the step can't be bypassed.'
'Although the SyncDate table is present, it cannot be accessed because it is hidden and the database window is also hidden and users don't have admin rights'

'The form "NSYNC Splash" lets the user know that the Replica is out of Date then the control is turned over to that form'
DoCmd.OpenForm "NSYNC Splash"
End If
Else
End If

End Sub

FORM 2 NSYNC Splash (Hidden): Has one large button declaring that the Replica is out of date and to “Click Here” to proceed. This closes just the NSYNC Splash screen (because it can’t be closed any other way) and launches the Macro which sends key strokes to the toolbar and subsequent dialog boxes.

MACRO (NSYNC)(hidden):
Close: acForm, “NSYNC Splash”, SAVE
SENDKEYS %(TPS)~~ which means press alt (%) and execute the following keystrokes T = Tools, P = Replication, S = Synchronize Now…, ~= <<Enter key>> x 2

This leaves the user with a single option to close and Synchronize or Abort. If three ~ are used it will basically FORCE the synchronization without leaving any option. But I experienced an endless loop during the development phase and decided to leave everyone with this one way out. At least until I’m sure of how I want it. The only way to stop it was to Kill the process using the task manager then reopen it in administrator mode. Most of my users are very cooperative and don’t even try to go against the flow. But if those few who like to explore get themselves on the list I’ll have to slam that door shut too.

I posted this so that I’d never loose the instruction and if this has helped anyone else, be sure to give credit where credit is due, thanks bunches

Any and All Feedback, or improvement ideas are welcome..

Cheers!

Goh Diamond
AccessGearHeadExtraordinaire

 

[Steve5]

Your not going to like my answer -

I would transfer all the data to a SQL server back-end and then create a new front-end with access, which would have all the users working from the same data source. Create Shortcuts on each users desktop to the Access db with permissons setup on who has access to what interface. The interface would then control who can change what records.
This idea may seem extreme, but it would be your best solution to your problem, while eliminating your sync issues. Then everyone is working from the same data and make's it much easier to maintain and back-up night with SQL Severs built in features.
SQL server 2000 is the easiest to learn and through ODBC connections from Access to SQL server you will not even feel the difference in performance.

Just a thought ---

 

[GohDiamond]

Thanks,

I don't have permission level to use our SQL server, sorry.

But as I mentioned there is an ACCESS Backend on a shared directory/folder making sure all the data is shared from the same source. The replicas are not syncing to the data for update, that part is live. they are synching to the DESIGN MASTER which dictates what forms, queries and reports are available for use and who can look at what.

I'm not planning to have more than one DESIGN MASTER because that would make things more chaotic.

The database is in continuous development by requests from directors and supervisors who have no idea how to create their own reports, forms and queries. Prior to the current configuration everyone had to get out of the database before it can be updated with a new form or report. But now, I can go to the Design Master and create anything, except a Table, then have the users synchronize to the Design Master Front End. This may have been confusing the issue, since I think this is a rather essoteric way to implement Synchronization compared with conventional method and thought.

Thanks for your input!

Goh

 

[RuralGuy]

I believe if you simply use a utility like this from Bob Larson, you would not need the replication. There are others available as well.

 

[dfenton]

The replicas are not syncing to the data for update, that part is live. they are synching to the DESIGN MASTER which dictates what forms, queries and reports are available for use and who can look at what.

Jet Replication does not work reliably in the long run for Access objects (forms/report/etc.) so should never be used for front ends at all.

A solution like Tony Toews's front end updater would make this a piece of cake:

http://www.granite.ab.ca/access/autofe.htm

I've used it in many situations and it works just great.

--
David W. Fenton
David Fenton Associates
http://dfenton.com/DFA/