Changing properties for an existing db field (mdb-file)

I have an mdb database that I would like to change from VB (using DAO). First I would like to add fields if they don't exist, if they exist I shall get a message about this and then go on. Next I would like to change properties for existing fields. In other words; I would like to know how to set (for a new field) properties and also how to check and change properties for an existing field.
Who is Participating?

Improve company productivity with a Business Account.Sign Up

inthedarkConnect With a Mentor Commented:
Did you get your job done?
Ryan ChongCommented:
First get access to the database

dim DB as DAO.Database
dim TD as DAO.TAbleDef
dim FLD as DAO.Field

Set DB = Workspaces(0).Opendatabase("C:\Yourdb.mdb")

' Get a table read for amendment

Set TD=DB.TableDefs("MyTable")

' Do we have a field "MyField" already

On error resume next

set FLD=TD.Fields("MyField")
if err.number = 0 Then
    msgbox "Field exists"
end if

' Now create a new field

set FLD=TD.CreateField("MyField",dbText,50)
fld.AllowNulls = true

' Now Save The field
TD.Fields.Add FLD

' Now save the table

DB.TableDefs.Append TD

' Your can create other types of fields
set FLD=TD.CreateField("MyDate",dbDate)
set FLD=TD.CreateField("MyCur",dbCurrency)

Have fun......

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

So I would test for a field in a function:

Private Sub Command2_Click()

Dim db As DAO.Database

Set db = Workspaces(0).OpenDatabase("D:\Test.mdb")

If FieldExists(db, "hotlist3", "MYIDs") Then
    MsgBox "Exists"
    MsgBox "Don't Exist"
End If

End Sub

Function FieldExists(db As DAO.Database, TableName As String, FieldName As String)

' Test for Field exists

Dim fld As DAO.Field

On Error Resume Next
Set fld = db.TableDefs(TableName).Fields(FieldName)
If Err.Number = 0 Then
    FieldExists = True
    FieldExists = False
End If
Set fld = Nothing

End Function
So now here is the cute bit:

Save all of you database changes in a file which you distribute with your application.

[Database Updates]

You can scane the db updates file and perofrm the necessary updates then save the last update in the client registry.

SaveSetting "MyApp Name","DB Updates","LastUpdate",cstr(LastUpdate)

In this way when you send chnages to your exe which required new fields the system all automatically add the new fields to the database.  The program can read the registry setting

LastUpdate=val(GetSetting("My App Name","DB Updates","Last Update"))

More hints:

To create a new table:

Set TD = DB.CreateTableDef("New Table Name")

' now you can add new fields

set FLD=Td.CreateField before

' You may want to add an index to a field.

dim ind as dao.index

Set ind = New Index
ind.Name = "ByCustomer"
ind.Fields.Add "MyField1"
ind.Fields.Add "MyField2"
ind.Unique = true ' change as required
ind.Primary = true ' ditto
ind.IgnoreNulls = true ' ditto
ind.Required = true ' ditto

' now save the new index
db.TableDefs("MyTable").Indexes.Append ind
soft4uAuthor Commented:
How can I change write protected properties like Size, Name etc. and how can I make a field indexed and then set it to "duplicates allowed" or not allowed?
That issue is another one of Micrsoft's oversights, however they have produced a work arround:;EN-US;q249682

But Microsoft's solution won't work on a Novel server so you may need a better solution.  I solve this problem with less code but may take longer for huge tables but works on all servers:

' copy the database first cos you don't want to be resposible for lossing the whole lot
FileCopy "D:\YourData.mdb d:\temp.mdb"

' Open the copy of the data
set db=workspacses(0).opendatabase("d:\temp.mdb")

' copy data to temp table
sql = "Select * into [temp] from [yourtable];"
db.Execute SQL

' remove the duff table
db.tableDefs.delete ("YourTable")

' Now create the new table as you want it
(As shown previously)

' copy data back:
sql = "Select * into [yourtable] from [temp];"
db.Execute SQL

' Sort out databbases
on error resume next
kill "d:\YourData.Bak"
on error goto 0
Name "d:\Yourdata.mdb" as "d:\YourData.Bak" ' create backup
Name "d:\temp.mdb" as "YourData.mdb" ' move temp to live

And its a whole lot less code too...
MSDN library, read up on ADOX, that's what I used to dereplicate databases (where I have to read the db structure and copy it w/o the replication fields) not enough time to show you code but MSDN has good examples on ADOX.
don't use DAO, it's archiac. =(

Maybe someone else can... just a guideline(that's why it's a comment!  =)
Ixeus, don't listen to Microsoft! DAO won't die yet as it is several 1000% faster than ADO.  But you are right for working with users and indexes ADOX is really simple. The only problem with ADOX is that Microsoft forgot to make it work with anything other than MS Access.
Actually, it's a lot faster, benchmark a Tabledef vs ADOX.Table retrieval over a loop, and then extract the properties too... Ofcourse if there are no indexes it maybe slower, but if there are, it will be faster.  In most cases businesses USE indexes and will need a better class to handle the databases (also much easier to maintain the code)

And no, it works with SQL server too.
not just access.  I did a table extraction on SQLserver with fields and props


lxeus I share your enthusiasm for ADOX and ADO.  The nice thing about ADOX is it has very few querks and is easy to use.  Repetative table schema changes is something my apps. don't do so speed is not an issue.  The only areas whare ADO is not as fast as DAO are:

* Inserting records into a locally held MDB.

* Moving to a specific current record within a recordst.

When Microsoft implement the seek command for MDB's, within ADO, I will be happy.

that's why I write my own class for seek, it's not that hard.

But anyway, I got stuff to do, I'm not here to argue, to be honest, I use DAO when I can't remember off hand how to do ADO and ADO when I don't remember DAO.  All depends on the client's needs.  And my mood =P

DAO is good for what it's needed for as is any other class, it's about personal preference, and in my opinion until ADOX's irritating issues like seek are dealt with I'm not going to stop using DAO when I want to (which isn't much but I do use it :p)

Here is an adox example of tables create but as with DAO once a field (column in ADOX) has been created some of the items are hard to change.

' make project references to:
' Microsoft ActiveX Data Objects 2.x
' Microsoft ADO Ext DLL & Security

Dim CN As ADODB.Connection
Dim AccessMDBFile As String

AccessMDBFile = "D:\test.mdb"
Set CN = New ADODB.Connection

CN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + AccessMDBFile + ";"

' here are the main adox table/field objects
Dim aCat As ADOX.Catalog
Dim aTable As ADOX.Table
Dim aKey As ADOX.Key
Dim aIndex As ADOX.Index
Dim aCol As ADOX.Column ' a field object

' first get access to the Catalog
Set aCat = New ADOX.Catalog
Set aCat.ActiveConnection = CN

' now you can work with a table

Set aTable = New ADOX.Table

aTable.Name = "New Table3"
aCat.Tables.Append aTable     ' save the new table

Set aTable = aCat.Tables("New Table3")

Set aCol = New ADOX.Column

aCol.Name = "Field1"
aCol.DefinedSize = 20
aCol.Type = adVarWChar ' VB autosuggests here which helps

aTable.Columns.Append aCol  ' save the new field
Set aCol = Nothing

' create another column
Set aCol = New ADOX.Column
aCol.Name = "Field2"
aCol.DefinedSize = 10
aCol.Type = adVarWChar ' VB autosuggests here which helps

aTable.Columns.Append aCol  ' save the new field
Set aCol = Nothing

' Create a key
Set aKey = New ADOX.Key
aKey.Name = "Primary"
aKey.Type = adKeyPrimary ' you can only have one primary key
aKey.Columns.Append "Field1"
aKey.Columns.Append "Field2"

aTable.Keys.Append aKey ' save the key

Set aIndex = New ADOX.Index
aIndex.Name = "ByField2"

aIndex.Clustered = False
aIndex.Columns.Append "Field2"
aIndex.Columns.Append "Field1"

aTable.Indexes.Append aIndex ' save the index

Set aTable = Nothing
Set aCat = Nothing

Set CN = Nothing

soft4uAuthor Commented:
Sorry for the dlay inthedark, I've been on vacation for a while but now I'm back. Thanks för the solution.
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.