Unlinked tables in queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Does anyone have a list of all the useful bits and pieces that used to be in
all access version up to 2000, and have been taken out in later versions.
So far I have found that we can no longer add Common dialogs to forms.
That 65 + null now equals null , where it used to be 65.
And today, worst of all a technique I've been using for years doesn't work
in the 2002 version with a 2000 format database.

In all previous versions, you could add a table to a query and not link it
to any others. If this was a single record 'parameter' table you could then
easily collect a parameter from the user once, and then simply run existing
non parameter queries.


I tried this in a new location this morning, and instead of ALL records, I
get none at all as soon as I put in my second table.


PLEASE, does anyone know how I can get it to work the way it used to? This
is the single most useful technique I've used for years. Easy, painless,
coding was straightforward, users loved it, and supporting it or adding new
parameters was an absolute doddle.

Any help gratefully received. I'd rather not have to use another workround
that just makes my life and the users lives harder. It really is annoying to
keep coming across functionality changes like this. (Although this one is
currently worse than not being able to use Outlook to send .mdb files - there
is a third party add in that sorts that one out properly)
 
Ok. Forget this one.
It was another 'null' trick.
When I tested, the parameter table actually had no records!
I needed the first run to show zero, and on first glance at an empty one
record one field table it look like you have one record with zero stored. but
in fact you have no records with null.
I changed the on screen zero to zero and all was well again.

So this one down to me, not Microsoft.
 
Comments in-line.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

DavidAtCaspian said:
Does anyone have a list of all the useful bits and pieces that used to be
in
all access version up to 2000, and have been taken out in later versions.
So far I have found that we can no longer add Common dialogs to forms.
Click to expand...

The common dialog control was not included by default. If you did add it, it
was unreliable (buggy), and prone to broken references (particularly when
changing versions.)

In Access 2002 the Office library provides a FileDialog object, but it is
unreliable and incomplete, so just as unusable as the old Common Dialog was.

The only reliable solution in any version of Access is the API calls such
as:
http://www.mvps.org/access/api/api0001.htm
http://www.mvps.org/access/api/api0002.htm
http://www.mvps.org/access/api/api0060.htm
That 65 + null now equals null , where it used to be 65.
Click to expand...

Incorrect. Any value plus Null has equalled Null in all versions of Access.
(Concatenation with the & operator is different, but has been consistent
across versions at least since 95 and probably longer.)
And today, worst of all a technique I've been using for years doesn't work
in the 2002 version with a 2000 format database.

In all previous versions, you could add a table to a query and not link it
to any others. If this was a single record 'parameter' table you could
then
easily collect a parameter from the user once, and then simply run
existing
non parameter queries.

I tried this in a new location this morning, and instead of ALL records, I
get none at all as soon as I put in my second table.
Click to expand...

JET 4 does behave quite differently than previous versions.

AFAICT, Microsoft has tried to make it closer to how SQL Server behaves,
even if this does not match how JET previously behaved. In the process, they
have added considerable new functionality and broken some existing
functionality.

One aspect is that JET is now worse at understanding the data type of
parameters and calculated fields. You can usually solve this by explicitly
declaring your parameters so, and typecasting calculated fields. More info
in:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

If that is not the issue you are struggling with, you should still be able
to use parameters, even in most stacked queries (except include some
crosstabs, UNION, and pass-throughs for example.)

Parameters have never worked under DAO (e.g. OpenRecordset or Execute)
unless you explicitly assign values to them.

If you are still stuck with this parameter issue, feel free to post an
example of a query that no longer works (including any lower level queries
it depends upon) and indicate which are the parameters.
 
Allen:

Many thanks for taking the time on this one. The function is in fact working
just the same as it did. It was my own fault for confusing a table having
one field and one record of zero, with an empty table!. So I'm happy on that
front. It's an amazingly useful method!. :-)

The null one is interesting as I got this when a fully working subroutine
written in Access 2000, didn't run properly when I opened the mdb in Access
2003.
Among other things the sub accumulated the values in a sparsely populated
field

I was expecting a final total in the region of 65000, and got nothing. It
was only when I single stepped and watched the variable I saw that as soon as
we got to a record where the field was null, the entire variable became null
(Oh yes it did :-))

(code: curCollector = curCollector + rs![value] : Where rs![value] was null,
then after this line of code, curCollector was null). - The fix was to set
all the nulls to zero.

I have heard the common dialog reasoning, and I still think its wrong!. At
the very least MS should offer the design time license as a free dwonload if
people are prepared to live with the consequences. Just the same as they
should offer a decent fix for Outlook being hard coded not to open mdb files.

Anyway, all is now operational again!

Regards

David
 
Back
Top