Check SQL please

[Ken]

I am trying to create a backup table in my Access program from another
application via ADO.
I can "drop" the existing table, but am getting a syntax error when trying
to create the new table and can't figure out why.
Thanks in advance for any help.
CODE:
On Error GoTo create
Cnn.Execute ("DROP TABLE Locationsbkup ")
create:
Cnn.Execute ("Create table " & tablename & " AS (Select Locations.*
from Locations where 1=2)")
Cnn.Execute ("SELECT Locations.* INTO" & tablename & "FROM
Locations;")

 

[vanderghast]

CREATE TABLE is a statement followed by the list of fields, data type and
constraints to be involved . To create a table from a set of data, the
syntaxt is a SELECT INTO:


SELECT listofField INTO newTableName FROM existingDataSource

So, you probably don't need to use the CREATE TABLE statement since you use
a SELECT INTO just after that.

Note that you drop the hard coded name table Locationsbkup, and create a new
one with the name hold in a variable.

You need space around the keywords. As it is right now, you end up with

"SELECT Locations.* INTOMyNewTableNameHereFROM Locations"

which is an invalid statement. Add a space after INTO and before FROM. The
operator & won't do it for you.



Vanderghast, Access MVP

 

[Ken]

Oh Crap!
The "Select ....Into" Was the first thing I tried, but since it didn't work
I thought I needed a table to "select into".

Thanks for the help.