Different type conversion A2k and A97 ?

Hi all,
I have a database that I need to use in different versions of Access.
This is A97 in most places and A2k in a few other locations.
(I develop in A97 and convert the db to A2k for these other locations)
FYI: I am using A97 as backend for both versions. No problem.

After conversion A97 > A2k everything (seems to) work(s) just fine, except for one thing I only discovered today.
Using an unbound form to collect data that is used in an appendquery A2k fails with the appendquery.
It always worked and still works in A97.
Btw: In this example I am using a local table to append to.

Problem seems to be that Access does not understand the values in the unbound form.
Access complains about type conversion errors.
When I look at the query in design view and switch the view to the records to be appended I see strange characters like = in the datagrid.
Solution:
When I change the query and add parameters for *each field* in the form it works as expected.
But having to add parameters for all the fields (27) is very annoying...
Also: I am aftraid I have to check all these query's now ... Aarrrrrgggghhhh.

Any comments? No corruption here AFAIK.

Thanks,
Arno R

re: Different type conversion A2k and A97 ?

> When I change the query and add parameters for *each field* in the form it[color=blue]
> works[/color]

Yes
[color=blue]
> But having to add parameters for all the fields (27) is very annoying...[/color]

Yes
[color=blue]
> Also: I am aftraid I have to check all these query's now ...
> Aarrrrrgggghhhh.[/color]

Yes

< Any comments? No corruption here AFAIK.

Sometimes even adding parameter types isn't enough. Fortunately,
we already had parameter types for most of our parameter queries,
and for the few cases where it failed anyway and crashed A2000,
we were able to code work-arounds.

(david)



"Arno R" <arraNOcomSPAM@tiscali.nl> wrote in message
news:439f09ba$0$2339$ba620dc5@text.nova.planet.nl. ..
Hi all,
I have a database that I need to use in different versions of Access.
This is A97 in most places and A2k in a few other locations.
(I develop in A97 and convert the db to A2k for these other locations)
FYI: I am using A97 as backend for both versions. No problem.

After conversion A97 > A2k everything (seems to) work(s) just fine, except
for one thing I only discovered today.
Using an unbound form to collect data that is used in an appendquery A2k
fails with the appendquery.
It always worked and still works in A97.
Btw: In this example I am using a local table to append to.

Problem seems to be that Access does not understand the values in the
unbound form.
Access complains about type conversion errors.
When I look at the query in design view and switch the view to the records
to be appended I see strange characters like = in the datagrid.
Solution:
When I change the query and add parameters for *each field* in the form it
works as expected.
But having to add parameters for all the fields (27) is very annoying...
Also: I am aftraid I have to check all these query's now ...
Aarrrrrgggghhhh.

Any comments? No corruption here AFAIK.

Thanks,
Arno R

re: Different type conversion A2k and A97 ?

"david epsom dot com dot au" <david@epsomdotcomdotau> schreef in bericht news:439f6ace$0$38807$c30e37c6@lon-reader.news.telstra.net...
[color=blue]
> Sometimes even adding parameter types isn't enough. Fortunately,
> we already had parameter types for most of our parameter queries,
> and for the few cases where it failed anyway and crashed A2000,
> we were able to code work-arounds.
>
> (david)[/color]


Thanks David,

I Googled around for this problem and saw a tip on Allen Browne's site about setting the format property for unbound controls.
But no luck with this also. In my example Access 'want's' a parameter setting for all fields.
I did read that there is a difference between Jet 3.6 and Jet 4.xxx in this 'type guessing' issue.

Do you happen to know whether this 'problem' is 'solved' in A2002 or A2003?

Arno R

re: Different type conversion A2k and A97 ?

Arno R wrote:
[color=blue]
> Problem seems to be that Access does not understand the values in the unbound form.
> Access complains about type conversion errors.
> When I look at the query in design view and switch the view to the records to be appended I see strange characters like = in the datagrid.[/color]

It woud be helpful if we knew how Access is being asked to "understand"
the values in the unbound form and when and what types of values. A few
lines of code could show this?
[color=blue]
> Any comments? No corruption here AFAIK.[/color]

All data on form are strings, series of bytes. Access magically or
wickedly, depending upon the situation and your point of view, may
treat these as someting else: numbers, booleans, blobs whatever.
As Access and VBA progress they seem to merge more of their
functionality with unicode. This can cause some things we have designed
to self-destruct. Where our work expected one byte previously it is now
getting two.
This may have nothing to do with your problem whatever, of course,

More information would help me to understand.

re: Different type conversion A2k and A97 ?

"Lyle Fairfield" <lylefairfield@aim.com> schreef in bericht news:1134564873.586426.56740@g47g2000cwa.googlegro ups.com...[color=blue]
>
> Arno R wrote:
> [color=green]
>> Problem seems to be that Access does not understand the values in the unbound form.
>> Access complains about type conversion errors.
>> When I look at the query in design view and switch the view to the records to be appended I see strange characters like = in the datagrid.[/color]
>
> It woud be helpful if we knew how Access is being asked to "understand"
> the values in the unbound form and when and what types of values. A few
> lines of code could show this?
> [color=green]
>> Any comments? No corruption here AFAIK.[/color]
>
> All data on form are strings, series of bytes. Access magically or
> wickedly, depending upon the situation and your point of view, may
> treat these as someting else: numbers, booleans, blobs whatever.
> As Access and VBA progress they seem to merge more of their
> functionality with unicode. This can cause some things we have designed
> to self-destruct. Where our work expected one byte previously it is now
> getting two.
> This may have nothing to do with your problem whatever, of course,
>
> More information would help me to understand.[/color]

Hi Lyle,
This is about an order form for shoes... (from one shop to another).

As I said I use an unbound form (FrmKlantenBestelling) to collect orders.

I use the values from this form in an appendquery like:
INSERT INTO TabKlantBestelling ( Besteld, WinkelVan, WinkelNaar, Categorie, Artikelnummer, 0, 35, [35+], 36, [36+], 37, [37+], 38, [38+], 39, [39+], 40, [40+], 41, [41+], 42, [42+], 43, [43+], 44, [44+], 45, [45+], 46, [46+] )
SELECT Date() AS Mutatiedatum, [Forms]![FrmKlantenBestelling]![VanWinkel] AS VWinkel, [Forms]![FrmKlantenBestelling]![NaarWinkel] AS NWinkel, [Forms]![FrmKlantenBestelling]![Categorie] AS Cat, [Forms]![FrmKlantenBestelling]![Artikelnummer] AS Art, [Forms]![FrmKlantenBestelling]![0] AS 0, [Forms]![FrmKlantenBestelling]![35] AS 35, [Forms]![FrmKlantenBestelling]![35+] AS [35+], [Forms]![FrmKlantenBestelling]![36] AS 36, [Forms]![FrmKlantenBestelling]![36+] AS [36+], [Forms]![FrmKlantenBestelling]![37] AS 37, [Forms]![FrmKlantenBestelling]![37+] AS [37+], [Forms]![FrmKlantenBestelling]![38] AS 38, (this goes further ..... until 46+)

I have combo's to select Categorie, Artikelnummer, ShopTo and ShopFrom and controls for the values 35, 35+, 36, 36+ .......46, 46+ (these values mean the size of the shoes, numbers from 1 to 5 are entered in the fields)

With Acces97 this worked for years without any problem.
After conversion (I had to convert the app for other reasons) to A2K, it does not work like this ONLY if I add the types (parameters) in the query like:

PARAMETERS [Forms]![FrmKlantenBestelling]![VanWinkel] Text ( 255 ), [Forms]![FrmKlantenBestelling]![NaarWinkel] Text ( 255 ), [Forms]![FrmKlantenBestelling]![Categorie] Short, [Forms]![FrmKlantenBestelling]![Artikelnummer] Long, [Forms]![FrmKlantenBestelling]![0] Short, [Forms]![FrmKlantenBestelling]![35] Short, [Forms]![FrmKlantenBestelling]![35+] Short, [Forms]![FrmKlantenBestelling]![36] Short, [Forms]![FrmKlantenBestelling]![36+] Short, [Forms]![FrmKlantenBestelling]![37] Short, [Forms]![FrmKlantenBestelling]![37+] Short, [Forms]![FrmKlantenBestelling]![38] Short, ....... ;
INSERT INTO TabKlantBestelling ( Besteld, WinkelVan, WinkelNaar, Categorie, Artikelnummer, 0, 35, [35+], 36, [36+], 37, [37+], 38, [38+], 39, [39+], 40, [40+], 41, [41+], 42, [42+], 43, [43+], 44, [44+], 45, [45+], 46, [46+] )
SELECT Date() AS Mutatiedatum, [Forms]![FrmKlantenBestelling]![VanWinkel] AS VWinkel, [Forms]![FrmKlantenBestelling]![NaarWinkel] AS NWinkel, [Forms]![FrmKlantenBestelling]![Categorie] AS Cat, [Forms]![FrmKlantenBestelling]![Artikelnummer] AS Art, [Forms]![FrmKlantenBestelling]![0] AS 0, [Forms]![FrmKlantenBestelling]![35] AS 35, [Forms]![FrmKlantenBestelling]![35+] AS [35+], [Forms]![FrmKlantenBestelling]![36] AS 36, [Forms]![FrmKlantenBestelling]![36+] AS [36+], [Forms]![FrmKlantenBestelling]![37] AS 37, [Forms]![FrmKlantenBestelling]![37+] AS [37+], [Forms]![FrmKlantenBestelling]![38] AS 38, [Forms]![FrmKlantenBestelling]![38+] AS [38+], [Forms]![FrmKlantenBestelling]![39] AS 39, [Forms]![FrmKlantenBestelling]![39+] AS [39+], [Forms]![FrmKlantenBestelling]![40] AS 40, [Forms]![FrmKlantenBestelling]![40+] AS [40+], [Forms]![FrmKlantenBestelling]![41] AS 41, [Forms]![FrmKlantenBestelling]![41+] AS [41+], [Forms]![FrmKlantenBestelling]![42] AS 42, [Forms]![FrmKlantenBestelling]![42+] AS [42+], [Forms]![FrmKlantenBestelling]![43] AS 43, [Forms]![FrmKlantenBestelling]![43+] AS [43+], [Forms]![FrmKlantenBestelling]![44] AS 44, [Forms]![FrmKlantenBestelling]![44+] AS [44+], [Forms]![FrmKlantenBestelling]![45] AS 45, [Forms]![FrmKlantenBestelling]![45+] AS [45+], [Forms]![FrmKlantenBestelling]![46] AS 46, [Forms]![FrmKlantenBestelling]![46+] AS [46+];

Also when I set the format property of the numeric controls to standard (as Allen Brown suggests in one of his tips) it does not work.

Hope this makes sense and makes it clear to you.

Arno R
..

re: Different type conversion A2k and A97 ?

Thank you Arno.
The table and query are interesting.
I think it's not unusual to have to tell JET what type of data it is
getting; you seem to have found a way to do it satisfactorily; some
think it's a good idea always to use a collection of typed parameters.
Perhaps, you have had occasion to think about the advisability of
naming fields with names that might be mistaken for numbers? Sometimes
JET SQL can refer to fields by their ordinal position; at this time it
might be confused between a field named "1" and the first field. Also
it may be confused about a literal 1 and the field name 1.

re: Different type conversion A2k and A97 ?

"Lyle Fairfield" <lylefairfield@aim.com> schreef in bericht news:1134571346.689207.243940@o13g2000cwo.googlegr oups.com...[color=blue]
> Thank you Arno.
> The table and query are interesting.
> I think it's not unusual to have to tell JET what type of data it is
> getting; you seem to have found a way to do it satisfactorily; some
> think it's a good idea always to use a collection of typed parameters.
> Perhaps, you have had occasion to think about the advisability of
> naming fields with names that might be mistaken for numbers? Sometimes
> JET SQL can refer to fields by their ordinal position; at this time it
> might be confused between a field named "1" and the first field. Also
> it may be confused about a literal 1 and the field name 1.[/color]

Hi Lyle,
Yes, I know about the field names ... they are 'horrible'.
It is a database I 'inherited' and sort of 'took over' from another guy.
Once in production it is very hard to change the tabledefinitions and/or fieldnames.
These fieldnames are also used in a *lot* of queries of course.
You get what I mean? But what the heck... it works for years and years.
I would develop it somewhat different that's for sure.

Access 'understands' that 35 is a field when this is put between brackets like [35].
But the fieldnames is not the problem at all here ...
I need to work with two versions of this app now because of install problems I had.
The problem is the difference in behaviour between an app developed in A97 and the very same app converted to A2k.

I noticed the difference in the type recognition when developing new apps in A2k, but I managed to find and solve these cases by testing and testing.
This app is fully tested and in production for years now, but when it's converted gives problems suddenly.
I tested the converted app, but obviously not enough....

Arno R

re: Different type conversion A2k and A97 ?

>Do you happen to know whether this 'problem' is 'solved' in A2002 or A2003?

Sorry, no idea. (It's the kind of thing you fix, and then don't
change back to try again.)

I always avoid form references like that, The main reason I have form
references in complex queries is because other people write them.

And our other parameters were all declared and typed in the conversion
from A2.0 to A97 because of similar problems with A97!

BTW, I also had to change one of my combo boxes so that it's base
query was not "DISTINCT". Sometimes grouping (and Union Queries)
seem to mess up the type information, and sometimes seem to mess
up the .Value of combo boxes.

(david)



"Arno R" <arraNOcomSPAM@tiscali.nl> wrote in message
news:43a0119f$0$2345$ba620dc5@text.nova.planet.nl. ..

"david epsom dot com dot au" <david@epsomdotcomdotau> schreef in bericht
news:439f6ace$0$38807$c30e37c6@lon-reader.news.telstra.net...
[color=blue]
> Sometimes even adding parameter types isn't enough. Fortunately,
> we already had parameter types for most of our parameter queries,
> and for the few cases where it failed anyway and crashed A2000,
> we were able to code work-arounds.
>
> (david)[/color]


Thanks David,

I Googled around for this problem and saw a tip on Allen Browne's site about
setting the format property for unbound controls.
But no luck with this also. In my example Access 'want's' a parameter
setting for all fields.
I did read that there is a difference between Jet 3.6 and Jet 4.xxx in this
'type guessing' issue.

Do you happen to know whether this 'problem' is 'solved' in A2002 or A2003?

Arno R

re: Different type conversion A2k and A97 ?

Arno

The problem is that Access97 permits this very sloppy sql script.
That problem was 'solved' in Access 2000 and, I suspect, JET4.0.

This guessing, this type coercion, prevents VBA (and JET SQL to the
extent that it emulates VBA) from being fast and efficient. To many
advanced programmers it makes VBA, Access and even JET objects of
derision.

If one wants to write sound code one types all variables and explicitly
converts them when conversion is required, leaving nothing to Uncle
Bill to guess at. This is the difference between a man and a milk sop.
Oh no, sorry that's India Pale Ale, isn't it? Oh well, you get the
idea.

re: Different type conversion A2k and A97 ?

david epsom dot com dot au wrote:[color=blue]
> I always avoid form references like that, The main reason I have form
> references in complex queries is because other people write them.[/color]

That's good advice. I run in an environment that is similar to Arno's.
I use unbound forms for search, select and edit since only one person
updates, say, customer or vendor information. I rarely use a form
reference in SQL for those cases. I build the SQL string in code
behind the unbound form and have the string include conversion
functions when needed. I'm glad Arno pointed out the problem though.

James A. Fortune
CDMAPoster@FortuneJames.com

re: Different type conversion A2k and A97 ?

CDMAPoster@FortuneJames.com wrote:[color=blue]
> david epsom dot com dot au wrote:[color=green]
> > I always avoid form references like that, The main reason I have form
> > references in complex queries is because other people write them.[/color]
>
> That's good advice. I run in an environment that is similar to Arno's.
> I use unbound forms for search, select and edit since only one person
> updates, say, customer or vendor information. I rarely use a form
> reference in SQL for those cases. I build the SQL string in code
> behind the unbound form and have the string include conversion
> functions when needed. I'm glad Arno pointed out the problem though.
>
> James A. Fortune
> CDMAPoster@FortuneJames.com[/color]

Correction: The select results form is bound to the SQL string created
by the unbound search form. The textboxes shown on the results form
are used to verify that the record is the correct one, and are locked.

James A. Fortune
CDMAPoster@FortuneJames.com