Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

DAO and Delphi -> Update MS Access database structure

Posted on 2007-03-21
8
Medium Priority
?
618 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:Stef Merlijn
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:Stef Merlijn
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 1500 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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:Stef Merlijn
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:Stef Merlijn
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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

971 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