Access 2003 amazing power, but am I beyond it's capabilities?

[Doug]

I'm amazed with the power of Access. I've built a pretty complex application
w/about 45 tables all pretty normalized, lots of forms and reports. Only at
the very end did i have to code any VBA. I'm *VERY* happy with what I've been
able to do in such a short time and the flexibility is TRULY IMPRESSIVE. But
now I find the system to be more and more unstable with unpredictable crashes
daily, doing simple things like clicking on a combo box. I get "Cannot open
any more databases" errors and now "Query too complex" errors for queries
that have not changed. To be sure I've saddled Access with a lot but i think
it's pretty well designed.
(1) Are there any Access updates to prevent the crashes? (2) Have I've
surpassed what Access can handle? (3) What better technologies exist to
continue my development? (4) What would you do in my situation?

Many thanks, Doug

 

[Sylvain Lafontaine]

Replace Access with SQL-Server as the backend.

For the frontend, two years ago I would have tell you to go with Access Data
Project (ADP); however ADP are now phased out in favor of the .NET Framework
and are probably going into oblivion in the fast lane.

If you are afraid of the step learning curve of the .NET Framework, then you
can go with linked tables, linked views (these are very, very important if
you don't want a very bad performance when using INNER JOIN beetween tables
over the Internet) and SQL-Passthrough. (For linked views with updating
capabilities, take a look a the following article:
http://support.microsoft.com/kb/q209123/ ).

The second option is very easy to set up but is, in my opinion, not the way
of the future; however, many other people in the newsgroup think exactly the
contrary.

S. L.

 

[Steve Schapel]

Doug,

These types of problems should not be occurring. Going on what you have
told us so far, you are still a very long way short of Access's
limitations to handle the job. Sometimes something as simple as this
can help, especially after some intensive development such as you have
undertaken... Make a new MDB file, and import all of the database
objects from your existing application into the new one. Decompile and
recompile your code, compact/repair the database, and then see if you
get the same problems with the new database.

 

[Doug]

Sylvain,

thanks for your reply. I assume you're saying i've exceeded Access
capabilities and a rewrite will be required. That's bad news. Ignorance of
..net would surpass my fear of it. In fact i just learned how to spell VBA
last week. So .net is a C-like language? I write a .net frontend to replace
the Access front end, then? This is a blow must say. I feel unsupported by
Microsoft.

Linked tables i hear uses many table handles and i assume adds to the
"cannot open more databases" problem i already have. I just imported all the
linked tables due to this problem and i notice it is a lot faster. Not more
stable sadly, which was my hope.

So you think it's the database that crashes and not the frontend? Replacing
with SQL Server would allow me to continue the use of my Access frontend and
it would be stable and reliable?
thanks,
Doug

 

[Allen Browne]

Doug, that's a fairly general question. We don't even know what version of
Access you are using.

There are updates available for Access 2000 onwards in the Downloads section
at support.microsoft.com. Be sure to get the JET 4 update as well (currently
SP8).

The issue "Cannot open any more databases" usually involves code that does
not clean up after itself, which includes the domain aggregate functions.
Particularly, if you use these in a query where they are called for each
row, it's not difficult to get this message. A subquery would be much *more*
efficient, but may not be suitable if a read-only result is a problem. If
you tell us a little more about this issue we may be able to offer
alternative suggestions. For example, if you have many instances of the same
combo on a form (e.g. for rostering staff), then it may be possible to set
their RowSourceType to a callback function, so they all draw records from
the same static array instead of loading multiple instances of the same
records.

There are various cuases for "query too complex errors". Unless the query is
actually too complex (e.g. 100 ANDs in the WHERE clause), the message may
indicate that there are ambiguities in the clauses, reserved names, or data
type mismatches. Access (JET) is particularly poor at determining the data
type of calculated fields. Details:
Calculated fields misinterpreted
at:
http://members.iinet.net.au/~allenbrowne/ser-45.html

There are also various things that will corrupt your database and cause it
to crash. For suggestions on how to avoid these, see:
Preventing Corruption
at:
http://members.iinet.net.au/~allenbrowne/ser-25.html
If all the conditions in that articule are met, you can expect a crash in
Access 2003 once every few weeks during development, and once every few
years once the database is complete in use by the end user. Correctly
configured and within particular limits, A2003 is the most stable version of
Access ever released.

What would we do? Follow the suggestions in the article regarding, users,
power, hardware, software, network, configuarion, disk space, service packs,
Name AutoCorrect, LinkChildFields, multi-developer environment, editing in
break mode, subqueries, decompiling, and so on. If you still don't have a
stable setup, post back some specifics.

HTH

 

[Doug]

Steve,
I think that's a terric idea! It makes a lot of sense. I really don't want
to have to start over. How do i de/recompile the code? I 'm a neophyte.

So you think 4 8-way joins unioned together is ok for Access to handle
generally? I don't have high data volume but it is complex. I embed bits of
VB in SQL, getting the thing to generate HTML and tons of other things. I've
used everything MS makes available and am more impressed daily with how much
can be done with it. I hate the thought of having to write actual code - I
don't think COBOL or Smalltalk is in vogue anymore is it?

 

[Steve Schapel]

Doug,

First of all, I would definitely recommend to read and apply the superb
information provided by Allen Browne.

Compile your project by selecting Compile from the Debug menu in the VB
Editor window.
To decompile, I usually go via Start => Run...
"C:\Program Files\...etc...\MSACCESS.EXE" /decompile "C:\MyFolder\MyDb.mdb"
Aftrer this, compile again.

4 8-way joins unioned together...

Not sure whether you mean 8 tables joined in a query, or 2 tables joined
on 8 fields. The need for this arrangement may indicate that there
could be a simpler way to get the same outcome, or it may indicate that
your table design could be improved. Whatever the case (taking into
account Allen's comments about domain aggregate functions), assuming the
union query is correctly constructed, I can see no problem with Access's
ability to handle it.
Performance will be significantly affected by correct indexing of table
fields.

embed bits of VB in SQL

You mean embed bits of SQL in VBA? Anyway, not a problem, assuming it
is done correctly of course.

generate HTML

I have not had experience with doing this, but I have seen Access
applications which successfully do extensive html-related work.

 

[Sylvain Lafontaine]

Doug,

No offence but my answer was based on the assumption that you were a
power user, with already a deep knowledge of Access, SQL and VBA. When I
read something like

« I've built a pretty complex application w/about 45 tables all pretty
normalized, lots of forms and reports. Only at the very end did i have to
code any VBA. »

my first impression was that your knowledge of SQL was so profound that only
occasionally you had to use VBA. This is not exactly the same thing as
saying that you just learned how to spell VBA last week. In my first
answer, I have also taken into account the end of your first message:

« (3) What better technologies exist to continue my development? »

But now, after having read your second message; I will advise you to
forget my first answer and go instead with the suggestions of Steve and
Allen.

S. L.

 

[Tony Toews]

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam

Replace Access with SQL-Server as the backend.

I doubt very much this would help as his problems are with the GUI and
not the backend. Mind you a SQL Server View might help with the
"query too complex" message but there's likely a work around. I've
had that message myself when I was trying to Union about 5 queries.

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 Toews]

So you think 4 8-way joins unioned together is ok for Access to handle
generally?

Yes, I've had the query too complex message when trying to do the same
thing. I ended up creating some temporary tables to store the
preliminary results of my queries.

See the TempTables.MDB page at my website which illustrates how to use
a temporary MDB in your app.
http://www.granite.ab.ca/access/temptables.htm

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

 

[Andi Mayer]

Yes, I've had the query too complex message when trying to do the same
thing. I ended up creating some temporary tables to store the
preliminary results of my queries.

I have a question regarding this.

did you have this sort of problem also if you run it with an execute?
or is it only in the "Design View".

I have this sort of Problem only in the design View, Access is often
telling my when i past a SQL string into to Design-View that it can't
show it and it deltes a view joins (most of the time i can recreate
them in the design View)

BTW: I think I remember that I jount once approximately 25 tables with
a left join with two fields an it worked (it was a little bit slow)


If you expect an answer to a personal mail, add the word "manfred" to the first 10 lines in the message
MW

 

[Sylvain Lafontaine]

You're right, only replacing the backend by using linked tables without any
view or stored procedure will give no help at all for queries to complex.
If I've made the suggestion about SQL-Server, it was because the original
poster asked about better technologies and was also experiencing a daily
increase in instability.

We must also look at the fact that even if many queries to complex Access
can be rewritten under one or another form, this has a cost in term of
development time and performance and that this usually finish by leading to
a situation where you pass much of your time trying to patch the query
engine of Access instead of doing some more usefull work. I had myself
experienced this problem with Access in the past.

S. L.

 

[Tom Wickerath]

Steve,

To decompile, I usually go via Start => Run...
"C:\Program Files\...etc...\MSACCESS.EXE" /decompile "C:\MyFolder\MyDb.mdb"

A shortcut named something like Access Decompile can be created one time, and then used to open
any database. A database that you open with this shortcut will be decompiled. For example, I have
three such shortcuts that I created for my PC, since I have Access 97, 2000 and 2002 loaded on my
machine. I find this a lot easier than typing out the path in the Start => Run... dialog each
time.

Name:
Access 97 Decompile
Target:
"C:\Program Files\Microsoft Office 97\Office\msaccess.exe" /decompile

Name:
Access 2000 Decompile
Target:
"C:\Program Files\Microsoft Office 2000\Office\msaccess.exe" /decompile

Name:
Access 2002 Decompile
Target:
"C:\Program Files\Microsoft Office XP\Office10\msaccess.exe" /decompile


Tom
___________________________________


Doug,

First of all, I would definitely recommend to read and apply the superb
information provided by Allen Browne.

Compile your project by selecting Compile from the Debug menu in the VB
Editor window.
To decompile, I usually go via Start => Run...
"C:\Program Files\...etc...\MSACCESS.EXE" /decompile "C:\MyFolder\MyDb.mdb"
Aftrer this, compile again.

4 8-way joins unioned together...

Not sure whether you mean 8 tables joined in a query, or 2 tables joined
on 8 fields. The need for this arrangement may indicate that there
could be a simpler way to get the same outcome, or it may indicate that
your table design could be improved. Whatever the case (taking into
account Allen's comments about domain aggregate functions), assuming the
union query is correctly constructed, I can see no problem with Access's
ability to handle it.
Performance will be significantly affected by correct indexing of table
fields.

embed bits of VB in SQL

You mean embed bits of SQL in VBA? Anyway, not a problem, assuming it
is done correctly of course.

generate HTML

I have not had experience with doing this, but I have seen Access
applications which successfully do extensive html-related work.

 

[Steve Schapel]

Thanks a lot, Tom, for this good suggestion.

However, I personally don't "type out the path in the Start => Run...
dialog each time". I just select the item I want from the combobox on
the Start => Run dialog.

 

[Tom Wickerath]

I tend to use the Clear button on the Advanced tab of the Taskbar and Start Menu Properties
dialog fairly often. Call me paranoid, but I don't like to leave snail trails that others can use
to snoop around and see what I've been up to. So, in my case, that would mean having to retype it
each time (or search for the correct version of msaccess.exe and drag-n-drop it onto the Start =>
Run dialog.

Tom
_____________________________

Thanks a lot, Tom, for this good suggestion.

However, I personally don't "type out the path in the Start => Run...
dialog each time". I just select the item I want from the combobox on
the Start => Run dialog.

 

[Tony Toews]

I have a question regarding this.

did you have this sort of problem also if you run it with an execute?
or is it only in the "Design View".

I vaguely recall having troubles intermittently as I was adding
queries to the Union. Once I added another query I was having
troubles all the time.

That was a couple of years ago.

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

 

[Doug]

You all gave me info that helped so i'm giving back. I resolved most of my
problems I've had with Access. Here's what i've learnt:

1) Do not never, no way, EVER name an Access object with a % in the name.
Things go very badly with the Object Dependencies gizmatron and other erratic
behavior happened regularly. I renamed those objects so they only had a-z,
0-9 chars and things are infinitely better now.

2) Do not never, no way, EVER name an Access table column the same as the
table it's in. The queries produce the most unusual XYZ____ ... stuff trying
to represent it. If you have already done this and don't want to change the
col name, you can rename the col name in the queries e.g. if the ORDER table
had also the ORDER column, in the queries do ORDER_ID: ORDER and your life
will be spared.

3) The "Too many databases" problem was resolved by UNsplitting the database
i.e. bringing all the linked tables back into the main DB. It also runs a ton
faster and is smaller too.

4) I found the reason the DB was crashing ("I'm sorry Access has fallen down
and cant' get up") was due to forms with lots of subforms. I'd click on a
combo box to go to another record and bOOm. When I replaced the subforms with
buttons that opened e.g. dialog boxes, suddenly no more crashes. Additionally
the forms open faster.

I hope any of this is helpful. Again, thanks for all your input.

Doug

 

[Allen Browne]

Well done! And thanks for sharing the things that helped you get a stable
database.

Regarding your specific points:

1 and 2. Naming Issues
Most of these naming issues you identified are probably caused by the many
bugs associated with Name AutoCorrect. Details:
http://members.iinet.net.au/~allenbrowne/bug-03.html
Avoiding characters like %, *, |, space, and brackets in names is a good
idea. Also avoid reserved words and the names of objects and properties such
as:
- Name (most objects have a Name property),
- Section (forms and reports have sections),
- Select, Where, Group (reserved words in queries),
- Date, Year, Month, Day (system names/functions).

3. Unsplitting
Okay, you worked around the problem successfully, but didn't solve it.
Splitting is essential in many cases for ease of maintenance or multi-user
operations.

4. Crashes with subforms.
This sounds like the bug in Access 2002 and 2003 where it crashes if the
field named in the LinkChildFields of the subform is not represented by a
control in the the subform. Theoretically this is supposed to work, but due
to a bug in the implemention of the AccessField type it crashes. You can
avoid the bug by adding a text box to the subform so that the
LinkChildFields reference is to a TextBox object instead of an AccessField
object. Then make sure Name AutoCorrect is off, decompile, compact, and the
crashes should stop.

 

[Tony Toews]

You all gave me info that helped so i'm giving back. I resolved most of my
problems I've had with Access. Here's what i've learnt:

Thanks very much for posting your list of problems. That helps us
especially when we've learnt over the years to never do things a
certain way. <smile>

As far as points 1 and 2 go a partial answer is Tony's Table and Field
Naming Conventions
http://www.granite.ab.ca/access/tablefieldnaming.htm

3) The "Too many databases" problem was resolved by UNsplitting the database
i.e. bringing all the linked tables back into the main DB. It also runs a ton
faster and is smaller too.

You really, really want to split the MDB though.

However you really want to put the FE on each machine or place in a
user specific directory on the server. This will help avoid some
weird error messages when users are changing the same forms record
source, filters and such as well as corruptions. It is also much
easier to implement a new version of the database with changed
queries, forms, reports and VBA code.

I specifically created the Auto FE Updater utility so that I could
make changes to the FE MDE as often as I wanted and be quite confident
that the next time someone went to run the app that it would pull in
the latest version. For more info on the errors or the Auto FE
Updater utility see the free Auto FE Updater utility at
http://www.granite.ab.ca/access/autofe.htm at my website to keep the
FE on each PC up to date.

In a Terminal Server or Citrix environment the Auto FE Updater now
supports creating a directory named after the user on a server. Given
a choice put the FE on the Citrix server to reduce network traffic and
to avoid having to load objects over the network which can be somewhat
sluggish.

4) I found the reason the DB was crashing ("I'm sorry Access has fallen down
and cant' get up") was due to forms with lots of subforms. I'd click on a
combo box to go to another record and bOOm. When I replaced the subforms with
buttons that opened e.g. dialog boxes, suddenly no more crashes. Additionally
the forms open faster.

Now this one is interesting. I'm sure I'll be encountering it soon.
<smile>

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