CODEWITHSUNDEEP
sqlexcelvbams-access
piele
piele

Reputation: 78

'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.

Upvotes: 1

Views: 818

Answers (2)

piele
piele

Reputation: 78

Final solution:

db.Execute ("SELECT DISTINCT History_Date, Sedol, Selskabsnavn, MarketCap, JQScore, JQ_Rank, Value_Rank, Quality_Rank, Momentum_Rank INTO tTemp FROM JQHISTORY ORDER BY History_Date")
db.Execute ("DELETE * FROM JQHistory")
db.Execute ("ALTER TABLE JQHistory ALTER COLUMN Id COUNTER (1, 1)")
db.Execute ("INSERT INTO JQHistory SELECT * FROM tTemp")
db.Execute ("DROP TABLE tTemp")

Upvotes: 1

NeepNeepNeep
NeepNeepNeep

Reputation: 913

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")

Upvotes: 0

Related Questions