Solved

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

Posted on 2007-03-19
11
218 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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 500 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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

688 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