Multi Value Insert Error

[tighe]

hope this is the best place for this.

i am running a query, its actually an SQL Statement from Allen Brown's
Creating an Audit Log. and i get an 3825 runtime error that * cannot be used
on an "insert into" query when the source or destination table contains a
multi-value field.

let not get into why i had the mutli-value field but because of the error it
has been deleted, but i still recieve the error.

when i recreate the SQL in a query use of the * creates the error but if i
select all fileds from the table and drag, it works fine. this is not an
option to handle the SQL in that manner if you look at Allens' audit, thanks
Allen.
and thank you in advance for your input.

AC2007/XP

SQL = "INSERT INTO " & sAudTable & " SELECT TOP 1 " & sAudTmpTable & ".*
FROM " & sAudTmpTable & _
" WHERE ([" & sAudTmpTable & "].audType = 'EditFrom') ORDER BY
[" & sAudTmpTable & "].audDate DESC;"

 

[Allen Browne]

If this is an attached table, try:
a) Delete the table in the front end (not the back end.)
b) Compact both the front end and back end
c) Link the table again.

If it's not an attached table, try a compact.

If that still fails, create a new (blank) database, and import everything.

 

[tighe]

Bonnie: thanks for the reply but i had to add the brackets otherwise nothing
was happening, the SQL couldnt reference the right information.

Allen, good advice i went through that with no luck, i ended up recreated
the final table, even thought the Multi Filed had already been deleted, and
it worked.

good stuff, thanks for the reply, creating, and releasing the info Allen.

If this is an attached table, try:
a) Delete the table in the front end (not the back end.)
b) Compact both the front end and back end
c) Link the table again.

If it's not an attached table, try a compact.

If that still fails, create a new (blank) database, and import everything.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

hope this is the best place for this.

i am running a query, its actually an SQL Statement from Allen Brown's
Creating an Audit Log. and i get an 3825 runtime error that * cannot be
used
on an "insert into" query when the source or destination table contains a
multi-value field.

let not get into why i had the mutli-value field but because of the error
it
has been deleted, but i still recieve the error.

when i recreate the SQL in a query use of the * creates the error but if i
select all fileds from the table and drag, it works fine. this is not an
option to handle the SQL in that manner if you look at Allens' audit,
thanks
Allen.
and thank you in advance for your input.

AC2007/XP

SQL = "INSERT INTO " & sAudTable & " SELECT TOP 1 " & sAudTmpTable & ".*
FROM " & sAudTmpTable & _
" WHERE ([" & sAudTmpTable & "].audType = 'EditFrom') ORDER BY
[" & sAudTmpTable & "].audDate DESC;"

.