SQL INSERT INTO Difficulty with use of variable

[byTimber]

Hi All,

Can anyone help here:
Public strOldTable,strNewTable as string

OldTable has data, NewTable is empty but with same field structure.

I assign names of tables to these variables and attempt to fill up the NewTable with the data from the OldTable using :

DoCmd.RunSQL "INSERT INTO ('" & strNewTable & "') SELECT * FROM " & ('" & strOldTable & "')

Driving me crazy; I can find examples of use of the OldTable variable but not of use of the NewTable in this SQL.

Please.....

 

[JHB]

What about, (I haven't tried it):

INSERT INTO [" & strNewTable & "] SELECT * FROM [" & strOldTable & "]"

 

[byTimber]

Thanks for response but produces "Expected end of Statement" at first Square Bracket.

 

[bob fitz]

Try:

CurrentDb.Execute "INSERT INTO " & strNewTable & " SELECT * FROM " & strOldTable

 

[byTimber]

Thanks Bob; that did the trick - now for Sunday lunch thanks to you ....

 

[bob fitz]

Thanks Bob; that did the trick - now for Sunday lunch thanks to you ....

You're welcome. Enjoy your lunch. Roast beef with yorkshires?

 

[JHB]

Thanks for response but produces "Expected end of Statement" at first Square Bracket.

Of course, DoCmd.RunSql must stand in front, I didn't think it was necessary to mention it, but ...

DoCmd.RunSQL "INSERT INTO [" & strNewTable & "] SELECT * FROM [" & strOldTable & "]"

But you got your problem solved, it is the main thing.

 

[bob fitz]

While DoCmd.RunSQL will work, CurrentDb.Execute is considered by many to be the better option. See http://www.utteraccess.com/wiki/index.php/RunSQL_vs_Execute