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

Posted on 2002-05-21
Last Modified: 2010-05-02
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.
Question by:soft4u
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 3
  • 2
  • +2
LVL 52

Expert Comment

by:Ryan Chong
ID: 7023456
LVL 17

Expert Comment

ID: 7023457
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......

LVL 17

Expert Comment

ID: 7023470
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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

LVL 17

Expert Comment

ID: 7023489
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"))

LVL 17

Expert Comment

ID: 7023516
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

Author Comment

ID: 7023584
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?
LVL 17

Expert Comment

ID: 7023683
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

LVL 17

Expert Comment

ID: 7023689
And its a whole lot less code too...

Expert Comment

ID: 7024119
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!  =)

Expert Comment

ID: 7025788
LVL 17

Expert Comment

ID: 7025823
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.

Expert Comment

ID: 7026346
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


LVL 17

Expert Comment

ID: 7026378
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.


Expert Comment

ID: 7026412
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)

LVL 17

Expert Comment

ID: 7026503
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

LVL 17

Accepted Solution

inthedark earned 200 total points
ID: 7055434
Did you get your job done?

Author Comment

ID: 7056234
Sorry for the dlay inthedark, I've been on vacation for a while but now I'm back. Thanks för the solution.

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

734 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