What is the issue with working with an Access DB in an on-line/off-line environment

[Laphan]

Hi All

I use an Access DB for my VB front-end and 95% of my users are
laptops/desktops linking to a Domain/Win server/Active Directory setup. I
currently have the Access DB and VB app located in the program folder of a
PC, but as many users may use the same laptop/desktop as they login to their
own specific user account I'm looking to move the access db to the user's My
Docs folder (re-directed from the server with off-line access enabled on the
laptops and disabled on the desktops) so that each user can run up the
program, but actually connect to their data file (access db) via the My Docs
folder.

My problem is that .mdb files seem to be classed as 'evil' when it comes to
off-line access and I don't know why. The problem is compounded in tat I've
changed the suffix of my db to .edb so a server will be 'conned' in a way
into thinking it can off-line this file and may cause corruption to the
data - I just don't know.

My idea of moving the data file to My Docs seems a perfect solution (note if
the user account doesn't have the data file there on prog load up then I'll
copy one across), but I'm concerned about the off-line implications.

Anybody got any ideas on this?

Thanks

 

[Robert Morley]

The main problem with MDBs is that they're not server-based databases, so
instead of sending "SELECT * FROM MyTable" across the network and sending
the result-set back, you're actually sending whole chunks of the MDB file
across the network, which is extremely slow.

Synchronization can also be an issue if you're implementing My Docs as a
network-based offline-able folder (which is what I understand you're
doing...correct me if I've misunderstood). MDBs are a little flakey when it
comes to sudden interruptions, and you might well end up with a corrupt
database. And, of course, you could run into problems if somehow the
database is updated on both ends simultaneously, although that seems
unlikely here.


Rob

 

[Supoch]

The main problem with MDBs is that they're not server-based databases, so
instead of sending "SELECT * FROM MyTable" across the network and sending
the result-set back, you're actually sending whole chunks of the MDB file
across the network, which is extremely slow.

Synchronization can also be an issue if you're implementing My Docs as a
network-based offline-able folder (which is what I understand you're
doing...correct me if I've misunderstood). MDBs are a little flakey when
it comes to sudden interruptions, and you might well end up with a corrupt
database. And, of course, you could run into problems if somehow the
database is updated on both ends simultaneously, although that seems
unlikely here.


Rob

 

[Tony Toews [MVP]]

I use an Access DB for my VB front-end

I'm confused. How can you use an Access DB for your VB front end? Or
do you mean that you are using the tables in an Access MDB as the
shared data storage for your VB app?

and 95% of my users are
laptops/desktops linking to a Domain/Win server/Active Directory setup. I
currently have the Access DB and VB app located in the program folder of a
PC,

Ah, so each user has thier own copy of the Access MDB?

but as many users may use the same laptop/desktop as they login to their
own specific user account I'm looking to move the access db to the user's My
Docs folder (re-directed from the server with off-line access enabled on the
laptops and disabled on the desktops) so that each user can run up the
program, but actually connect to their data file (access db) via the My Docs
folder.

My problem is that .mdb files seem to be classed as 'evil' when it comes to
off-line access and I don't know why. The problem is compounded in tat I've
changed the suffix of my db to .edb so a server will be 'conned' in a way
into thinking it can off-line this file and may cause corruption to the
data - I just don't know.

The problem with offline folders and Access MDBs is that the operating
system doesn't know if you are attempting to share the data within the
Access MDB with other users and thus it should use replication.

Now it would appear in your case this isn't a problem. You are not
sharing the data in the MDB.

Anybody got any ideas on this?

However I'm not sure how to tell the OS that you do want to use
offline folders and MDBs without using replication.

Possibly this will give you enough of an idea that some searching will
find you an answer.

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/

 

[Tony Toews [MVP]]

The main problem with MDBs is that they're not server-based databases, so
instead of sending "SELECT * FROM MyTable" across the network and sending
the result-set back, you're actually sending whole chunks of the MDB file
across the network, which is extremely slow.

Define "whole chunks". Your statement could be interpreted to mean
massive chunks of MDB or little chunks of MDB. In fact it's little
chunks of MDB.

The appropriate 4kb pages of the MDB containing the indexes and the
data are sent down the network cable. Now Access is quite "chatty"
compared to other products such as SQL Server. Of course if your
selection criteria isn't indexed then yes the entire table is sent
down the network cable.

But Access does not generally move large chunks down the network
cable.

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/

 

[Robert Morley]

The appropriate 4kb pages of the MDB containing the indexes and the

data are sent down the network cable. Now Access is quite "chatty"
compared to other products such as SQL Server. Of course if your
selection criteria isn't indexed then yes the entire table is sent
down the network cable.

While that's true, there's a lot of other data/metadata involved in there,
such as finding the appropriate table or query, possibly looking up field
properties for display or data-entry purposes, and other such metadata.
Then, of course, there's record-locking data to consider.

Access is great at being a local database, but having experienced it in a
network environment, it's not something I'd care to repeat.



Rob

 

[Ralph]

While that's true, there's a lot of other data/metadata involved in there,
such as finding the appropriate table or query, possibly looking up field
properties for display or data-entry purposes, and other such metadata.
Then, of course, there's record-locking data to consider.

Access is great at being a local database, but having experienced it in a
network environment, it's not something I'd care to repeat.

That's because it is a file-based database. One has identical problems if
you went about trying to share any file or document across a wire.
Considering all the issues - Jet and mdb is very much the classic dancing
bear - it isn't how well it dances, it's the fact it can dance at all. <g>

-ralph

 

[Tony Toews [MVP]]

While that's true, there's a lot of other data/metadata involved in there,
such as finding the appropriate table or query, possibly looking up field
properties for display or data-entry purposes, and other such metadata.
Then, of course, there's record-locking data to consider.

Sure. All that too.

Access is great at being a local database, but having experienced it in a
network environment, it's not something I'd care to repeat.

Access works reasonably well in a network environment. Sure, not as
good as SQL Server but it does work well.

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/

 

[Tony Toews [MVP]]

Considering all the issues - Jet and mdb is very much the classic dancing
bear - it isn't how well it dances, it's the fact it can dance at all. <g>

<chuckle>

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/

 

[Robert Morley]

I've had no problems when it's a single-user database. The problems (at
least in the environment I was using it in) started when we had multiple
users over a 10 Mbps connection. Strangely for a tech company, our
connection speed was very far behind the times as little as a few years ago.

And of course in ANY network environment, MDBs don't cope very well with
unexpectedly dropped connections.


Rob

 

[Laphan]

Hi Guys

Many thanks for all of this valuable info.

What I need to ascertain is am I going down the right or wrong route by
putting the DB in the user's My Docs and working between app in PC's program
files folder and database in user's My Docs folder.

I can't see how I can do it any other way. At the mo, I have my DB and app
in the program files folder on the PC and everybody keeps telling me this is
the wrong way of doing it. The database/data file has been an issue for me
in that when a new user logs on after a previous user the previous data is
obviously still there and knowing my users they'll simply obliterate this
data for their own sake!!

Just to clear up how I'm using the Access DB, yes I'm using it purely as a
data store. All data is queried using SQL queries and displayed /
manipulated by the VB app. I just think is such a great way to work when
needing to store user data.

And although I talk about various users working from different machines,
this is just to open up the versatility of Active Directory in that the user
can work from machine to machine. I suppose I'm saying it's one user per
database.

My only problem is this flaky aspect of the on-line / off-line connectivity
of, in my case, teacher laptops.

I suppose at the end of the day, I've just got to try it out in my
environment and see how I get on. Would just be nice if somebody had gone
on this journey before to give me a bit of guidance.

Thanks

I use an Access DB for my VB front-end

I'm confused. How can you use an Access DB for your VB front end? Or
do you mean that you are using the tables in an Access MDB as the
shared data storage for your VB app?

and 95% of my users are
laptops/desktops linking to a Domain/Win server/Active Directory setup. I
currently have the Access DB and VB app located in the program folder of a
PC,

Ah, so each user has thier own copy of the Access MDB?

but as many users may use the same laptop/desktop as they login to their
own specific user account I'm looking to move the access db to the user's
My
Docs folder (re-directed from the server with off-line access enabled on
the
laptops and disabled on the desktops) so that each user can run up the
program, but actually connect to their data file (access db) via the My
Docs
folder.

My problem is that .mdb files seem to be classed as 'evil' when it comes to
off-line access and I don't know why. The problem is compounded in tat
I've
changed the suffix of my db to .edb so a server will be 'conned' in a way
into thinking it can off-line this file and may cause corruption to the
data - I just don't know.

The problem with offline folders and Access MDBs is that the operating
system doesn't know if you are attempting to share the data within the
Access MDB with other users and thus it should use replication.

Now it would appear in your case this isn't a problem. You are not
sharing the data in the MDB.

Anybody got any ideas on this?

However I'm not sure how to tell the OS that you do want to use
offline folders and MDBs without using replication.

Possibly this will give you enough of an idea that some searching will
find you an answer.

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/

 

[Ralph]

Hi Guys

Many thanks for all of this valuable info.

What I need to ascertain is am I going down the right or wrong route by
putting the DB in the user's My Docs and working between app in PC's program
files folder and database in user's My Docs folder.

I can't see how I can do it any other way. At the mo, I have my DB and app
in the program files folder on the PC and everybody keeps telling me this is
the wrong way of doing it. The database/data file has been an issue for me
in that when a new user logs on after a previous user the previous data is
obviously still there and knowing my users they'll simply obliterate this
data for their own sake!!

Just to clear up how I'm using the Access DB, yes I'm using it purely as a
data store. All data is queried using SQL queries and displayed /
manipulated by the VB app. I just think is such a great way to work when
needing to store user data.

And although I talk about various users working from different machines,
this is just to open up the versatility of Active Directory in that the user
can work from machine to machine. I suppose I'm saying it's one user per
database.

My only problem is this flaky aspect of the on-line / off-line connectivity
of, in my case, teacher laptops.

I suppose at the end of the day, I've just got to try it out in my
environment and see how I get on. Would just be nice if somebody had gone
on this journey before to give me a bit of guidance.

<snipped>

As for locations take a look at the Help article "CSIDL Values" and "Special
Folders". (In Vista they become "known folders")
CSIDL_APPDATA and CSIDL_COMMON_APPDATA looks like what you need.

But this

 

[Tony Toews [MVP]]

I've had no problems when it's a single-user database. The problems (at
least in the environment I was using it in) started when we had multiple
users over a 10 Mbps connection. Strangely for a tech company, our
connection speed was very far behind the times as little as a few years ago.

But that may have been more a cheap network environment.

And of course in ANY network environment, MDBs don't cope very well with
unexpectedly dropped connections.

Yes, that MDBs don't. Which is why we strongly urge folks to not use
MDB BEs with wireless networks.

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/

 

[Tony Toews [MVP]]

What I need to ascertain is am I going down the right or wrong route by
putting the DB in the user's My Docs and working between app in PC's program
files folder and database in user's My Docs folder.

As Ralph stated look at the All users\Application Data folder for the
data.MDB. If all multiple users of that system can run your app then
the same folder for the FE. Otherwise put the FE in the
users\Application Data.

I can't see how I can do it any other way. At the mo, I have my DB and app
in the program files folder on the PC and everybody keeps telling me this is
the wrong way of doing it.

The biggest reason for not doing this though is that regular users
don't, and shouldn't, have update access to the Program Files folder.
Only admins with install privileges should. Regular users can read
from that folder though so that's where the Office/Access/Access
runtime executables go.

My only problem is this flaky aspect of the on-line / off-line connectivity
of, in my case, teacher laptops.

Wireless? If not what's flaky about them?

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/