DAO and Delphi -> Update MS Access database structure


When I 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.
Fieldname = 'Test1'
Fieldtype = 'Yes/No'
Format = <empty>

I seems that DAO has access to the format-property. I already imported the DAO 3.6 Objects library into my project, but I don't know how to use DAO to:
1. Open MS Access database that is password protected
2. Add or alter a table
3. Alter fieldname properties (especially property: FORMAT)

Bye the way: I just need DAO to make changes to the database structure (including the fieldproperties). (For accessing my data I use ADO).

Please give me some samples on different fieldtypes (f.e. Boolean, Text, Float, Memo)

Thanks in advance for your help,
Regards, Stef
Stef MerlijnDeveloperAsked:
Who is Participating?
rockiroadsConnect With a Mentor Commented:
As far as I know, u cant edit the format properties via sql
It can be done using DAO/VBA but I do not know how to do this in Delphi

sample code to add tables using DAO can be found here http://www.scalabium.com/faq/dct0111.htm

to open password protected databases, u could possibly try passing in a password here (see code from link provided)

db := access.OpenDatabase(yourDatabaseName);

one of the arguments might have something which allows passing of passwords.

As I dont have Delphi, I cant confirm this but you can play around
Rey Obrero (Capricorn1)Commented:
run this function to create a table with data types

Function CreateTableDDL()
Dim sql As String
sql = "CREATE TABLE TestAllTypes "
sql = sql & "( "
sql = sql & "      MyText       TEXT(50), "
sql = sql & "      MyMemo       MEMO, "
sql = sql & "      MyByte       BYTE, "
sql = sql & "      MyInteger    INTEGER, "
sql = sql & "      MyLong       LONG, "
sql = sql & "      MyAutoNumber COUNTER, "
sql = sql & "      MySingle     SINGLE, "
sql = sql & "      MyDouble     DOUBLE, "
sql = sql & "      MyCurrency   CURRENCY, "
sql = sql & "      MyReplicaID  GUID, "
sql = sql & "      MyDateTime   DATETIME, "
sql = sql & "      MyYesNo      YESNO, "
sql = sql & "      MyOleObject  LONGBINARY, "
sql = sql & "      MyBinary     BINARY(50) "
sql = sql & ")"
CurrentDb.Execute sql
End Function

Stef MerlijnDeveloperAuthor Commented:
Thank you Capricorn1:
I looks as if your code is Visual Basic and I'm using Delphi.
Also I don't see anything about the Format-property?
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Rey Obrero (Capricorn1)Commented:
sorry, thoght you want to do it in Access VBA
i think that it doesn't matter if you choose yes/no, true/false, on/off access stores value in the same way
you just choose how access application will interpret value of boolean field

Eddie ShipmanAll-around developerCommented:
See this article on using ADOX to create your database/tables:
Stef MerlijnDeveloperAuthor Commented:
ADOX doesn't support changing the field's Format-property.
Therefor I need to do the database updates with DAO.
Stef MerlijnDeveloperAuthor Commented:
Thank you all for your help.
I have bought the DAO componentsuite of Scalabium. This is giving me all the stuff I need.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.