97 to 2003 Database Conversion - Datatype

[Guest]

After converting from 97 to 2003, text datatype is defaulting to binary.
I've checked the 97 tables and the datatype is text.

This is sure upsetting the many many make table queries we use to append
data. When the table is created, the field datatype is binary when it should
be text.

Anyone have any idea(s) PLEASE?

Thanx in advance,
Gerry b.

 

[[MVP] S.Clark]

This is one of the reasons to use an append query, instead of a Make Table.
I don't know the extent of your 'many many' make table queries, but maybe
you can switch them to be append queries, to a known table format, instead.

 

[Guest]

The known datatype in 97 was text. The make table query worked fine. Why
does it switch to binary in 2003? We have 300+ make table queries used for
ad hoc reporting. This is huge! Any other assistance would be greatly
appreciated - I don't think the plan was to re-write the system my clients
are using.

Gerry b.

 

[Allen Browne]

Gerry, if these are JET tables (i.e. the tables exists in an mdb), I have
never set JET default to a Binary type field. You can't even create that
type through the user interface in Access.

What do you get from:
? GetOption("Default Field Type")
I would expect it to return zero (for Text type.)

To make sure we are talking about the same thing, I presume you mean the
field is dbBinary (DAO) or adVarBinary (ADOX.)

BTW, like Steve, I almost never use Make Table queries in a production
environment.

 

[[MVP] S.Clark]

I tried to duplicate the problem by making a table with text field, then
creating a Make table based on it. The text fields stayed text fields,
except when I forced this little syntax SNAFU:

SELECT Table1.ID, Table1.Text1, [text2]="1" AS Expr1 INTO Table2
FROM Table1;

In this case, the result is a True / False condition, but even it was made
into a Number of integer, and not a "Binary".(Like Allen said, Access
doesn't have a binary, but I think you meant a binary or boolean style.

SOOOOO, my guess is that you're performing a comparison in the query, like
the above statement, and your result is undesired.

HTH,

 

[Guest]

Hi,
I am having a very similar problem....

I have an Access 97 database that I'd like to convert to Access 2003. I
currently have both versions of Access on my computer. (I have Access 97 only
and Office 2003 Professional).

I have this problem running the query in the Access 97 version db in Access
2003 (that is, not converted), as well as when I run the query from the
converted database in Access 2003. The query runs fine in Access 97.

When I try to run the append query, it doesn't work due to a data type
mismatch. If I preview the query results, the date fields are blank, and
the field which contained a long integer now contains a Chinese-looking
character. If I change the query to a make-table query, the data types are
Binary in the fields which should contain dates and the one that should
contain a long integer is also Binary. ( I see this in design view of the
table, though as far as I know there is no Binary data type in Access)

The same thing happens if I make a new form based on a new query based on
the same table. If I make a new table with the data, I still have the
problem.

Any ideas on what could cause this?
Thanks for your help,
Gretchen

Here is the SQL for an example which acts the same way:
INSERT INTO atesttblClaimDetails ( ClaimNo, Narrative )
SELECT [Forms]![Form3]![ClaimNo] AS ClaimNo, "Hello" AS [Narrative];

 

[Guest]

I found an answer in another post...using clng, etc. solves the problem.
See link below:
http://groups.google.com/group/micr...e8b5e/e71191f3be1329d6?tvc=1#e71191f3be1329d6
Thanks!
Gretchen

Hi,
I am having a very similar problem....

I have an Access 97 database that I'd like to convert to Access 2003. I
currently have both versions of Access on my computer. (I have Access 97 only
and Office 2003 Professional).

I have this problem running the query in the Access 97 version db in Access
2003 (that is, not converted), as well as when I run the query from the
converted database in Access 2003. The query runs fine in Access 97.

When I try to run the append query, it doesn't work due to a data type
mismatch. If I preview the query results, the date fields are blank, and
the field which contained a long integer now contains a Chinese-looking
character. If I change the query to a make-table query, the data types are
Binary in the fields which should contain dates and the one that should
contain a long integer is also Binary. ( I see this in design view of the
table, though as far as I know there is no Binary data type in Access)

The same thing happens if I make a new form based on a new query based on
the same table. If I make a new table with the data, I still have the
problem.

Any ideas on what could cause this?
Thanks for your help,
Gretchen

Here is the SQL for an example which acts the same way:
INSERT INTO atesttblClaimDetails ( ClaimNo, Narrative )
SELECT [Forms]![Form3]![ClaimNo] AS ClaimNo, "Hello" AS [Narrative];

This is one of the reasons to use an append query, instead of a Make Table.
I don't know the extent of your 'many many' make table queries, but maybe
you can switch them to be append queries, to a known table format, instead.