Solved

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

Posted on 2002-05-21
17
307 Views
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.
0
Comment
Question by:soft4u
[X]
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
17 Comments
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 7023456
0
 
LVL 17

Expert Comment

by:inthedark
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
err.clear

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)
etc.

Have fun......

0
 
LVL 17

Expert Comment

by:inthedark
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"
Else
    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
Else
    FieldExists = False
End If
Set fld = Nothing


End Function
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 17

Expert Comment

by:inthedark
ID: 7023489
So now here is the cute bit:

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

e.g.
[Database Updates]
U1=Tablename1,FieldName1,FieldType,Attributes
U2=Customers,WebAddress,Text,/L:128

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"))

0
 
LVL 17

Expert Comment

by:inthedark
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 etc....as 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
0
 

Author Comment

by:soft4u
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?
0
 
LVL 17

Expert Comment

by:inthedark
ID: 7023683
That issue is another one of Micrsoft's oversights, however they have produced a work arround:

http://support.microsoft.com/default.aspx?scid=kb;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

0
 
LVL 17

Expert Comment

by:inthedark
ID: 7023689
And its a whole lot less code too...
0
 
LVL 2

Expert Comment

by:Ixeus
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!  =)
0
 
LVL 6

Expert Comment

by:pierrecampe
ID: 7025788
ping
0
 
LVL 17

Expert Comment

by:inthedark
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.
0
 
LVL 2

Expert Comment

by:Ixeus
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

*shrug*

-Ixeus
0
 
LVL 17

Expert Comment

by:inthedark
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.

0
 
LVL 2

Expert Comment

by:Ixeus
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)

-Ixeus
0
 
LVL 17

Expert Comment

by:inthedark
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 + ";"
CN.Open

' 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

CN.Close
Set CN = Nothing

0
 
LVL 17

Accepted Solution

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

Author Comment

by:soft4u
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.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
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.
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…
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…
Suggested Courses
Course of the Month7 days, 2 hours left to enroll

623 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