Getting an error 3825, complaining about multi-valued fields, but none are involved. (2 Viewers)

[Papa_Bear1]

So, I'm getting an error 3825 from Access while running an INSERT INTO query, and the error says "SELECT * cannot be used in an INSERT INTO query when the source or destination table contains a multi-valued field."

This is quite strange since neither table has any multi-valued fields.

I found one other thread on this where someone apparently saw this, and wondered if there was some ghost attribute of the table that was making Access think there was an MV field when there wasn't, but I've seen no fixes or comments on how to resolve it. In fact, in this case, I'm fairly certain that the tables involved NEVER had an MV field, so it is quite puzzling.

Any suggestions on how to perhaps 'clean' the tables to ensure Access doesn't think this?
I intend to try to list the fields next, rather than using the *, but that is a pain - not to mention just slows things down.

The code that constructs the SQL is as follows:

strSQL = "INSERT INTO [" & strOutPath & strOutFile & "].[" & strOutTableName & "] " _
            & "SELECT X.* FROM " & strSource & " AS X"

In case it helps any to describe the purpose - it is trying to simply copy some data from a local table, to a table in an external Access DB. In this case, the strSource variable refers to a SELECT query (so that I can limit the output rows to the current record basically.) So, it ends up looking something like "INSERT INTO [C:\BLAHBLAH\BLAH.accdb].

SELECT X.* FROM qryGetAnalysisData AS X.

Thanks for any insight!

 

[MajP]

Should that be of the form

    INSERT INTO external_table IN 'C:\Path\To\ExternalDB.accdb ...

not

    INSERT INTO '[C:\Path\To\ExternalDB.accdb].[tableName]'...

INSERT INTO Statement - Microsoft Support

Use INSERT INTO to add a single record to a table or to append a set of records from another table or query.

support.microsoft.com

 

[arnelgp]

i believe strOutFile is the externa db name,

'make sure to add backspace to OutPath
strOutPath = Replace$(strOutPath & "\", "\\", "\")

strSQL = "Insert Into [" & strOutTableName & "] In '" & strOutPath & strOutFile & "' " & _
        "Select X.* from [" & strSource & "] As X;"

 

[Gasman]

Someone else had the same message recently. Search here for it.

 

[cheekybuddha]

Someone else had the same message recently. Search here for it.

I found one other thread on this where someone apparently saw this, and wondered if there was some ghost attribute of the table that was making Access think there was an MV field when there wasn't, but I've seen no fixes or comments on how to resolve it.

It appears they did search for it

 

[Papa_Bear1]

Should that be of the form

    INSERT INTO external_table IN 'C:\Path\To\ExternalDB.accdb ...

not

    INSERT INTO '[C:\Path\To\ExternalDB.accdb].[tableName]'...

INSERT INTO Statement - Microsoft Support

Use INSERT INTO to add a single record to a table or to append a set of records from another table or query.

support.microsoft.com

Well - the code works on some tables and not others. I tried your alternate syntax with the same results. It seems to pick some tables to complain about, but not others.

 

[Papa_Bear1]

i believe strOutFile is the externa db name,

'make sure to add backspace to OutPath
strOutPath = Replace$(strOutPath & "\", "\\", "\")

strSQL = "Insert Into [" & strOutTableName & "] In '" & strOutPath & strOutFile & "' " & _
        "Select X.* from [" & strSource & "] As X;"

Indeed - the backslash has gotten me more than once. In this case, however, it works sometimes and not other times. Can't figure out why Access thinks some tables have MV fields - that don't.

 

[Minty]

If any of the destination and source tables have an Identity (Autonumber) field it would fail, however I would expect it to tell you that as the error message, not one about MV fields?

 

[arnelgp]

Indeed - the backslash has gotten me more than once. In this case, however, it works sometimes and not other times. Can't figure out why Access thinks some tables have MV fields - that don't.

did you try the "code", because it is different from what you have.

 

[Papa_Bear1]

If any of the destination and source tables have an Identity (Autonumber) field it would fail, however I would expect it to tell you that as the error message, not one about MV fields?

Hmmmmm... I'm pretty sure I do not have any Autonumber fields in these scenarios. I did set a field to be a key field, but I should be able to delete/add rows without THAT affecting it.

 

[Papa_Bear1]

did you try the "code", because it is different from what you have.

Not exactly sure what you mean.
My backslashes are in order... as it works for some tables and not others.
So, I'm pretty sure this is not an all-or-nothing syntax thing - it is a hard-to-identify apparent randomness on when Access deems a table to have MV fields - or so the error message implies. (...probably another erroneous error message I guess...)

 

[arnelgp]

My backslashes are in order... as it works for some tables and not others.

it's not about the backslash, it's about the code actually. it is different from what code you posted.

 

[Ken Sheridan]

If any of the destination and source tables have an Identity (Autonumber) field it would fail, however I would expect it to tell you that as the error message, not one about MV fields?

There is nothing to stop an INSERT INTO statement from inserting values from an autonumber column in one table into an autonumber column in another table, provided that the unique index on the column in the destination table is not violated. Autonumber is not a data type per se, the data type is a long integer number, but with a mechanism which inserts a value automatically if a value is not inserted by other means. A value cannot be inserted manually, but it can be in SQL.

If the destination table is empty, as I assume is the case here, then any values can be inserted. To test this I created a copy of the structure of a Cities table as CitiesX and executed the following statement:

INSERT INTO
    CitiesX
SELECT
    Cities.*
FROM
    Cities;

All rows were inserted successfully, with the autonumber CityID column repeating the non-sequential values from the source table.

 

[MajP]

Any chance there is an attachment field? Which is a MV field but many people do not call it one so might not have been recognized as one.

 

[Papa_Bear1]

it's not about the backslash, it's about the code actually. it is different from what code you posted.

Ahh --- if you mean the alternate syntax provided by @MajP, then yes - I tried that. It performed the same as what I had, and did not resolve the error... if that is what you meant.

 

[Papa_Bear1]

There is nothing to stop an INSERT INTO statement from inserting values from an autonumber column in one table into an autonumber column in another table, provided that the unique index on the column in the destination table is not violated. Autonumber is not a data type per se, the data type is a long integer number, but with a mechanism which inserts a value automatically if a value is not inserted by other means. A value cannot be inserted manually, but it can be in SQL.

If the destination table is empty, as I assume is the case here, then any values can be inserted. To test this I created a copy of the structure of a Cities table as CitiesX and executed the following statement:

INSERT INTO
    CitiesX
SELECT
    Cities.*
FROM
    Cities;

All rows were inserted successfully, with the autonumber CityID column repeating the non-sequential values from the source table.

OK - good to know.
I vaguely recall being able to insert rows into a table with Autonumber, (including values for that field) but I also recall that things kind of got wonky sometimes - so my approach since then has been to either use Autonumber - and let it do what it wants, or don't use it, and manage IDs myself!

Having said that - due to a permissions issue on an external DB recently, I did try running a routine to reset that Autonumber counter (entirely different table!) - rather than my usual destroying of the table and making a new copy from a template that has a zero counter (having never had any rows/date.) To my surprise, it seems to be working - which is great as my usual approach was no longer viable. (If you're wondering why I care about the ID - I just don't want to spiral out of control. It is an application that will process thousands of records at a time, so the re-use of a table with the Autonumber could get a little crazy.)

In this particular case - Autonumber is not a factor, though, as the tables of interest have neither Autonumber fields, nor any MV fields.

In case it helps anyone, I did try something I saw that was suggested (somewhere)- which is to include a field list, rather than the *, and it seems to be working. Kind of annoying - but at least it seems to be working.

Aside: I did take a peek at table Relationships - thinking maybe the table of interest was related to a table with MV fields. Two things were odd: 1) I did find a different table linked to a table that does have MV fields, and I never told it to relate them. Weird. and 2) The table I did find that was related to the table with an MV field was NOT the one it was complaining about anyway.
I would still like to know what Access is thinking in this case.

 

[Papa_Bear1]

Any chance there is an attachment field? Which is a MV field but many people do not call it one so might not have been recognized as one.

In this case - No. The tables are relatively "normal".

 

[MajP]

Since you do not specify any columns are your completely sure that all the fields line up and same data type?

 

[Papa_Bear1]

Since you do not specify any columns are your completely sure that all the fields line up and same data type?

I did a scan of that - comparing the fields in both tables, and they all looked the same.
I actually created the external destination table using a query from an empty version of the source - so unless my empty version is somehow mismatched (which has definitely happened when I change a table and forget to update that template) - I think they are matched.

A good question though, as the fact that using fieldnames works might imply otherwise.

 

[Papa_Bear1]

Just an FYI ---

I tried running the same type of query on the two databases again, manually, with both of them open --- and now it magically works.

This has me thinking that it may be a permissions issue of some kind, perhaps with an erroneous error message.

It seems that if I create the DB file on-the-fly, insert tables into it on-the-fly, and then try to insert data into those tables - again on-the-fly - somewhere along the way - this process breaks. Why this would happen on some tables and not others still escapes me. If it really was an overall permission issue - it seems to me it would either work or not.

This does seem to prove, though, that there really is indeed nothing inherently wrong with the SQL itself.