Solved

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

Posted on 2007-03-19
11
211 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
  • 5
  • 5
11 Comments
 
LVL 4

Expert Comment

by:DoubleJ92
Comment Utility
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 84
Comment Utility
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
Comment Utility
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
 

Author Comment

by:Delphiwizard
Comment Utility
LSMConsulting
How would that statement look like (please keep in mind that I use a MS Access database).
0
 
LVL 84
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 84
Comment Utility
Sorry, that should be ADD COLUMN ..
0
 

Author Comment

by:Delphiwizard
Comment Utility
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 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
Comment Utility
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
Comment Utility
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 84
Comment Utility
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
Comment Utility
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now