Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to set property 'Notation' of an MS Access field from within Delphi?

Posted on 2007-03-19
11
Medium Priority
?
225 Views
Last Modified: 2013-11-23
Hi,

To update the MS Access database of my application I use following statement.
      QSQLDB.SQL.Clear;
      QSQLDB.SQL.Text:='ALTER TABLE FacturenUIT ADD COLUMN IsCorrect   Bit   DEFAULT True';
      QSQLDB.ExecSQL;
This will add field IsCorrect to the database table.
I also can set the default value for the field by adding 'Default True' as you can see above.

For boolean values I can set the notation (F6) in MS Access (like: 'Yes/No', 'True/False' or 'On/Off')
How can I set the notation from within Delphi?

Regards,
Stef
0
Comment
Question by:Delphiwizard
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
11 Comments
 
LVL 4

Expert Comment

by:DoubleJ92
ID: 18747759
I don't know about Delphi, but in SQL Server, no matter what notation I use in access, the SQL Bit data type only stores 1 or 0. If I wanted to store "On/Off", "True/False", "Yes/No" I would have to create a varchar field to hold that value. Don't know if this helps you any, but I thought I would give it a try.
0
 
LVL 85
ID: 18748138
If you're asking how to set the Datatype for a boolean field, use "Yes/No" with your ALTER TABLE statement ...
0
 

Author Comment

by:Delphiwizard
ID: 18748148
Basically I don't mind how to update the datafield, as long as it get's updated.
So it doesn't have to be done with a SQL statement.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:Delphiwizard
ID: 18748156
LSMConsulting
How would that statement look like (please keep in mind that I use a MS Access database).
0
 
LVL 85
ID: 18748526
Try this:

ALTER TABLE FracturenUIT ADD IsCorrect YesNo DEFAULT True

You can also use LOGICAL or BIT for the Boolean Datatype.

Here's a link to Jet DDL language: http://msdn2.microsoft.com/en-us/library/aa140015(office.10).aspx
0
 
LVL 85
ID: 18748529
Sorry, that should be ADD COLUMN ..
0
 

Author Comment

by:Delphiwizard
ID: 18749670
Hi, first of all a small correction.
In the Dutch version of MS Access there is a fieldproperty called 'Notatie'.  This propertyname I translated into 'Notation', but it appears to be called 'Format' in the english version of MS Access.

When you open a table in MS Access (in designview) and add a boolean field, it will have as it's property:
Fieldname = 'Test1'
Fieldtype = 'Yes/No'
Format = 'Yes/No'      (= automatically filled)
Format can be changed into Yes/No, True/False and On/Off

The statement below is not adding the Format-property of the boolean field.
ALTER TABLE FracturenUIT ADD COLUMN IsCorrect YesNo
Fieldname = 'Test1'
Fieldtype = 'Yes/No'
Format = <empty>

So: How can I access the fieldproperty Format?
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1500 total points
ID: 18750238
The Format property is a property of the Field and can't be set with straight SQL. Matter of fact, if you try to set this property and is doesn't exist already, then you'll receive an error.

I'm not familiar with Delphi, but in VB we'd do this after creating the field:

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set dbs = DAO.OpenDatabase("Full path to db")
Set tdf = dbs.TableDefs("NameOfYourTable")
Set fld = tdf.Fields("NameOfYourField")

fld.Fomat = "xxxx"

Of course, the last line may fail if the Format property doesn't exist. You can handle this various ways, but the easiest may be to use the two function below, which will create the property if it doesn't already exist:

SetPropertyDAO fld, "Format", dbText, "Currency", strErrMsg

So you'd change the code sample above to this:

Set dbs = DAO.OpenDatabase("Full path to db")
Set tdf = dbs.TableDefs("NameOfYourTable")
Set fld = tdf.Fields("NameOfYourField")

SetPropertyDAO fld, "Format", dbText, "Currency", strErrMsg

Of course, if you're going to do this, it's just as easy to add the tables and fields with DAO as it is with SQL ... if you'd like to do that, take a look at this website:

http://www.scalabium.com/faq/dct0111.htm

(and it's even Delphi-specific ;)


'///////////// CODE BELOW

'/these two function courtesy of Allen Browne www.allenbrowne.com
Function SetPropertyDAO(obj As Object, strPropertyName As String, _
intType As Integer, varValue As Variant, Optional strErrMsg As String) As
Boolean
On Error GoTo ErrHandler
'Purpose: Set a property for an object, creating if necessary.
'Arguments: obj = the object whose property should be set.
' strPropertyName = the name of the property to set.
' intType = the type of property (needed for creating)
' varValue = the value to set this property to.
' strErrMsg = string to append any error message to.

If HasProperty(obj, strPropertyName) Then
obj.Properties(strPropertyName) = varValue
Else
obj.Properties.Append obj.CreateProperty(strPropertyName, intType,
varValue)
End If
SetPropertyDAO = True

ExitHandler:
Exit Function

ErrHandler:
strErrMsg = strErrMsg & obj.Name & "." & strPropertyName & " not set to
" & varValue & ". Error " & Err.Number & " - " & Err.Description & vbCrLf
Resume ExitHandler
End Function

Public Function HasProperty(obj As Object, strPropName As String) As Boolean
'Purpose: Return true if the object has the property.
Dim varDummy As Variant

On Error Resume Next
varDummy = obj.Properties(strPropName)
HasProperty = (Err.Number = 0)
End Function

0
 

Author Comment

by:Delphiwizard
ID: 18761488
If I would import the type library "Microsoft DAO 3.6 Object Library (version 5.0)"
Could I do it somehow with that? And how would the code for Delphi look like?
0
 
LVL 85
ID: 18763052
Yes, you would somehow need to make use of the DAO library. As I mentioned earlier, I'm not at all familiar with Delphi so I can't advise you of how to do that, nor could I advise you of how to make the necessary code changes, but the code at the link I provided shows how to create a table with Delphi; it appears from that code that once you actually get the database open, you would use code as described in the lower section of my last comment to add your field and/or table.
0
 

Author Comment

by:Delphiwizard
ID: 18763410
Thank you LSMConsulting.
The site that you mentioned http://www.scalabium.com/faq/dct0111.htm describes the way it would work with their DAO-componentset, which differs from native DAO from the type library.

I will look further for DAO <-> Delphi information.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

610 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