[AccessD] Is Access a Bad Program?

Jim Lawrence accessd at shaw.ca
Tue May 3 15:29:21 CDT 2011


Just a note; when I was describing this I might not have emphasized that
each copy of MS Access, on each station should be a different license. This
has nothing to do with FE or BE applications.

I personally like to use MS Access for just its unsurpassed presentation
capabilities but prefer to secure most other functionality on a reliable MS
SQL server which immediately gives unsurpassed security and extended remote
capabilities as well. And then can lead to the eventual migration to a web
interface. 
  
I use ADO-OLE connections for Access to MS SQL and simply had no idea that
multi-level transaction management was even possible with basic Access. Show
me some code. 

" And I think you can create SQL Server Reports in SQL Server Express 2008
R2 " ...that's good to know.

I have had a few issues on sites but only ones that have matched the site
setup as described. (Or is built on a MS SBS with real-time station desktop
management turned on... this is where the desktops are stored under each
user profile, on the server and is being updated on regular intervals. When
the system is under load it results in the bound FE to time-out and
disconnects from its BE...very ugly.)

Jim



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Dan Waters
Sent: Tuesday, May 03, 2011 12:37 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Is Access a Bad Program?

Jim,

I have to step in and disagree with you (I think).  

I, and many other people on this list, manage systems that have multiple
Access applications (clients) using table links all to the same Access
database (server).  An Access application is an .mdb file which contains
table links (usually), forms, reports, modules, macros, and queries.  An
Access database is tables only.  Only very rarely have I experienced
corruption in this type of system, and only when the server was having
problems.

If you're discussing having multiple people using the same Access app (FE)
at the same time, there's no need for that discussion.  It won't work, and
if Access is set up correctly it won't be set up that way.

For SQL Server, with an Access app, you would not put reports, modules, and
extended coding there.  Tables, stored procedures, views, UDF's - yes.
Also, an Access app does do transactions, and rather elegantly, so I use
them where appropriate.  And I think you can create SQL Server Reports in
SQL Server Express 2008 R2.

If what you are describing is actually happening, they you're using Access
incorrectly in some way, or on a network with poor hardware, or on a badly
configured network.  

Dan


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence
Sent: Tuesday, May 03, 2011 1:50 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Store accdr File Locally or on a File Server?

Hi John:

I agree with you observations. 

The BE will always corrupt if used by multiple users, but only in certain
cases. The most common scenario is if there is more than one copy of MS
Access base running on the network. If the FE modules are then bound to the
BE, their activities are tracked by a couple values. One of those values is
MS Access's product code number, so the lock file system does not
distinguish between the connections of two or more FEs and you can see where
that is heading. Unexplained crashes and data corruption.

Of course a person could try to simply use an unbound set of forms but that
will also lead in to another group of problems as a MDB database will be
with any real record locking or protection against multiple users access of
the same record. That again can lead to data lost and record corruption of
which you have so eloquently described over the years.

Now if we move to a real SQL BE, those issues are moot. These ACID
(atomicity, consistency, isolation, durability) BE databases handle all
multi-users using multi-records or even the same records all without any
problems. Most of the business logic (queries, reports, modules and extended
coding etc.) can be stored there as well. And then the ability to handle
transactions is an awesome feature beyond anything designed in an MDB. The
final reason for not migrating to MS SQL is also a non-issue as a Free MS
SQL Express is there for all to use. (Note: MS SQL Express does not have
report building capabilities but a reports can built in its big brother and
imported.)

Jim

 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Tuesday, May 03, 2011 3:44 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Store accdr File Locally or on a File Server?

Jim,

I know that you likely understand all of these issues, I am just writing
them up for the benefit of any of our list members who might not.

I see from this email that you are specifically discussing a "script:" and
thus are discussing a "copy to local drive" system.

If you were discussing actually using the FE from the RAM disk, the problems
are as follows:

1) Access doesn't load "the *entire* file" or "the *entire* FE", it loads
whatever piece it is using at this instant.  For example it may open a
switchboard and some code modules to get started. 
Access thus asks the file store to go get specific pieces of the file which
are those pieces.  Later the FE is running and the user opens a form.  DAO
asks the file store to go get the specific pieces of the file which are the
form, and perhaps a stored query etc.  In order to do all of this, Access
creates the LDB (the lock database) and holds the LDB open with entries for
itself, for as long as Access is working in the FE.  IOW it "locks" the FE
(or pieces).

It is quite possible for multiple users to hold locks in the LDB at the same
time, this is what happens when the users access the BE.  While this does
work, the problem is threefold.

1) temp tables in the FE suddenly become multi-user
2) Writes to the FE (rare, I understand) can corrupt the file if the write
is interrupted (bad nic / cable / user)
3) Because of the locks, it is impossible to update the FE with a new
version if anyone is in the FE.

OTOH if you are just discussing loading the FE from the ram disk into a
directory in the local disk and opening it from there, all of these problems
disappear.

I have a client where something happens on a regular basis to corrupt the
BE.  We have never identified what the problem is but in such cases, you
would likely end up with corrupt FEs as well.

John W. Colby
www.ColbyConsulting.com

On 5/3/2011 12:00 AM, Jim Lawrence wrote:
> Hi John:
>
> Hmmm, I see your concerns.
>
> The FE is not running from the RAM disk it is just loading from it and
that
> is quite a different thing. In most cases the FE, loads once a day,
maximum.
> The actual package runs on the individual stations. Of course the FE 
> is locked on the server when someone is working on it and so it should be.
>
> I have had this working for years, on a product that was originally
designed
> in Access97 so that dates it. :-)
>
> The only problem that the script had to resolve was when there are two 
> people trying to grab the FE from the server at the same time. Then 
> the
code
> would wait a couple of seconds and tries again and keeps looping until 
> resolution or until 10 tries and then it fall through and it then fails.
(I
> used a piece of code from the original Norton's command apps called 
> WAIT.) Under normal conditions I have never had a failure.
>
> But for some reason I have never used it with another client...the 
> speed
of
> transfer from today's servers has always been more than acceptable to 
> the clients. It always does take a few moments for the user to 
> actually log on to the MS SQL Server data source anyway.
>
> Jim
>
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: Monday, May 02, 2011 7:11 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Store accdr File Locally or on a File Server?
>
> Not so, if you are talking about loading the FE from the ram disk.  At
that
> point you are sharing
> the Fe which is bad news.  Not to mention that the FE is locked while
anyone
> is in it making it
> impossible to update with the latest version if anyone is in the Fe.
>
> John W. Colby
> www.ColbyConsulting.com
>
> On 5/2/2011 9:55 PM, Jim Lawrence wrote:
>> When loading a crucial file, from the server to all stations and 
>> given
> that
>> the transfer has to be fast, create a shareable RAM disk on the 
>> server (default is drive Z), assign the appropriate drive letter, 
>> make it just a little bigger than size of the FE (allowing room for 
>> expansion and
>> improvements) and load the latest FE into the memory drive.
>>
>> For all the information you may need see the following:
>> http://www.speedguide.net/articles/ramdisk-guide-131
>>
>> When workstations log in, the latest version of the FE is transferred
> almost
>> instantly especially if you have a GBit LAN.
>>
>> This makes the whole issue of whether to store the FE locally a moot
> point.
>>
>> Jim
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list