Solved

DAO and Delphi -> Update MS Access database structure

Posted on 2007-03-21
8
587 Views
Last Modified: 2010-04-04
Hi,

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.
ALTER TABLE FracturenUIT ADD COLUMN IsCorrect YesNo
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
0
Comment
Question by:Delphiwizard
8 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 18763524
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

0
 

Author Comment

by:Delphiwizard
ID: 18763569
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?
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 18763610
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
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 18763627
Delphiwizard,
sorry, thoght you want to do it in Access VBA
0
 
LVL 21

Expert Comment

by:ziolko
ID: 18763644
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

ziolko.
0
 
LVL 26

Expert Comment

by:EddieShipman
ID: 18772949
See this article on using ADOX to create your database/tables:
http://delphi.about.com/od/database/l/aa072401a.htm
0
 

Author Comment

by:Delphiwizard
ID: 18773207
ADOX doesn't support changing the field's Format-property.
Therefor I need to do the database updates with DAO.
0
 

Author Comment

by:Delphiwizard
ID: 18896933
Thank you all for your help.
I have bought the DAO componentsuite of Scalabium. This is giving me all the stuff I need.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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 …

808 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