Solved

DAO and Delphi -> Update MS Access database structure

Posted on 2007-03-21
8
568 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 119

Expert Comment

by:Rey Obrero
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 18763627
Delphiwizard,
sorry, thoght you want to do it in Access VBA
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
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…
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…
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…

743 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

10 Experts available now in Live!

Get 1:1 Help Now