create table and indexes in the same command

Question

create table and indexes in the same command

roy.tollison

SSCommitted

Points: 1930
More actions
September 13, 2010 at 8:40 am

#229224

how do i create the table and indexes in the same command...
create table employees
(emp_number integer constraint PK_emp_number primary key,
emp_lastname text(30) constraint idxemp_lastname index,
emp_fistname text(30))
I don't want the lastname to be unique may have lots of 'SMITH's working for customer. i just want to index it for faster searches.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Philipp Post Default port Points: 1401 More actions · Answer 1

I am using a SQL Scratchpad form for executing all SQL commands via ADO (much better SQL support than through the Access UI)

If you pass CREATE TABLE .... ; followed by CREATE INDEX ....; and whatever else you can execute them in one go.

Basically the code is executed like this:

Private Sub ExecuteSQL(byval SQL_Command as string)

Dim DB_Connection As ADODB.Connection

Dim SQL_Statements() As String

Dim i As Long

'create an array with the SQL statements

SQL_Statements() = Split(SQL_Command, ";")

'establish an ADO connection to the same database

Set DB_Connection = Application.CurrentProject.Connection

'process all SQL statements in the array

For i = 0 To UBound(SQL_Statements) - 1

DB_Connection.Execute SQL_Statement

Next

DB_Connection.Close

End Sub

brgds

Philipp Post

grovelli-262555 SSCrazy Points: 2633 More actions · Answer 2

grovelli-262555

SSCrazy

Points: 2633

September 23, 2010 at 4:00 am

#1226192

Hi Philipp, where do you get this SQL Scratchpad?

grovelli-262555 SSCrazy Points: 2633 More actions · Answer 3

By the way, shouldn't it be

DB_Connection.Execute SQL_Statement(i)?

Philipp Post Default port Points: 1401 More actions · Answer 4

grovelli-262555 (9/23/2010)
By the way, shouldn't it be
DB_Connection.Execute SQL_Statement(i)?

Yes, right. I copied it out of my (home made) scratchpad and simplified it a bit for easier reading. Obviously this was lost.

It should read DB_Connection.Execute SQL_Statements(i)

The scratch pad is not very difficult to make. Just create a new form with a text box and a button and put this code behind the form and make the button execute it with the input from the text box.

One thing I like is that you can create table CHECK constraints (i. e. those which depend on more than one column like start_date < end_date) what you otherwise could not. However you will have to remember the name of the constraint to drop it again (it will not display anywhere in the Access UI). You can also create CHECK constraints with a SELECT statement in it (what neither SQL Server nor DB2 could). What I do not use are creating VIEWs (you can simply use the saved queries as a VIEW) and creating stored procedures (just very simple ones are possible). I put such SQL in a VBA module and execute it from there.

brgds

Philipp Post

grovelli-262555 SSCrazy Points: 2633 More actions · Answer 5

Thanks Philipp,

can you give me an example of a CHECK constraints with a SELECT statement in it?

Philipp Post Default port Points: 1401 More actions · Answer 6

grovelli-262555 (9/25/2010)
Thanks Philipp,
can you give me an example of a CHECK constraints with a SELECT statement in it?

ALTER TABLE Customers

ADD CONSTRAINT LimitRule

CHECK (CustomerLimit <= (SELECT MAX(Limit)

FROM CreditLimit));

The ISO/ANSI standard has this. The standard also has CREATE ASSERTION which does a similar job but is external to the tables. All would be nice to have but we are stuck with Triggers and VIEWs with CHECK options in those RDBMS.

brgds

Philipp Post