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 49

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
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...
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline


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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

707 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

12 Experts available now in Live!

Get 1:1 Help Now