Run time error 3346: No. of query values and dest fields not same

[jrickard]

Hi folks

I'm trying to use a DoCmd runSQL to insert recordset results into a table. I can do it successfully using a rst.addnew method if I declare the table as a recordset however I'd rather use the SQL Method.

I can provide the code if need be - just hoping someone may be familiar with this straight off.

Thanks
Jon

 

[PHV]

The number of items in the VALUES list (or the SELECT clause) must be the same as the number of items in the columns list of the table you want insert to.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jrickard]

That's the strange thing - I just can't understand why it would be giving me that error. Here's the SQL:

DoCmd.RunSQL ("INSERT INTO CONC_INTERESTS (ID, INTERESTS) VALUES ('" & strAccountno & "', '" & strInterests & "') ")

The only fields in the CONC_INTERESTS table are the ID and INTERESTS table.

Hopefully I'm just missing something simple...

 

[willir]

Is strAccountno and the target field both text strings?

 

[jrickard]

PS Thanks for the reply PHV

The process was actually inserting 92 records into the fields, so I looked at the unique ID immediately after these records, which is:

A2051864567! 66! Bar

This is suspiciously like the way we reference objects in Access, and once I removed this code from the source table (a copy) the process ran through all the records.

I thought that treating the variables as strings would avoid the problem of such characters within the string.

So my problem then becomes:

Does anyone know how to deal with strings that contain 'reserved' characters? Is there a way I should be bracketing the string somehow?

 

[willir]

You can try...

Dim strQ as String

strQ = Chr$(34)

DoCmd.RunSQL ("INSERT INTO CONC_INTERESTS (ID, INTERESTS) VALUES (" _
& strQ & strAccountno & strQ ", " & strQ & strInterests & strQ & ") ")

 

[jrickard]

Thanks willir, I tried this, but no joy.

For some reason VBA/SQL just doesn't seem to treat the string variable as one variable - it's continuing to evaluate the contents of the string and split it up into more than one string in some cases.

Grrrrr

 

[willir]

Okay, some more debugging...

One typo, missed an "&", in blue.
Added a STOP and a Debug.Print - use CTRL-G to see results of print out.

Dim strQ as String, strSQL as String

strQ = Chr$(34)

[COLOR=blue]STOP[/color]

strSQL = "INSERT INTO CONC_INTERESTS (ID, INTERESTS) VALUES (" _
& strQ & strAccountno & strQ [COLOR=blue]&[/color] ", " _
& strQ & strInterests & strQ & ")"

[COLOR=blue]debug.print strSQL[/color]

DoCmd.RunSQL strSQL

I tested the code - works fine with my data, and tested it with your rather unusual "A2051864567! 66! Bar".

You can copy the Debug.Print output in the Query Builder (SQL view), and test it here too.

Assumptions...
strAccountno is a text string and has been defined correctly
Ditto for strInterests.

Sorry about the missing "&" in my previous post -- haste makes waste.

Richard