‘CREATE TABLE’ generates Run-time error ‘3290’

I have a syntax issue in the first CREATE TABLE statement.

I’m receiving the following VBA error:

Run-time error ‘3290’

The goal is to move the distinct data to a new table dependent on values in specific columns. Afterwards the original table is cleared, and every distinct value will be inserted again. The temporary table will be deleted afterwards.

' ** Issue here ** '
db.Execute ("CREATE TABLE tTemp AS (SELECT DISTINCT History_Date, Sedol, Selskabsnavn, MarketCap, JQScore, JQ_Rank, Value_Rank, Quality_Rank, Momentum_Rank FROM JQHistory)")

db.Execute ("DELETE * FROM JQHistory")
db.Execute ("SELECT * FROM tTemp INTO JQHistory")
db.Execute ("DROP TABLE tTemp")

This code is being run from within MS Excel.

Answer

You already have the syntax to make this work in the third statement, although it’s not quite correct.

The first line should be db.Execute ("SELECT DISTINCT <list of fields> INTO tTemp FROM JQHistory")

The third statement should be: db.Execute ("INSERT INTO JQHistory SELECT <list of fields> FROM tTemp")

Advertisement

Answer