?
Solved

MS Access and DDL -  how to set Indexed, Validation rule...

Posted on 2008-01-26
6
Medium Priority
?
1,264 Views
Last Modified: 2008-05-09
I am trying to create access database (tables, fields and relationships) with SQL queries, and I am done most of the job, but having problems with these:

1) how to set property indexed to: Yes, Duplicates OK (need it for the foreign keys).
2) How to set the Validation rule property? While creating the table, I have tried:

CONSTRAINT constraint_name CHECK (fieldname > 0)

it doesn't throw any error, but when I open database in MS Access, it doesn't show up in design view under the Validation Rule for this field.

3) how to set the Format property of the Yes/No field to one of the 3 given values?

4) I am curious about one thing: what happens when in relationship window I connect two tables and I leave the Enforce referential integrity unchecked (I need it unchecked because its an optional for user to enter)? Are anything created in the background, or is it just shown visually in relationship window to we can know what is related to what?

Thanks,
Goran
0
Comment
Question by:Priest04
  • 3
  • 3
6 Comments
 
LVL 16

Assisted Solution

by:Rick_Rickards
Rick_Rickards earned 400 total points
ID: 20751974
Create a Customer Table Primary Key (Unique Index) that is alos a counter and has a Key Name of "Primary Key".  Also proidies a Long CustoemrID Field and a 64 Character Text field named InvoiceNo and a boolean field named Active...
    strSQL = "CREATE TABLE tblCustomer (CustomerID COUNTER constraint PrimaryKey PRIMARY KEY, CustomerID LONG,  Cusotmer Text(64)), Active as YESNo"

Create a Table with a Primary Key (Unique Index) that is alos a counter and has a Key Name of "Primary Key".  Also proidies a Long CustoemrID Field and a 16 Character Text field named InvoiceNo...
   strSQL = "CREATE TABLE tblOrder (OrderID COUNTER constraint PrimaryKey PRIMARY KEY, CustomerID LONG,  InvoiceNo Text(16))"

Relate tblCustomer.CustomerID  to tblOrder.CustomerID in a 1 to many relationshiop Respecively.
     ALTER TABLE tblOrder ADD CONSTRAINT CustToOrder FOREIGN KEY (CustomerID) REFERENCES tblCustomer (CustomerID)"

How to delete an index inside a table..
     ALTER TABLE tblOrder DROP CONSTRAINT CustToOrder"         'Deletes Relation CustToOrder

Trying to set the default value via  a SQL Query dould be a problem.  For example if you wanted tblCustomer.Active to default to yes one would normally run the following VBA Code like...
        CurrentDb.TableDefs.Fields("Active").DefaultValue = True

Q: "What happens when in relationship window I connect two tables and I leave the Enforce referential integrity unchecked"
A: "Not much.  Your queries will want to relate these tables in the QBE grid but there is nothing to insure that referential integrity is invofced and once you've set it up that way referential integrity will fall out of favor in time, (orphans records, bogus values and the like going into a field that shouls have more structure).



   
0
 
LVL 16

Expert Comment

by:Rick_Rickards
ID: 20751980
Queck Correction the VBA line of code listed above.  It's the 4th line from the bottom and should had read...
    CurrentDb.TableDefs("tblCustomer").Fields("ActieID").DefaultValue = True

Q: How to set the Validation rule property? While creating the table, I have tried:
A: Same way as you set the default values as seen above... Using VBA you would...

    CurrentDb.TableDefs("tblCustomer").Fields("ActieID").ValidationRule = "Not Is Null"
    CurrentDb.TableDefs("tblCustomer").Fields("ActieID").ValidationText = "A Value is Required"
0
 
LVL 18

Accepted Solution

by:
Priest04 earned 0 total points
ID: 20752126
Hello, Rick Rickards, thanks for replying. The query examples you have posted are not related to my question No1. All they do is create tables and relationships, they don't set Indexed property for the foreign key field to Yes (Duplicates OK). I have found a query that does

CREATE INDEX idxName ON TableName (FieldName)

>> Trying to set the default value via  a SQL Query dould be a problem.

I didn't ask how to set default value, but since you have mentioned it, here is how it is done with SQL:

CREATE TABLE TableName (fieldName YESNO DEFAULT Yes)

I have asked how to set Format property of The YesNo field to one of the 3 give options. If you add YesNo field in table designer in Access,  you can see that Format property is automatically set to Yes/No. If you add field via SQL, it is not. I am only interested in how to set Format and Validation Rule properties via SQL queries. I know how to do it via ADOX (I am not using VBA, but c# and using access database only as a back-end).

Thanks,
Goran
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 16

Expert Comment

by:Rick_Rickards
ID: 20755535
>> Qestion 1) how to set property indexed to: Yes, Duplicates OK (need it for the foreign keys).

Supose you had a table named tblCustomer with 2 fields in it, 1 named CustomerID (An AutoNumber) that is also a Primary Key with a 2nd field named CustomerName.  You could create this table with the following DDL Query...

    CREATE TABLE tblCustomer (CustomerID COUNTER CONSTRAINT PrimaryKey PRIMARY KEY, CustomerName TEXT(64))

Now, the question is how to set the Index property of one of these fields to Yes and Allow Duplicates.  So in this example we'll assume you want to Index the field named CustomerName but you want duplicates to be allowed.  You can do this with a DDL Query as follows.

     CREATE INDEX CustomerName ON tblCustomer (CustomerName DESC)

I Think that answeres the question but in the event that you wanted something more elaborate you could have just as easily made the field a UNIQUE index merely by using the word UNIQUE in the above query as follows...

     CREATE UNIQUE INDEX CustomerName ON tblCustomer (CustomerName DESC)

Now to take this one step futher you can also declare that the field is NOT to have any NULL VALUES by adding that clause as a suffix to either of the two above queries.  The clause is WITH DISALLOW NULL and would alter either of the two queries above to look like the ones below...

     CREATE INDEX CustomerName ON tblCustomer (CustomerName DESC) WITH DISALLOW NULL
     CREATE UNIQUE INDEX CustomerName ON tblCustomer (CustomerName DESC) WITH DISALLOW NULL

Now In the event that you wanted to create an index to be used in a relationship (Foregin Key) you could add another table such as tblOrder to illustrate this.  To create the Child table (tblOrder) you could use the following DDL Query....

     CREATE TABLE tblOrder (OrderID COUNTER constraint PrimaryKey PRIMARY KEY, CustomerID LONG,  InvoiceNo Text(16))

Now to Relate tblCustomer.CustomerID to tblOrder.CustomerID in a 1 to Many relationship you would need only to execute the following DDL Query.

ALTER TABLE tblOrder ADD CONSTRAINT CustToOrder FOREIGN KEY (CustomerID) REFERENCES tblCustomer (CustomerID)

Note: You cannot specify that you want "Cascade Updates" or "Cascade Deletes" with a relationship created using DDL. These features are available only when using the Microsoft DAO (Data Access Objects) interfaces via code or when using the Microsoft Access user interface.  See Microsoft KB: 180841 http://support.microsoft.com/kb/180841

That said just let me know if any part of your question remains unaswered.

Rick


0
 
LVL 18

Author Comment

by:Priest04
ID: 20756113
:) Well, actually, I don't see any answers here, Rick, that are relevant to my questions. :) You again are talking about how to create a table, columns, constraints, etc, which was not being asked by me. Creating constraints fk->pk are not going to set property indexed to Yes (Duplicates ok), so above examples didn't answer my questions, too. This time you are giving examples about CREATE INDEX query, but as you have seen in my previous post, I already found out that myself, so I don't see why you are posting examples for it. CREATE UNIQUE INDEX is not allowing duplicate values for this field, so it cant be applied as solution.

And in the end, you say:

>> Note: You cannot specify that you want "Cascade Updates" or "Cascade Deletes" with a relationship created using DDL. These features are available only when using the Microsoft DAO (Data Access Objects) interfaces via code or when using the Microsoft Access user interface.  See Microsoft KB: 180841 http://support.microsoft.com/kb/180841

This information is outdated and is incorrect with newer versions of Access (I believe it is supported from Access 2003).  I am using it with no problem in my applications. Here is an example

CREATE TABLE Orders (OrderId INTEGER PRIMARY KEY, CustId INTEGER, CONSTRAINT FKOrdersCustId FOREIGN KEY (CustId) REFERENCES Customers ON UPDATE CASCADE ON DELETE CASCADE)

So, to summarize:

Q1) answered by me, in my 2nd post
Q2) not answered (in my 1st post I specifically asked for SQL DDL solution, I already knew how to do it with ADOX)
Q3) not answered (in my 1st post I specifically asked for SQL DDL solution, I already knew how to do it with ADOX)
Q4) Will give you credits for that one. :) Btw, orphans records, bogus values and the like, as you have put it, will not necessarily be the result of not having referential integrity enforced between two tables that can be related ( am programming for more than 7 years, and never had any orphaned records, and I do have situations where I don't enforce it). Example, a basic financial application that also prints bills on fiscal printers - you can't request from user to enter every customer in database that buys something. But user will enter customer if a firm is buying something, to print an invoice. My curiosity was only about what happens in the Access background when I connect 2 tables in relationship window, but don't enforce anything.

Goran
0
 
LVL 18

Author Comment

by:Priest04
ID: 20756187
Btw, considering the Q2, it is also answered to some point...

ALTER TABLE TableName ADD CONSTRAINT check_price CHECK (price>0)

does create a constraint for the price field value (must be greater than zero). It is not shown in field Validation Rule property, nor in table Validation Rule property, but is is actually validating the input in the field. Its stored somewhere, don't know where though.

Goran
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Get the source code for a fully functional Access application shell with several popular security features that Access VBA application developers desire, but find difficult or impossible to figure out how to code. You get the source code for managi…

599 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question