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
  • 10
  • 3
  • 2
  • +2
LVL 50

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
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

839 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