Synchronizing through code locks up database for editing

Synchronizing through code locks up database for editing

Post by leickld » Sun, 14 Jan 2007 04:58:13

I have about 5 laptops, all with a front and back end database on their
hard drives. The central server has the main back end database as well
as the deisgn master on it.

When users login to the front end database on their harddrives, they
have two options: login remotely (don't synchronize) and login from
office (synchronize). The problem is that when they login from the
office and synchronize, parts of the database "lock up". The two parts
that lock up are:

1. Local front-end tables (unlinked to the back-end) that aren't
replicable cease to work properly. These are temporary tables that are
generated frequently and on the fly from procedures within the
front-end. The data WILL build into the table correcly, but it won't
requery in forms without multiple requery attempts. When users login
without synchronizing, everything works fine. When they login and
synchronize, these tables/requeries of their data start malfunctioning.

2. I can't make design changes after synchronizing, I get an error
message that I don't have exclusive use of the database - even though I
do my design changes at night when no one else is logged in.

The data does synchronize OK, and I don't get any error messages. I
just get these 2 "lock-out" malfunctions after synchronizing.

Here is my synchronization code:

Public Function synch()
Dim LocalDataSet As Database
Set LocalDataSet = OpenDatabase("C:\Database\Bsysdata.mdb")
On Error GoTo err_synch
LocalDataSet.Synchronize "\\HPPavilion\database\Bsysdata.mdb"
MsgBox "Local Server Detected..." & vbCrLf & vbCrLf & _
"Data Synchronized Successfully", vbOKOnly
Exit Function

MsgBox "Local server not detected..." & vbCrLf & vbCrLf & _
"Please synchronize when you are connected to the network",
End Function

Any help is GREATLY appreciated!

Synchronizing through code locks up database for editing

Post by David W. F » Sun, 14 Jan 2007 13:13:14

leicklda" < XXXX@XXXXX.COM > wrote in

Temporary tables shouldn't be in the front end, but in a separate
temporary mdb linked to the front end. That will mean that your
front end won't bloat, and it might fix the issue.

I assume your front end is not replicated.

Are you not waiting until the synch finishes before allowing the
user to work in the app?

Sounds like an LDB file is not getting deleted. There are two
potential causes for this that I can think of:

1. users don't have appropriate permissions on the folder where the
database you're trying to change is located.

2. the database variable in your app that is used for the synch
isn't being de-initialized. You may want to try the two
possibilities for which replica you use for which. When you do this:

Set db = DBEngine.OpenDatabase("[local back end"]

db.Synchronizer "[remote back end"]

Set db = Nothing

It may be that there's an implicit lock on the remote db that is not
released when the code finishes running, so, instead, try it in the
opposite direction:

Set db = DBEngine.OpenDatabase("[remoted back end"]

db.Synchronizer "[local back end"]

Set db = Nothing

That would mean you've explicitly closed and cleared the connection
to the remote back end.

Of course, if you're not closing your db variable and setting it to
Nothing, that could be the problem.

And if you're using JRO for your synch, I'd suggest you abandon it
and change to DAO, as above, since DAO is native to Jet and JRO is
just an add-on necessitated by MS's insistence on replacing DAO with
the non-native ADO, and MS now deprecates the use of ADO and its
ugly step-children (like JRO) in favor of DAO for use with Jet data.

Try switching the two.

Secondly, you really oughtn't be synching with the design master, in
any event. You should use the DM *only* for DESIGN, and not as your
end point for regular synchs. Doing that would remove the editing
problem entirely without having to experiment with code.

David W. Fenton
usenet at dfenton dot com