Key Violation using TransferSpreadsheet

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

Guest

I have an Access procedure that uses TransferSpreadsheet to transfer the
results of four queries to separate sheets in the same Excel file. It worked
as expected until I added one more field (the same field) to each query. Now
the results of the first two queries are exported out as expected, but for
last two queries I get an error message:

The contents of fields in 16 records(s) were deleted, and 0 record(s) were
lost due to key violations.

The results of the last two queries are exported--all except the contents of
the new field that I added. All four queries execute as expected when run in
Access. How can there be a key violation in the Excel spreadsheet I am
exporting to (for two queries, but not the other two)?

Any help would be appreciated!
Judy
 
Hi Judy,

Are you exporting the four queries to a new workbook each time, or using
the same workbook every time? If the latter, what happens if you export
to a new workbook rather than the existing one?
 
Thank you for your response. I tried exporting just one of the spreadsheets
I am having trouble with to a new Excel file and get the same error.

What is baffling me is that if I remove the field "SWIT IR" all four queries
export out as expected. If I add the field "SWIT IR" (exact same field), two
of the queries export out without errors and two don't. This field is
actually the value of a combo box on the form:
SWIT IR: Forms!frmMain!cboSWIT_IR

I copied and pasted the same thing in each of the four queries, why would it
work for two and not the other two? This is driving my crazy (I have even
tried rebooting my system)!

I would sure appreciate any other suggestions.
Thank you,
Judy
 
I really don't know what's happening.

1) Do all four queries behave normally if you simply open them rather
than export with TransferSpreadsheet? When you do that, is there any
difference in the value of the [SWIT IR] field between one query and
another?

2) Please post the SQL from one of the "good" queries and one of the
"bad ones": this may give someone a clue.
 
My problem is solved, thanks to you. All four queries behave normally when I
simply open them rather than export them, and the value of SWIT IR is exactly
the same for all four queries. In total frustration I opened up the queries
in SQL view so that I could post them here for you and saw what the
difference was between the two queries that behaved and the two that didn't.
The two that didn't export as expected started with "SELECT DISTINCT". I
still don't know why that would cause an error (doesn't make any sense to
me), but after removing DISTINCT I don't get the error (and hopefully I won't
get any duplicate rows).

Thank you very much for responding. Hopefully I can remember to check my
SQL in the future. I tried to reply yesterday, but the network was so bad
(either on my end or the Microsoft end) my request to reply kept timing out.

Judy

John Nurick said:
I really don't know what's happening.

1) Do all four queries behave normally if you simply open them rather
than export with TransferSpreadsheet? When you do that, is there any
difference in the value of the [SWIT IR] field between one query and
another?

2) Please post the SQL from one of the "good" queries and one of the
"bad ones": this may give someone a clue.



Thank you for your response. I tried exporting just one of the spreadsheets
I am having trouble with to a new Excel file and get the same error.

What is baffling me is that if I remove the field "SWIT IR" all four queries
export out as expected. If I add the field "SWIT IR" (exact same field), two
of the queries export out without errors and two don't. This field is
actually the value of a combo box on the form:
SWIT IR: Forms!frmMain!cboSWIT_IR

I copied and pasted the same thing in each of the four queries, why would it
work for two and not the other two? This is driving my crazy (I have even
tried rebooting my system)!

I would sure appreciate any other suggestions.
Thank you,
Judy
Click to expand...
Click to expand...
 
Hi Judy,

I can't imagine why the presence or absence of DISTINCT should interfere
with the exporting. Maybe someone else reading this will have a
suggestion.



My problem is solved, thanks to you. All four queries behave normally when I
simply open them rather than export them, and the value of SWIT IR is exactly
the same for all four queries. In total frustration I opened up the queries
in SQL view so that I could post them here for you and saw what the
difference was between the two queries that behaved and the two that didn't.
The two that didn't export as expected started with "SELECT DISTINCT". I
still don't know why that would cause an error (doesn't make any sense to
me), but after removing DISTINCT I don't get the error (and hopefully I won't
get any duplicate rows).

Thank you very much for responding. Hopefully I can remember to check my
SQL in the future. I tried to reply yesterday, but the network was so bad
(either on my end or the Microsoft end) my request to reply kept timing out.

Judy

John Nurick said:
I really don't know what's happening.

1) Do all four queries behave normally if you simply open them rather
than export with TransferSpreadsheet? When you do that, is there any
difference in the value of the [SWIT IR] field between one query and
another?

2) Please post the SQL from one of the "good" queries and one of the
"bad ones": this may give someone a clue.



Thank you for your response. I tried exporting just one of the spreadsheets
I am having trouble with to a new Excel file and get the same error.

What is baffling me is that if I remove the field "SWIT IR" all four queries
export out as expected. If I add the field "SWIT IR" (exact same field), two
of the queries export out without errors and two don't. This field is
actually the value of a combo box on the form:
SWIT IR: Forms!frmMain!cboSWIT_IR

I copied and pasted the same thing in each of the four queries, why would it
work for two and not the other two? This is driving my crazy (I have even
tried rebooting my system)!

I would sure appreciate any other suggestions.
Thank you,
Judy

:

Hi Judy,

Are you exporting the four queries to a new workbook each time, or using
the same workbook every time? If the latter, what happens if you export
to a new workbook rather than the existing one?



On Thu, 18 May 2006 16:51:01 -0700, Judy Ward

I have an Access procedure that uses TransferSpreadsheet to transfer the
results of four queries to separate sheets in the same Excel file. It worked
as expected until I added one more field (the same field) to each query. Now
the results of the first two queries are exported out as expected, but for
last two queries I get an error message:

The contents of fields in 16 records(s) were deleted, and 0 record(s) were
lost due to key violations.

The results of the last two queries are exported--all except the contents of
the new field that I added. All four queries execute as expected when run in
Access. How can there be a key violation in the Excel spreadsheet I am
exporting to (for two queries, but not the other two)?

Any help would be appreciated!
Judy
Click to expand...
Click to expand...
Click to expand...
 
Back
Top