SQL code to create a table

[nst]

Hi,

a ER programm gives the following output to an sql code, which it pretends it should be for Access 97. Till now I haven't found an idea of how to create a table using the SQL statement CREATE. As a matter of fact it returns me an error of Create statement or something, even if I tried any available query form.

CREATE TABLE Algorithm_Property(
Algorithm_Property_ID Integer NOT NULL,
Layout_Property_Name_ID Integer NOT NULL,
Data_Type_ID Integer NOT NULL,
Submodel_Algorithm_ID Integer NOT NULL,
AlgPropValue Text(255) NOT NULL,
Global_User_ID Integer NOT NULL,
Row_Time_Stamp Integer NOT NULL
)
;
CREATE TABLE AppGenAttribute(
AppGenAttribute_ID Integer NOT NULL,
Attribute_ID Integer NOT NULL,
DiagramId Integer NOT NULL,
ModelId Integer NOT NULL,
EntityId Integer NOT NULL,
AttributeId Integer NOT NULL,
InitialValueType Text(1) NOT NULL,
ClientDefaultValue Text(254),
CounterColumn Text(1),
Global_User_ID Integer,
Row_Time_Stamp Integer
)
;

 

[Sergeant]

The script you posted would be used to create two tables on an existing SQL Server database.
Of course, you could connect to the tables in SQL Server using an Access .adp (project) file.

 

[nst]

But Access is SQL capable, isn't it. You can always write directly in a query
SELECT * FROM tablename and you will get all the rows of the table.

Is it impossible to handle CREATE?

 

[Sergeant]

OK...(I'm not a professor of computers, languages, or applications, but...)
Structured Query Language is a language, just like it says.

SQL Server, MySQL, etc... are data storage applications (databases).

Access uses the Jet database engine for its native tables...Access is the designated front end for Jet.

...They all share the SQL language for querying them.

 

[nst]

Thanks, I got what you mean.

Still, I would like to know if there is a way to have an Access DB created from a plain text file (which, I guess, should be an Sql file), instead of creating the db first in a MySql or Ms Sql server and then transferring it into Access via ODBC or another 3rd party utility.

 

[SammyJ]

You can execute a CREATE TABLE statement easily enough. Not sure about through the query builder, but in code it is simple enough. Load your statement into a string variable, create a database variable, and execute it.

strSQL = "CREATE ..."
set db = CurrentDB
db.Execute strSQL

Go crazy.

(You might want to break the SQL statements into separate instructions. Not sure. Experiment and let me know!)

 

[Pat Hartman]

Access can only execute a single SQL statement in each query. So you'll either have to cut and paste the text file into multiple queries or write the code to do it for you.