SQL - Copy multiple fields, multiple row to an existing table 2

[leonepaolo]

Hi,

I can't figure out how to code the SQL to copy multiple fields from multiple rows to an existing table.

sDestinationFields = "fld1, fld2, fld3"
sSourceFields = "fld1, fld2, fld3"

sql = "INSERT INTO " & tblDestination & "(" sDestinationFields & ") " & _
"SELECT " & sSourceFields & " FROM " & tblSource

I don't understand why this doesn't work because when I use it for just one field it works well.

Any comments or suggestions would be greatly appreciated.

Thanks,
Paolo

 

[Golom]

You're missing an ampersand from the string but I assume that's just a typo.

sql = "INSERT INTO " & tblDestination & "(" [COLOR=red]& [/color] sDestinationFields & ") " & _
"SELECT " & sSourceFields & " FROM " & tblSource

Other than that I don't see why it shouldn't work.

what do you see if you do a

Debug.Print sql



[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[leonepaolo]

Hi Golom,

You're right, the missing ampersand was just a typo.

I don't know how to make Debug.Print sql (or any other Debug.Print) work.

I do get Run-time error '3134'
Application-defined or object-defined error

Thanks for your help.

 

[Golom]

Debug.Print doesn't work? Never heard of that.

Set a breakpoint on the line following this one and then open the immediate window (Ctrl-G) and type

? sql

press ENTER and then look at the sql statement that you are attempting to use.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[leonepaolo]

Hi Golom,

Thanks for teaching about the Debug.Print, I'll be using that, wish I knew about it sooner.

The table I was exporting to had a '-' int its name. I figured I'd try taking it out and sure enough, it's like it always worked.

Thanks for your help, I do appreciate it,
Paolo

 

[PHV]

So, the correct way was:
sql = "INSERT INTO [" & tblDestination & "] (" & sDestinationFields & ") " & _
"SELECT " & sSourceFields & " FROM [" & tblSource & "]"

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

 

[leonepaolo]

Thanks PHV,
I guess all those brackets and braces aren't for nothing.
Paolo