CREATE TABLE function

[jovana]

Hi I am doing a distance learning course and was asked to create a table within an existing database using these SQL commands;

CREATE TABLE Vehicle (reg_no TEXT(10)
CONSTRAINT VehicleKey PRIMARY KEY,
type TEXT(10),
purchase_date DATETIME,
last_service_date DATETIME, mileage_at_last_service INTEGER);

I was told to save the query and check the new table had the required records in it.

I had huge problems when I tried to save the query and kept getting error messages like:
"query must have at least one destination field" and "syntax error in field destination". This was strange since I was sure I typed the query exactly as it had been written on the worksheet.

Having not changed the SQL command at all it eventually worked when I skipped the 'save query" stage and just went to the "run query" stage. I still don't know why it actually worked in the end and why I kept getting error messages.[FONT=Times New Roman,serif][FONT=Arial,sans-serif] By running the query am I supposed to just click on "RUN" or can I check the information first by looking at datasheet view? :banghead:





[/FONT][/FONT]

 

[Trevor G]

I would always suggest using the RUN query. Save is purely doing what it says just saving not creating or running the action.

 

[jovana]

Thanks, just a question though. Can you view the SQL statement in datasheet view prior to running the query?

 

[Trevor G]

What is actual the SQL code you are using.

 

[jovana]

Sorry not sure what you mean by SQL code. Do you mean the SQL statement (which I posted in my first post) or the type of SQL statement? Which would be CREATE TABLE or the error message code? I kept getting "query must have at least one destination field" and "syntax error in field destination.
For some reason the SQL query is working fine now but wasn't before, even though I did not change a thing.

I just wanted to know if it is possible to view the SQL statement for CREATE TABLE in datasheetview before executing aquery, as when you so other queries you can skip between datasheet view and SQL view easily.

 

[CJ_London]

Can you view the SQL statement in datasheet view prior to running the query?

No - it is not a regular query, but one called a data definition query.

You are probably not able to save it because it is not syntactically correct - your constraint line should be

VehicleKey Integer PRIMARY KEY,

Also, once you have run the query, at this point, there are no records - you would be checking the new table has the required fields in it.

 

[jovana]

Ok, that's very helpful thanks.