Many Create Table Statements

  • Thread starter Thread starter Brett Baisley
  • Start date Start date
B

Brett Baisley

Hello

I am trying to use SQL to create my tables in a Access db, but I am having
trouble. The only way I can get to an SQL editor is by creating a black
query and setting it to SQL View. So that is what I did. Now I have all of
my SQL statements in there, but when I try to run it, it doesn't work. I get
an Syntax error in Create Table Statement.

Its a simple thing, just so I can get the hang of it:

Create Table Artist (ArtistID int, Name text, Primary Key (ArtistID));

Create Table Albums ( AlbumID int, Name text, ArtistID int, Primary Key
(AlbumID), Foreign Key (ArtistID) References Artists(Name));

But it won't do the second one. Any suggestions?
 
Brett said:
Hello

I am trying to use SQL to create my tables in a Access db, but I am having
trouble. The only way I can get to an SQL editor is by creating a black
query and setting it to SQL View. So that is what I did. Now I have all of
my SQL statements in there, but when I try to run it, it doesn't work. I get
an Syntax error in Create Table Statement.

Its a simple thing, just so I can get the hang of it:

Create Table Artist (ArtistID int, Name text, Primary Key (ArtistID));

Create Table Albums ( AlbumID int, Name text, ArtistID int, Primary Key
(AlbumID), Foreign Key (ArtistID) References Artists(Name));
Click to expand...

Shouldn't it be Artist(Name) instead of Artists(Name)
 
Hi Brett,

just tested with

Create Table Artist (ArtistID int CONSTRAINT Index1 PRIMARY KEY, Name text);

and

Create Table Albums ( AlbumID int CONSTRAINT Index1 PRIMARY KEY, Name
text, ArtistID int CONSTRAINT Key1 References Artist(ArtistID));

Working, was the reference to Artist(Name) instead of Artist(ArtistID)

HTH Claus
 
Are you running those from with MS Access, all in the same SQL query? I
still can't get that to work.
 
Running from the query window of Access 97, both queries in separate
windows. You can run it also from a stringvariable in VB by using
'docmd.runsql'.

See code below as example :

Option Compare Database
Option Explicit

Public Function CreateTables()
Dim myStr1 As String
Dim myStr2 As String

myStr1 = "Create Table Artist (ArtistID int CONSTRAINT Index1
PRIMARY KEY, Name text);"
myStr2 = "Create Table Albums ( AlbumID int CONSTRAINT Index1
PRIMARY KEY, Name text, "
myStr2 = myStr2 & "ArtistID int CONSTRAINT Key1 References
Artist(ArtistID));"

DoCmd.RunSQL myStr1
DoCmd.RunSQL myStr2
End Function

If you need a .mdb as example just mail me (without the NO.SPAM. in the
E-Mail address and I'll send it to you.

HTH, Claus
 
I am trying to use SQL to create my tables in a Access db, but I am having
trouble. The only way I can get to an SQL editor is by creating a black
query and setting it to SQL View. So that is what I did. Now I have all of
my SQL statements in there, but when I try to run it, it doesn't work. I get
an Syntax error in Create Table Statement.
Click to expand...

Unfortunately, unlike many other relational database management
systems, Access does NOT support multiple SQL queries in one block.

To run multiple queries, you'll need to store them as separate
queries, and run them in sequence from a Macro or from VBA code.
 
You can only have ONE DLL statement per Query, NOT multiple statements.

If you want to do multiple statements, you need to write a VBA Sub with
multiple Execute statement, each one running a single JET DLL SQL.
 
Brett Baisley said:
Hello

I am trying to use SQL to create my tables in a Access db, but I am
having trouble. The only way I can get to an SQL editor is by
creating a black query and setting it to SQL View. So that is what I
did. Now I have all of my SQL statements in there, but when I try to
run it, it doesn't work. I get an Syntax error in Create Table
Statement.

Its a simple thing, just so I can get the hang of it:

Create Table Artist (ArtistID int, Name text, Primary Key (ArtistID));

Create Table Albums ( AlbumID int, Name text, ArtistID int, Primary
Key (AlbumID), Foreign Key (ArtistID) References Artists(Name));

But it won't do the second one. Any suggestions?
Click to expand...

Access won't let you run multiple SQL statements in a single query. One
solution is to build an array of SQL statements as strings, and then
loop down the array using the DAO Execute method to run them. For
example,

Dim astrSQL(10) As String
Dim intX As Integer
Dim intLast As Integer
Dim db As DAO.Database

astrSQL(0) = _
"Create Table Artists (ArtistID int, " & _
"Name text, Primary Key (ArtistID));"

astrSQL(1) = _
"Create Table Albums ( AlbumID int, Name text, " & _
"ArtistID int, Primary Key (AlbumID), " & _
"Foreign Key (ArtistID) References Artists(Name));"

intLast = 1 ' last subscript used

Set db = CurrentDb

For intX = 0 to intLast
db.Execute astrSQL(intX), dbFailOnError
Next intX

Set db = Nothing
 
You can only have ONE DLL statement per Query, NOT multiple statements.

If you want to do multiple statements, you need to write a VBA Sub with
multiple Execute statement, each one running a single JET DLL SQL.
Click to expand...
what do you want
 
Back
Top