different results w/transferspreadsheet 2002sp2 and 2003

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Phil - 27 Jul 2006 22:10 GMT

OK, Same database, same spreadsheet, running on two different XP pro
machines, accessed from a network drive.
I have a macro that includes a transferspreadsheet command.
The spreadsheet is in excel 2000 format.
I am doing an import, with field names, with the range a1:n2001

The transferspreadsheet ommand, in Access 2002 sp2, I get a dialog box
saying that fields in two records were deleted. THIS IS FINE AND EXPECTED.

In access 2003, I do NOT get this dialog box, and the data does not
import correctly. I end up with data that looks mostly ok, but does not
process right.

I have a query with a single criteria;
WHERE (((CCDataDump.[MCC Code])<>"0000"));

If the data was pulled under 2002, this query works fine. About 6 in
300 records have "0000" in this field, so I get about 294 records.
If the data was pulled in via 2003, I get NO RESULTS, even though I can
look at the table, and see that almost NO records contain "0000" for
that field. IF I delete that criteria from the query, I get all the
records.

Please explain?

Phil

Reply to this Message

John Nurick - 28 Jul 2006 06:33 GMT

Hi Phil,

My first thought is that you should install the Office 2003 service
packs (at least SP1).

Also: if you're importing to a new table, are the field types identical
in the two versions? If not, read Help on "Initializing the Microsoft
Excel Driver" and look for differences in the relevant registry keys.

>OK, Same database, same spreadsheet, running on two different XP pro
>machines, accessed from a network drive.
[quoted text clipped - 22 lines]
>
>Phil

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Reply to this Message

Phil - 28 Jul 2006 17:32 GMT

I thought about updates, so ran all of them. The 2003 version is now
totally up to date, no difference.

The spreadsheet is being transfered into an existing table. However, I
will look into that help document anyway, perhaps something will spark.

> Hi Phil,
>
[quoted text clipped - 36 lines]
>
> Please respond in the newgroup and not by email.

Reply to this Message

david@epsomdotcomdotau - 29 Jul 2006 14:01 GMT

> WHERE (((CCDataDump.[MCC Code])<>"0000"));
> If the data was pulled in via 2003, I get NO RESULTS,

That tells us that there has been a failure on that column,
and that the value of MCC Code is Null or Error, but
how or why we do not know.

If you are fully patched, the call from Jet to the Excel
IISAM has been replaced with a call to an Access DLL,
(dunno how that works if you have both installed on the
same PC) . But I've got no useful suggestions.

(david)

> OK, Same database, same spreadsheet, running on two different XP pro
> machines, accessed from a network drive.
[quoted text clipped - 22 lines]
>
> Phil

Reply to this Message

Phil - 31 Jul 2006 20:41 GMT

Here is the probelm though. If it is NULL, I should still get results,
because Null <>"0000".
IF it is Error, I would see that when I open the table manually.

You say something that might hold the key, but i am not sure i
understand it.

Are you saying that a 2002 call for data to this spreadsheet, is using
something from an excell library, but a fully patched 2003 copy of
access would be using an Access routine instead?

These installations are installed on two different machines.

>>WHERE (((CCDataDump.[MCC Code])<>"0000"));
>>If the data was pulled in via 2003, I get NO RESULTS,
[quoted text clipped - 38 lines]
>>
>>Phil

Reply to this Message

david@epsomdotcomdotau - 01 Aug 2006 00:01 GMT

No, Null is not <> to "0000". Null is not equal to anything,
but still not 'not equal' to anything. Null is a third state, not
equal, not unequal.

This sometimes causes problems in import from Excel, because
some versions of Access import blank cells as Null, and some
import blank cells as "" empty strings - or that may be the way
the import is configured. Whatever, it doesn't appear to be your
problem.

More likely to be your problem, "0000" has come in as 0,
causing a type comparison error, causing the criteria to fail,
returning 'error' for the criteria instead of 'true', and not including
any records.

This might be a configuration problem. causing the value to come
in as text on one PC, and as a number on the other PC.

For many years, Jet used an Installable ISAM called MSEXCL35
or MSEXCL40.dll for import, export, update of excel spreadsheets.
Following a patent dispute, MS released an Access patch for 2002
and 2003, which replaced the reference to MSEXCL with a
reference to an Access DLL, but did not provide any documentation,
(this seems to be the pattern for Access now) so it's anybodies
guess how Access and Jet work with Excel. Some of my stuff
just stopped working when I applied the patch, but I've got multiple
versions installed, and , lacking any documentation, I've got no idea
if the failure was typical or atypical.

(david)

> Here is the probelm though. If it is NULL, I should still get results,
> because Null <>"0000".
[quoted text clipped - 51 lines]
> >>
> >>Phil

Reply to this Message