Make Back End available off line

[Guest]

Just exploring here.

Windows XP has the option of making server files available off line and
syncs on the next connection to the server. This is suefule for all Office
Suite files except Access2003 files which could not be synchronised usinmg
this method.

I note that A2007 file in a folder marked for availability off line are also
synchronised - stumbled onto this in error.

I am wondering if this would have the same effect as creating a replica and
if there are any reasons not to use this method to make an A2007 BE available
off line on a laptop. Anyone aware of any material I could read?

 

[Guest]

Hi Tom

This is a copy of an answer I gave a while ago which should do the trick for
you. Try it on a COPY of the DB 1st.

Once you have up-ed the file to the stick just run the same code (obviously
different paths) for the stick tp the laptop. Of course you can do without
the stick and just do a Server - to - laptop without any middle stages The
code will still work.


______________________________________

I can’t imagine that I am the only IT type to have ever come across this
situation. We have an access system running office in 5 cities from one
backend server. The in-office terminals all run FE.


But
We also have a large number of laptops used by field staff. Due to some
peculiar habits of the field staff's use of the internet ?????, I (as the IT
dept) do not want them “ever†connecting to the in-office system. But of
course any changes I make to the BE will occasionally need placing on the
laptops as a stand alone DB. Oh yes – when field staff leave the company I
reload XP and any other programmes from the original disks – just to be sure
the laptops are clean of bugs.


So how to do this. Of course there is always windows explorer which really
would work fine – but it is time consuming. You can use import / Get
External Date / CreateObject / CopyFile / etc etc etc. But non of these seem
to work very well (this is almost certainly down to me just being crap).


Anyway, here is a solution that you can use if you have the same situation.
BUT BUT BUT


DO NOT use a Kill code unless you know what you are doing or you will be
(really) sad. Once it’s gone (your database) it’s gone ??
________________________________


Simply create a form and a button on it and use this OnClick.


Private Sub ButtonName_Click()
Dim strOldPathName As String
Dim strNewPathName As String


'Delete the old file'
Kill "C:\Documents and Settings\My Documents\DBName.mdb"


'Copy external database to local drive'
strOldPathName = "E:\DBName.mdb"
strNewPathName = "C:\Documents and Settings\My Documents\DBName.mdb"
DBEngine.CompactDatabase strOldPathName, strNewPathName


End Sub


E:\ is a DataStick but this works from/to any drive. Not too sure about
CD/RW or CD/R though.
I have used compact DB at the end as I feel it needs it (not for any other
reason) so this is up to you. Change the path names to what they are and you
up and running.
________________________________
It may be that you want to check that your DB really has transferred across
before you start using it so in this case you could import and use any name
then after you’re happy with it, simply rename the new DB (run the kill code
just before the rename or it will not work)


'Rename the copied DB to another name'
Dim OriginalName, As String
Dim NewName, As String
OriginalName Name = "C:\Documents and Settings\My Documents\DBName.mdb"
NewName = "C:\Documents and Settings\My Documents\SomeNewNameHere.mdb
"Name OriginalName As NewName


Note I have note included a check to see if the DB exists on the laptops as
it always does but you could include this if you feel it ness.


Have fun

 

[Guest]

Just re-read your post and I missed of some of the code that you may need.
Feel free to use the code or bits of it I you want.

In my case each laptop has 2 DB’s. NewVentureMDB and MerchantMDB

I need to ensure that the latest data is available to field staff (they use
the MerchantMDB to make sales). As you can see the code below checks the
NewVenture exists – if it does it deletes it. Then a new copy of NewVenture
is imported. The 3 tables that are needed in Merchant tblBookings –
tblClients - tblEvents are deleted then imported from the new copy of
NewVenture. It’s not really the DB, I need to import. It’s that data in 3
tables. (and relationships need to stay in place).

Hope that makes sense. I have done this to ensure the field staff always
have the most up to date bookings details. - You can’t sell anything to
someone who doesn’t exist ??

frmMerchanttimerWait s just a popup that runs for 15 seconds to tell them to
leave things alone.

This is copied from a form on a test laptop so you will need to change the
names and paths to what you need.



Private Sub cboImport_Click()
'Open form with timer event close to tell staff not to do anything'
DoCmd.OpenForm "frmMerchanttimerWait", acNormal, "", "", , acNormal

'Check NewVenture.MDB exists'
Dim strOldPathName As String
Dim strNewPathName As String
myfile = Dir("C:\Documents and Settings\UK Outdoor Pursuits\My
Documents\UKOP_stuff\NewVenture.mdb")

'If it does delete it'
If myfile <> "" Then
Kill "C:\Documents and Settings\UK Outdoor Pursuits\My
Documents\UKOP_stuff\NewVenture.mdb"

'Then reimport NewVenture.MDB'
strOldPathName = "E:\NewVenture.mdb"
strNewPathName = "C:\Documents and Settings\UK Outdoor Pursuits\My
Documents\UKOP_stuff\NewVenture.mdb"
DBEngine.CompactDatabase strOldPathName, strNewPathName

Else
'If it didn't exist then import NewVenture.MDB from E drive onto C drive'
strOldPathName = "E:\NewVenture.mdb"
strNewPathName = "C:\Documents and Settings\UK Outdoor Pursuits\My
Documents\UKOP_stuff\NewVenture.mdb"

End If

'If tables exisit - delete them'
Dim db As Database, tdf As TableDef
Set db = CurrentDb()
For Each tdf In db.TableDefs
If tdf.Name = "tblBookings" Then
DoCmd.DeleteObject acTable, "tblBookings"
End If
If tdf.Name = "tblClients" Then
DoCmd.DeleteObject acTable, "tblClients"
End If
If tdf.Name = "tblEvents" Then
DoCmd.DeleteObject acTable, "tblEvents"
End If

'Reimport tables from newly imported DB on C drive'
Next
DoCmd.TransferDatabase acImport, "Microsoft Access", "C:\Documents and
Settings\UK Outdoor Pursuits\My Documents\UKOP_stuff\NewVenture.mdb",
acTable, "tblBookings", "tblBookings", structureonly:=False

DoCmd.TransferDatabase acImport, "Microsoft Access", "C:\Documents and
Settings\UK Outdoor Pursuits\My Documents\UKOP_stuff\NewVenture.mdb",
acTable, "tblClients", "tblClients", structureonly:=False

DoCmd.TransferDatabase acImport, "Microsoft Access", "C:\Documents and
Settings\UK Outdoor Pursuits\My Documents\UKOP_stuff\NewVenture.mdb",
acTable, "tblEvents", "tblEvents", structureonly:=False

End Sub


Hope all this helps.

-
Wayne
Manchester, England.

 

[Guest]

Thanks, Wayne.

If I am reading your code correctly, it will delete a file and copy a
replacement onto the laptop.

This is not really what I am asking to do. I am not an IT specialist, I just
know that I can take my files home by Rt Click and select "Make Available Off
Line"

If the DB were a Word document, any changes to the off line copy would be
synchronised to the server copy the nexst time the laptop was on the network.
This facility was not available for A2003 files - for some reason windows
would not synchronise these or make them available off line.

Because of this, I made a point of keeping all DB's (FE and DB) out of
folders marked for synchronisation with the server and availability off line.
I recently synchronised an off line folder which contained an A2007 file and
it appears to have synchronised all the objects and data. (Data captured
since the last laptop synch by users on the server BE now appears on the
laptops off line copy of the BE - this is something that was only possible
with Replicas in A2203.

 

[Guest]

In this case it may be an idea just to have a backup CD and use scheduler to
run this each day.

 

[David W. Fenton]

I made a point of keeping all DB's (FE and DB) out of
folders marked for synchronisation with the server and
availability off line. I recently synchronised an off line folder
which contained an A2007 file and it appears to have synchronised
all the objects and data. (Data captured since the last laptop
synch by users on the server BE now appears on the laptops off
line copy of the BE - this is something that was only possible
with Replicas in A2203.

Is this an ACCDB or an MDB? Are you certain it wasn't converted to
replication?

 

[Tony Toews [MVP]]

Windows XP has the option of making server files available off line and
syncs on the next connection to the server. This is suefule for all Office
Suite files except Access2003 files which could not be synchronised usinmg
this method.

I note that A2007 file in a folder marked for availability off line are also
synchronised - stumbled onto this in error.

I am wondering if this would have the same effect as creating a replica and
if there are any reasons not to use this method to make an A2007 BE available
off line on a laptop. Anyone aware of any material I could read?

The reason for using replication is that Access then keeps track of
new, updated and deleted records on the various MDBs.

Without using replication what happens when an MDB is updated on the
server and on your computer? Which one is the latest? Offline files
work by tracking the entire file, not changed records.

Thus one of two things will happen when you re-attach to the server.
Any changes made on the MDB on the server will be lost or all changes
you made to your MDB will be lost.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[David W. Fenton]

The reason for using replication is that Access then keeps track
of new, updated and deleted records on the various MDBs.

Without using replication what happens when an MDB is updated on
the server and on your computer? Which one is the latest?
Offline files work by tracking the entire file, not changed
records.

Thus one of two things will happen when you re-attach to the
server. Any changes made on the MDB on the server will be lost or
all changes you made to your MDB will be lost.

The functionality described sounds very much like the old Briefcase
Replication, which was really only a solution for a single user.

 

[Tom Wimpernark]

if you want to support laptop users u should download MSDE or SQL Express

 

[Guest]

The functionality described sounds very much like the old Briefcase

Replication,

"When a file with the .mdb extension is dragged into Briefcase, the
Briefcase reconciler code is called to convert the database into a
replicable form. ... leaves the Design Master at the source, and places a
replica in the Briefcase... "

http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccess/html/msdn_replicat.asp

(david)

 

[Tony Toews [MVP]]

"When a file with the .mdb extension is dragged into Briefcase, the
Briefcase reconciler code is called to convert the database into a
replicable form. ... leaves the Design Master at the source, and places a
replica in the Briefcase... "

http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccess/html/msdn_replicat.asp

Yeah, that's what I thought happened but I didn't want to bother doing a search.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[David W. Fenton]

Yeah, that's what I thought happened but I didn't want to bother
doing a search.

Briefcase is de facto deprecated by MS, since it's no longer
installed by default. The documentation has not been updated since
Win98 or so.

Briefcase is basically worthless for replication work, seems to me.