MS access size issue???

[hugh welford]

Hi,

Using ACCESS 2000 on Win XPPro.

I have written data trawling application for a medical practice to extract
data from patients records contained in serial visit records kept in a table
"visits" and linked to patient details in "details" . It is basically a tool
to extract serial data according to user input paremeters for reseach
purposes.

It has worked fine in the past, but has started to give inconsistent results
for identical runs. I cannot find any programming reason for this and have
begun to suspect the data or the way ACCESS is handling it. The base data
looks OK and runs the practice management suite with no problem, but the
trawling program makes extensive use of make-table and append queries to do
its work, and its here that I think the problem may lie. For instance, the
datasheet view of the make-table query shows perfect data, but the resulting
table ( supposedly ordered by PIN and visit#) contains randomly out-of-order
records. Also, compacting and refreshing links to the base data results in
loss of records and further dis-order.

The base data is now about 106 megabytes, and I wonder whether there is a
size issue here, and that 106 meg is too big for access to handle reliably.
Is it time to upgrade to mysql and how difficult would that be? Would a
re-write be necessary toupgrade to mysql?

Anyoneout there had or know about such problems and their solution?

I would be grateful for any help/advice/pointers to help me with this
problem

Thanks in advance

Hugh

 

[Matt]

AFAIK, an Access 2000 database can be up to 2GB. You can (should) use to
repair tool once a while to compress the size.













- Show quoted text -

Yeah, 2GB is the limit, and it works reliably up until that point
(other than query performance issues on the bigger tables).

 

[hugh welford]

thanks guys

 

[(PeteCresswell)]

Per hugh welford:

and that 106 meg is too big for access to handle reliably.
Is it time to upgrade to mysql and how difficult would that be? Would a
re-write be necessary toupgrade to mysql?

Based on the 100-megish apps I've put up, I don't think that's a problem.

What *could* be a problem is that the back end is subtly corrupted - not so
badly that it won't load/open, but bad enough that the data's become flaky.

Try browsing the tables directly and see if you notice anything.

The no-brainer for me would tb compact and repair the DB.

After that, I'd export each table to a tab-delim'd text file and the re-import
them into a virgin empty DB.


Why MySQL and not the SQL Server mini-version that MS puts out there as a
freebie? I'm not touting it bc I don't know... but the thought came to
mind... Certainly it would position you better for migration to the full blown
version of SQL Server if that became desirable sometime in the future.

 

[Guest]

Avoid aggregate queries. Never use 'first' or 'last' in aggregate
queries (only 'max' or 'min').

Avoid XLS import/export. Never do repeated XLS import/export.

Also, compacting and refreshing links to the base data results in
loss of records and further dis-order.

compact and re-link after making any design changes to the base
data, or after compacting the base data.

(david)

 

[Albert D. Kallal]

You not given much to go on in the sense that you left out major important
details.

The 1st issue here is a network involved? (in accessing the data)

Further, is the database being used by more then one person (ie: is it multi
user).

And, more further, if it is multi-user, is your setup correct ? (ie: have
you split the database).

I not sure we should jump into talking about using sql server when we not
even determined if a network is in use now....

However, to answer your qeustion, I don't see the 100 meg file size any
thing to really worry about.

We can assume this file size you speak of is right after you done a compact
and repair?

So, if I read this correct, you have a sql order by clause, but the results
are not the same from one run to the next? What kind of order clause are you
talking about here?

I would try importing the data into a new blank file, and see if this order
issue comes up a gain...

You do realize that if you write out a number of records to a table, and
then open that table, the order of records retuned will not be the same. In
fact from one run to the next, the order of data will not necessary be the
same *unless* you use a order by clause in your sql.

Data in ms-access by definition is un-ordered..and you MUST set order..it
CAN NOT be assumed (I kind of doubt this is your problem..but, sometimes
people assume order of data..and you can't in ms-access).

So, if you open up a table (and don't use sql), then records do not
necessary come back in the same order even from the last time you opened the
table!!!

So, we probably should look further into which kind of ordering problem you
are experiencing.

If your sql has a order by clause on a some type of number field, and the
results are inconsistent, then you got just some plain damage on the index,
or the data file...

If the order by clause is on a text field, then this can be more difficult,
as things like extra spaces, or non printable control characters could be in
the text data to mess this up.

 

[Vladimír Cvajniga]

Avoid aggregate queries. Never use 'first' or 'last' in aggregate

queries (only 'max' or 'min'). Why?

Avoid XLS import/export. Never do repeated XLS import/export.

Why?

Vlado

 

[Guest]

Why?

Never use 'first' or 'last' because they are the most likely to give
unexpected result. By definition, they return a random value. Normally,
they return the first/last value. This is just inconsistant enough to be
a problem. Technically, they return the first/last value of a recordset,
but the recordset order is not defined. In practice, the recordset order
is likely to be 'natural' order, or may be defined by an 'order by'
clause, but 'natural order' is not defined, and the order in which an
'order by' clause, and the 'select max/min' clause, and the 'where'
clause, and the 'group by' clause are evaluated is not defined.

Avoid aggregate queries because a rare bug in the query interpreter
sometimes misorders the evaluation of complex nested aggregate
queries. There is also a rare problem found when the order of the
columns is not consistant accross all terms in the query, or all queries
in a nested set. I only worry about it when there is an obvious
problem, as there is here.

Why?

Avoid XLS import/export because it doesn't alway give consistant
results for large recordsets. In particular, if you are trying to do a
set of XLS exports, you may find that you get the wrong number
of records. I think that there may be a problem with XLS export
not completing synchronously before you move to the next one.
Again, I only mention this because there is a problem: it seems to
work ok for small recordsets.

 

[Vladimír Cvajniga]

Thx for your respond. I hope all those problems were passed to Microsoft as
BUGs.

Firs/Last: if I use it in context I think I should get correct results. When
I need to know whether ANY record exists I don't care about order.

Vlado

 

[hugh welford]

Thanks Pete - very useful info
Hugh

Per hugh welford:

Based on the 100-megish apps I've put up, I don't think that's a problem.

What *could* be a problem is that the back end is subtly corrupted - not
so
badly that it won't load/open, but bad enough that the data's become
flaky.

Try browsing the tables directly and see if you notice anything.

The no-brainer for me would tb compact and repair the DB.

After that, I'd export each table to a tab-delim'd text file and the
re-import
them into a virgin empty DB.


Why MySQL and not the SQL Server mini-version that MS puts out there as a
freebie? I'm not touting it bc I don't know... but the thought came to
mind... Certainly it would position you better for migration to the full
blown
version of SQL Server if that became desirable sometime in the future.

 

[hugh welford]

thanks david

Avoid aggregate queries. Never use 'first' or 'last' in aggregate
queries (only 'max' or 'min').

Avoid XLS import/export. Never do repeated XLS import/export.


compact and re-link after making any design changes to the base
data, or after compacting the base data.

(david)

 

[hugh welford]

Thanks albert -

Straight forward single user no network.

Looking into all your suggestions

hugh

 

[aaron.kempf]

yes; there is a size issue.

MDB cannot reliably hold 100mb; you need to move to SQL Server.

Access Data Projects allow you to keep a LOT of your existing business
logic.
and with VB changing into Visual Fred version 3.0; you're best off
sticking with Access

 

[aaron.kempf]

(other than query performance issues on the bigger tables? ARE YOU
FUCKING KIDDING ME? WHAT A PIECE OF CRAP DISCLAIMER... YEAH IT WORKS
FINE EXCEPT FOR PERFORMANCE)

fucking newbie

**** you and don't fucking promote MDB you faggot

 

[aaron.kempf]

wow... that is a new one

so now XLS import / export is one of those things that causes MDB
bloat?

ROFL

what is the _POINT_ of MDB with all these damn disclaimers?

SERIOUSLY

 

[aaron.kempf]

Singler User & No Network?

ADP is still easier development than this MDB _CRAP_




I've got profiler.. does MDB have a profiler?

 

[Tony Toews [MVP]]

yes; there is a size issue.

MDB cannot reliably hold 100mb; you need to move to SQL Server.

This is wrong.

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

 

[John W. Vinson]

Never use 'first' or 'last' because they are the most likely to give
unexpected result. By definition, they return a random value.

Ummm... not exactly.

They return the first or last record *in disk storage order*. If you run a
query selecting First of a field repeatedly, you'll get the same record -
possibly until the database containing the table is compacted, which may well
change the order. And if you have populated the table all in one go using an
Append query, the record returned by First() will in fact probably be the
record a user would expect. If the table has been filled piecemeal, with
additions and deletions, that expectation might NOT be met - it'll be the
first record in disk storage order still, but that might be a record added
much more recently which happened to land in a slot deleted previously.

In other words, First and Last aren't "random" in any mathematical sense - but
they are arbitrary and unpredictable. First() is quite useful if you don't
CARE which record, and just want any record that comes along; I've never seen
any good rationale for using Last() though, since it is equally arbitrary but
requires that the query traverse the entire recordset.

John W. Vinson [MVP]

 

[(PeteCresswell)]

Per (e-mail address removed):

MDB cannot reliably hold 100mb; you need to move to SQL Server.

BS

 

[aaron.kempf]

what is wrong about it fucknut?

I was dealing with dozens of MDB while you were still sucking your
thumb

go and find a real database, friggin WUSS


MDB is only used by lamers and retards



lose the training wheels you friggin Newbie