List of Access XP bugs and not to do's?

[Adam]

Hi All

I've been using Access XP for a little while now and picking it up sure
was a big learning curve. I've got round the basics, and worked on
understanding good database design and normalization, however when
working on big databases I seem to always find bugs or glitches.
When looking for solutions on the newsgroups, I then find out that
perhaps I should not have done something in a particular way, due to a
long standing issue...

Now I know a lot of this will come with experience, but its such a work
stopper when you've made your design and centre its working around the
chosen way of doing things, only to find out its not possible, or wise
once you've made it all!

Is there a list of do's and don't's anywhere?

Why are glitches left in Access for so long? Like the memo field
corruption etc? The need to compact the databases regularily?

I struggle to understand how such a commercially used application still
has these glitches in.

Many Thanks for any pointers you can give,

Adam

 

[BruceM]

For a succinct list of do's and dont's you should check the 10 Commandments
listed here:
http://www.mvps.org/access/
I have included the general link because there is a lot of other useful
information as well.
Allen Browne's fine web site has some specific information about Access
bugs, as well as a lot of useful tips. You may find that some of your
perceived bugs and glitches are not that at all.
http://allenbrowne.com/tips.html
Why do you regard the need to compact as a "glitch"?

 

[Adam]

Well do you need to compact SQL servers all the time?

 

[Bill Mosca, MS Access MVP]

The memo corruption is much less frequent if you have all the Office updates
installed. In fact, I haven't had a problemm with that in a few years.

As to compacting, that is not a glitch. It is needed with all databases,
even SQL Server. Pages become fragmented after deletions. That fragmentation
does not just go away. Compacting is part of maintenance.

Aside for page fragmentation, another cause of file fragmentation is
creating and deleting forms and reports during development. Regular
compacting during development is a very good idea.

 

[Bill Mosca, MS Access MVP]

Yes, but they can be compacted (shrunk) automatically.

 

[James A. Fortune]

Hi All

I've been using Access XP for a little while now and picking it up sure
was a big learning curve. I've got round the basics, and worked on
understanding good database design and normalization, however when
working on big databases I seem to always find bugs or glitches.
When looking for solutions on the newsgroups, I then find out that
perhaps I should not have done something in a particular way, due to a
long standing issue...

Now I know a lot of this will come with experience, but its such a work
stopper when you've made your design and centre its working around the
chosen way of doing things, only to find out its not possible, or wise
once you've made it all!

Is there a list of do's and don't's anywhere?

Access newsgroups and Microsoft's websites have a lot of information.
Jeff Conrad has an archivist's talent for organizing where to find this
kind of information. His links also include websites by many capable
individuals that contain useful tips and techniques.

Try:

http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html

Why are glitches left in Access for so long? Like the memo field
corruption etc? The need to compact the databases regularily?

I struggle to understand how such a commercially used application still
has these glitches in.

Many Thanks for any pointers you can give,

Adam

I remember reading in a fiction book, perhaps one of the Hitchhiker's
Guide books by Douglas Adams, about a company that created a perfect
product that never broke. They went out of business. I guess there are
some Adams fans at Microsoft .

James A. Fortune
(e-mail address removed)

 

[dbahooker]

#1 - don't use MDB it doesn't scale. it's not reliable. it has locking
problems. it makes you use workarounds for everything. ADP is a
superior technology because it is more scalable.

I strongly agree with you; Access MDB is not a viable, practical,
reasonable choice.

SQL Server wins the battle; it is the same price.
And Access Data Project is a great solution.. it allows you to reuse
your existing forms and reports


-Aaron Kempf
ADP Nationalist

 

[aaron.kempf]

listen FUCKNUT you mean to say that they _ARE_ compacted automatically
right?

not that they CAN BE-- which implies development-- but it IS
COMPACTED/SHRUNK on an automatic basis; without any intervention.

-Aaron

 

[larrylinsonjr]

the 10 commandments from a MDB dork?

who cares; MDB is obsolete

Larry Linson Jr

 

[aaron.kempf]

much less frequent is STILL TOO OFTEN

fucking simpletons

Use SQL Server and Access Data Projects

-Aaron

 

[Adam]

I work in a large organisation, and they have policies imaged across
all of the PC's within the company. Office updates are not run, and it
would not be possible for me to update each users PC as I do not have
the appropriate access.

The Office product is on version 10 or 11 now? I am unsure and
frustrated as to how memo corruption is still in such a product?

Granted, I've learnt not to use memo fields now as I seem to start to
see corruption appearing, while I may have less than 8 users using the
database each day.

I have it split etc, as I've read the commandments on the MVP website,
but it is at the stage where I need to leave my PC on overnight so that
I can have a scheduled task compact and repair the database for me each
evening.

SQL and ADP is not viable in this case.

The http://allenbrowne.com/tips.html is very useful from what i've
seen so far. Guess its just a suck it and see to approach needed to see
what I need to learn in terms of work-arounds and best practice guides.

Regards,

Adam

 

[Adam]

He he, I would hope that MS didn't use this policy and that they would
better there product by adopting newer technologies, rather than
leaving a need for workarounds!

Imagine that google brought out Google Databases!

 

[aaron.kempf]

SQL/ADP is not viable?
why not?

well if ADP isn't viable and MDB isn't reliable; then you're shit outta
luck you better rewrite it in PHP/mySql.

I mean seriously here.
you got 2 choices. Move it to ADP or rewrite it completely.

I could migrate any of your super-duper complex MDB apps in probably an
hour to ADP
and faster performance, better maintenance


Microsoft has not been supporting the MDB format for about a decade
now; long-standing bugs never get fixed and this is the most profitable
division at Microsoft.

Sure makes me wish that they had some competition because Microsoft is
doing a shitty ass job of making Access usable if 'ADP is not viable'
and 'MDB is not reliable'

MDB is for retards and lepers; it has been out of style - because of
reliability problems- for a decade now


-Aaron

 

[Dirk Goldgar]

Granted, I've learnt not to use memo fields now as I seem to start to
see corruption appearing, while I may have less than 8 users using the
database each day.

If you're seeing frequent corruption, something is very wrong. You say
the database is split? Each user has his own local copy of the
front-end? Is your network stable? Do you have users who just turn off
their PCs while Access is updating?

 

[Adam]

Yes, kind of. I have one back end, and a front end for each user,
however rather than the frontend on each of the users PC, i have it in
a folder on the network, and put a shortcut to that on each users PC's.

I know that the network connection drops quite regularily, which maybe
the cause of the problem, but this is out of my hands.

 

[Dirk Goldgar]

Yes, kind of. I have one back end, and a front end for each user,
however rather than the frontend on each of the users PC, i have it in
a folder on the network, and put a shortcut to that on each users
PC's.

Not the best arrangement, since you're still transferring both data and
design across the network, and have two different files that can become
corrupted. It should insulate the data file from front-end corruption,
though. Still, you'd do better to have the front-ends on the users'
PCs.

I know that the network connection drops quite regularily, which maybe
the cause of the problem, but this is out of my hands.

I'd guess that is the cause. Access/Jet may not be the best tool under
the circumstances, since Jet is a file-server database and normally
expects to have uninterrupted access to the .mdb file. However, if you
write your application carefully, as if you were using a client/server
DB like SQL Server, bringing only the bare minimum of data across the
network to be edited and sent back, I think you'll be much less
corruption prone.

If you've already done that and are still experiencing frequent
corruption, you should consider using a c/s back-end like SQL Server.
That doesn't necessarily mean you have to pay big bucks for the
enterprise version of SQL Server -- Access ships with the SQL Server
Desktop Engine (or whatever they're calling it these days), and that may
serve your needs just fine.

 

[Bill Mosca, MS Access MVP]

Adam

Your current split setup is useless. You still have a multi-user front end.
Dropped network connections are compounded when you have a server front end.

Each user needs a local copy for a split to be effective. There are several
methods for deploying and maintaining front ends. If you decide to do it,
I'll be happy to give you links to those methods.

Also, setting each front end to compact on close will negate you having to
leave your PC on to run compacting. A weekly or monthly compacting of the
back end should be often enough.

You mentioned that Office updates are not run in your company. They update
operating systems, right? Maybe they just need to be asked to at least
install the service packs. After all, without installing patches, they are
leaving every PC vulnerable to security breaches.

If you are running Access 10 or 11, your corruption probably has nothing to
do with updates. If I remember correctly, Access 2000 was the one that was
released with this bug and was later fixed with SR 1a.

 

[aaron.kempf]

yes.. something IS wrong

the database craps out it's not scalable.. and im tired of rebooting
file servers to release the lock lol

should I just use those Access Data Projects? I had heard that they are
a lot simple for development AND administration

-Aaron

 

[James A. Fortune]

He he, I would hope that MS didn't use this policy and that they would
better there [sic] product by adopting newer technologies, rather than
leaving a need for workarounds!

I think Microsoft wrote the book . Microsoft rightly spends more
money advertising than in developing new software. I think the gamble
I'm taking on with acquiring .NET capability may have been influenced
more by advertising than common sense . But if Microsoft has spent
all that money convincing companies to use .NET, I should leverage those
advertising dollars rather than fight against them.

Imagine that google brought out Google Databases!

From:

http://www.businessweek.com/technol...p+news_top+news+index_businessweek+exclusives

Microsoft CEO Steve Ballmer chats with BusinessWeek editors about Web
2.0's sky-high valuations and the new competition the company faces

Guys who can touch us in multiple places probably matter more than guys
who can touch us in any one place. And actually we don't really have our
big competition from any one company. Any one company, we know how to
compete with. It's alternate business models that we will have to
embrace or compete well with. You give me any enterprise software
company, O.K., and I'll say c'mon. We know how to go do that. We do do
that. And we're really pretty good at it. We haven't gotten any worse at
it. Boom. Boom. Boom. We know how to keep coming.

James A. Fortune
(e-mail address removed)

 

[Larry Linson]

Who cares about comments from an impersonator?

Larry Linson