Append query doesn't work in 2007

[ragtopcaddy via AccessMonster.com]

The following query works fine in 2003, but gives me data type mismatch
errors in 2007. There are 241 records that have 0s when I run the select
portion of the query in 07, and there are 241 records that can't be appended
due to conversion failure (I believe). Coincidence? I think not!!!

INSERT INTO tblNBV ( Storid, NBV, Remaining, DoR )
SELECT DISTINCT Store, NBV, [Remaining Allowance], #9/9/2008 4:41:44 PM#
FROM xlNBV
WHERE Store In ( SELECT [Store #] FROM tblStoreMaster )

This append query is part of a download process that reads data from a named
range in an Excel worksheet, and appends it to a table in Access. The field
with the 241 0's is [Remaining Allowance], and it actually displays as 241 "-
" characters. Access 2003 had no problem interpreting this field and
appending all 985 records to the table. When I try to run the same procedure
in Access 2007, the .execute statement fails.

Unfortunately, this is happening on my client's Access2007 machine at work,
and I am at home, so I can't duplicate the error here as I don't have
Access2007 here. On my client's machine, I don't currently have web access,
so I have to try to explain from memory.

 

[Dale Fye]

It looks like the source table is actually an Excel worksheet. Is that
correct?

First thing I would do is determine the field types of the fields you are
importing to in tblNBV. My guess is that Access and Excel are not playing
together nicely, and that you will have to explicitly type the fields from
the Excel file into the Access table. Something like (make sure you use the
correct conversion functions):

INSERT INTO tblNBV ( Storid, NBV, Remaining, DoR )
SELECT DISTINCT clng(Store), cint(NBV),
cdbl([Remaining Allowance]),
cdate(#9/9/2008 4:41:44 PM#)
FROM xlNBV
WHERE Store In ( SELECT [Store #] FROM tblStoreMaster )

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[ragtopcaddy via AccessMonster.com]

Dale,

Thanks for the suggestion.

Yes, it is an Excel workbook.

What's puzzling is, the append query works in Acc03, but not in Acc07, even
though the target local table is exactly the same in both installations. It
would appear that Access is getting fussier in it's old age. When I get to
the workstation this morning, I will try reformatting the cells in Excel and
see if 07 finds it any more acceptable.

Regards,

Bill

It looks like the source table is actually an Excel worksheet. Is that
correct?

First thing I would do is determine the field types of the fields you are
importing to in tblNBV. My guess is that Access and Excel are not playing
together nicely, and that you will have to explicitly type the fields from
the Excel file into the Access table. Something like (make sure you use the
correct conversion functions):

INSERT INTO tblNBV ( Storid, NBV, Remaining, DoR )
SELECT DISTINCT clng(Store), cint(NBV),
cdbl([Remaining Allowance]),
cdate(#9/9/2008 4:41:44 PM#)
FROM xlNBV
WHERE Store In ( SELECT [Store #] FROM tblStoreMaster )

The following query works fine in 2003, but gives me data type mismatch
errors in 2007. There are 241 records that have 0s when I run the select

[quoted text clipped - 17 lines]

Access2007 here. On my client's machine, I don't currently have web access,
so I have to try to explain from memory.

 

[Dale Fye]

Bill,

I have found that with each successive version of Access, the application
(don't know whether it is Access or JET) has become less and less tollerant
of datatype mismatches.

I've also found that linking or importing from Excel is problematic, as
Microsoft has not given us the ability to create import specifications for
Excel worksheets, so sometimes the data type in the linked/imported table is
not what we expect it to be.

I don't think reformatting the cells in Excel is going to solve the problem.
I think you are going to have to modify the query to explicitly convert the
Excel data into the correct data type during the insert process.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Dale,

Thanks for the suggestion.

Yes, it is an Excel workbook.

What's puzzling is, the append query works in Acc03, but not in Acc07, even
though the target local table is exactly the same in both installations. It
would appear that Access is getting fussier in it's old age. When I get to
the workstation this morning, I will try reformatting the cells in Excel and
see if 07 finds it any more acceptable.

Regards,

Bill

It looks like the source table is actually an Excel worksheet. Is that
correct?

First thing I would do is determine the field types of the fields you are
importing to in tblNBV. My guess is that Access and Excel are not playing
together nicely, and that you will have to explicitly type the fields from
the Excel file into the Access table. Something like (make sure you use the
correct conversion functions):

INSERT INTO tblNBV ( Storid, NBV, Remaining, DoR )
SELECT DISTINCT clng(Store), cint(NBV),
cdbl([Remaining Allowance]),
cdate(#9/9/2008 4:41:44 PM#)
FROM xlNBV
WHERE Store In ( SELECT [Store #] FROM tblStoreMaster )

The following query works fine in 2003, but gives me data type mismatch
errors in 2007. There are 241 records that have 0s when I run the select

[quoted text clipped - 17 lines]

Access2007 here. On my client's machine, I don't currently have web access,
so I have to try to explain from memory.

 

[Lord Kelvan]

what happens when linking not sure bout importing is that access reads
the first 7 fields and then makes a judgement on what the data type
for that column is. it will then retrieve the data in that format.
if access decides a column in excel is text and come of the values are
numbers it will not retrieve them insted it will display #error!

there are ways to get around this by adding a space before each value
in thoes columns. this can cause statements to fail.

Regards
Kelvan

 

[Dale Fye]

Kelvan

I also saw an article a while back that indicated how to change the registry
to have Access review more records (I thought it said the default is 25) for
each field before it comes to a decision on the data type for the field.
Don't rememeber where I saw it though.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.