major restructure of backend file from code - can this be done?

[Tony Vrolyk]

I have an Access XP app that has been distributed to several hundred users
and I need to do a major table structure modification on the backend file.
For some time I have been doing some basic field creating procedures but now
I need to delete relationships, change keys and set new relationships.

I have read some posts that stated certain backend changes can't be done in
code. I have been starting to look at the help files and some previous posts
but I though first I better find out if what I want to do is possible.

Here are the basic steps I need to accomplish
1. Append data to a temporary table for archive (can do)
2. Delete existing data (can do)
3. Delete two relationships
4. Delete primary keys on 3 tables (not delete the fields, just remove the
PK setting)
5. Create new fields on two tables (can do)
6. Append data from temp table to modified table
7. Set Primary Keys on 3 tables (two of these are two-field primary keys)
8. Create relationship using new fields (with referential integrity and
cascading updates)
9. Delete the now unnecessary fields
10. Delete temp table (can do)

In order to clarify the steps for myself I wrote a short doc detailing the
steps using actual table and field names. Also I have a test backend mdb
that has everything stripped out except those tables requiring modification.
If you would like to look at those so you can understand my project in more
detail you can get them here
http://personalpages.tds.net/~tvrolyk/table_mod/.

The PDF also has an example of the code I have been using to create fields
in the backend tables. I would like to use the same basic method since I am
familiar with it already.

Could I first get some basic feedback on whether or not this is all possible
from code, from a front end file? Also, I might be interested in paying
someone write the code for me with payment contingent on it working.

Otherwise does anyone have a time machine so I could go back and slap some
sense into my head so I do it right the first time?

Thanks
Tony Vrolyk

 

[Brendan Reynolds]

It all sounds do-able to me, Tony. I'm working on something similar at the
moment. Here's part of my code with a few explanatory comments ...

'Have we updated the data file already?
Set db = CurrentDb
Set tdf = db.TableDefs("tblYear")
For Each fld In tdf.Fields

'This new field is part of the update, so if it exists, we've done
this already ...
If fld.name = "NEWBNumber" Then
boolDone = True
Exit For
End If
Next fld
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing

If boolDone Then Exit Sub

'These are links in the front end. They'll be re-created after we create
the
'tables in the back end. I added this code because I got fed up manually
'deleting the links every time I tested the code, or forgetting to do
so!
For Each tdf In CurrentDb.TableDefs
If tdf.name = "tblNEWBReason" Then
CurrentDb.TableDefs.Delete "tblNEWBReason"
Exit For
End If
Next tdf
For Each tdf In CurrentDb.TableDefs
If tdf.name = "tblReturns" Then
CurrentDb.TableDefs.Delete "tblReturns"
Exit For
End If
Next tdf

'Get the path to the back end from the Connect property of one of the
linked tables ...
strDataFile = CurrentDb.TableDefs("tblAbsenceReason").Connect
strDataFile = Mid$(strDataFile, InStr(1, strDataFile, "=") + 1)

'Open the backend exclusively - this will raise an error
'if any other uses are using the data, but you need excluisve
'access to make these changes ...
Set db = DBEngine.OpenDatabase(strDataFile, True)

 

[Tony Vrolyk]

That is a good start but it looks like this just addresses links so far. The
main things I am not sure how to do are the modifying of relationships and
perimary keys.

tony

 

[Brendan Reynolds]

A primary key ...

Set tdf = db.CreateTableDef("tblNEWBReason")
Set fld = tdf.CreateField("NEWBCode", dbText, 50)
tdf.Fields.Append fld
Set fld = tdf.CreateField("NEWBDescrip", dbText, 50)
tdf.Fields.Append fld
Set fld = Nothing
db.TableDefs.Append tdf
Set idx = tdf.CreateIndex("PrimaryKey")
idx.Primary = True
idx.Fields.Append idx.CreateField("NEWBCode")
tdf.Indexes.Append idx

I don't have any code in this project yet to create any relationships, but
here's a quick example using the Northwind sample database ...

Public Sub CreateRelationship()

Dim db As DAO.Database
Dim rel As DAO.Relation
Dim fld As DAO.Field

Set db = CurrentDb
Set rel = db.CreateRelation("MyNewRelation", "Customers", "Orders")
Set fld = rel.CreateField("CustomerID", dbLong)
fld.ForeignName = "CustomerID"
rel.Fields.Append fld
db.Relations.Append rel

End Sub

There's a more detailed example in the help file. Look for the topic
'CreateRelation Method'.

 

[Tony Vrolyk]

Thanks, that one got me going and I think I have it. With some more
reasearch here is what I have. It needs error handling of course. Watch for
wraps.

Thanks
Tony

Function RunCategoriesTablesUpdates()

Dim dbsUpdate As DAO.Database, wrkDefault As DAO.Workspace, tdfUpdate As
DAO.TableDef, tdfField As DAO.Field, idx As DAO.Index
Dim strBE As String, strTable As String
Dim rel As DAO.Relation
Dim inti As Integer
Dim db As DAO.Database, strSql As String

'Beging ArchiveData
Set db = CurrentDb()
'Achive Project_Categories Relationship
strSql = "SELECT Projects_Categories.ProjectID,
Projects_Categories.CategoryID INTO Temp_Projects_Categories FROM
Projects_Categories"
db.Execute strSql, dbFailOnError

'Delete Old Project_Categories Data
strSql = "DELETE Projects_Categories.* FROM Projects_Categories"
db.Execute strSql, dbFailOnError

'Delete Old Customers_Categories Data
strSql = "DELETE Customers_Categories.* FROM Customers_Categories"
db.Execute strSql, dbFailOnError

Debug.Print "ArchiveData Done"

'Beging work on linked db
strBE = fGetLinkPath("Customers")
Set wrkDefault = DBEngine.Workspaces(0)
Set dbsUpdate = wrkDefault.OpenDatabase(strBE, True)


strTable = "Categories"

For inti = dbsUpdate.Relations.Count - 1 To 0 Step -1
Set rel = dbsUpdate.Relations(inti)
If rel.Table = strTable Or rel.ForeignTable = strTable Then
dbsUpdate.Relations.Delete rel.Name
End If
Next inti
Set rel = Nothing

Debug.Print "DeleteRelations Done"

'Begin DeletePrimaryKeys
Set tdfUpdate = dbsUpdate.TableDefs("Categories")
tdfUpdate.Indexes.Delete ("PrimaryKey")
Set tdfUpdate = Nothing

Set tdfUpdate = dbsUpdate.TableDefs("Customers_Categories")
tdfUpdate.Indexes.Delete ("PrimaryKey")
Set tdfUpdate = Nothing

Set tdfUpdate = dbsUpdate.TableDefs("Projects_Categories")
tdfUpdate.Indexes.Delete ("PrimaryKey")
Set tdfUpdate = Nothing

Debug.Print "DeletePrimaryKeys Done"

'Begin CreateFields
Set tdfUpdate = dbsUpdate.TableDefs("Customers_Categories")
With tdfUpdate
Set tdfField = .CreateField("Category", dbText, 50)
.Fields.Append tdfField
End With
Set tdfField = Nothing

Set tdfUpdate = dbsUpdate.TableDefs("Projects_Categories")
With tdfUpdate
Set tdfField = .CreateField("Category", dbText, 50)
.Fields.Append tdfField
End With
Set tdfField = Nothing

Debug.Print "CreateFields Done"

'Begin RestoreArchivedData
strSql = "INSERT INTO Projects_Categories ( ProjectID, Category )SELECT
Temp_Projects_Categories.ProjectID, Categories.Category FROM
Temp_Projects_Categories INNER JOIN Categories ON
Temp_Projects_Categories.CategoryID = Categories.CategoryID"
db.Execute strSql, dbFailOnError
Set db = Nothing

DoCmd.DeleteObject acTable, "Temp_Projects_Categories"

Debug.Print "RestoreArchivedData Done"

'Begin CreatePrimaryKeys

'Set new Pk for Categories
Set tdfUpdate = dbsUpdate.TableDefs("Categories")

Set idx = tdfUpdate.CreateIndex("PrimaryKey")
idx.Primary = True
idx.Fields.Append idx.CreateField("Category")
tdfUpdate.Indexes.Append idx

Set tdfUpdate = Nothing
Set idx = Nothing

'Set new Pk for Customers_Categories
Set tdfUpdate = dbsUpdate.TableDefs("Customers_Categories")

Set idx = tdfUpdate.CreateIndex("PrimaryKey")
idx.Primary = True
idx.Fields.Append idx.CreateField("CustomerID")
idx.Fields.Append idx.CreateField("Category")
tdfUpdate.Indexes.Append idx

Set tdfUpdate = Nothing
Set idx = Nothing

'Set new Pk for Customers_Categories
Set tdfUpdate = dbsUpdate.TableDefs("Projects_Categories")

Set idx = tdfUpdate.CreateIndex("PrimaryKey")
idx.Primary = True
idx.Fields.Append idx.CreateField("ProjectID")
idx.Fields.Append idx.CreateField("Category")
tdfUpdate.Indexes.Append idx

Set idx = Nothing


Debug.Print "CreatePrimaryKeys Done"

'Begin CreateRelationShips

'Create Relationship between Categories and Customers_Categories
Set rel = dbsUpdate.CreateRelation("CustomersCategories", "Categories",
"Customers_Categories", dbRelationUpdateCascade)
rel.Fields.Append rel.CreateField("Category")
rel.Fields!Category.ForeignName = "Category"
dbsUpdate.Relations.Append rel
Set rel = Nothing

'Create Relationship between Categories and Projects_Categories
Set rel = dbsUpdate.CreateRelation("ProjectsCategories", "Categories",
"Projects_Categories", dbRelationUpdateCascade)
rel.Fields.Append rel.CreateField("Category")
rel.Fields!Category.ForeignName = "Category"
dbsUpdate.Relations.Append rel
Set rel = Nothing


Debug.Print "CreateRelationShips Done"


'Begin DeleteFields
Set tdfUpdate = dbsUpdate.TableDefs("Categories")
tdfUpdate.Indexes.Delete ("CategoryID")
tdfUpdate.Fields.Delete ("CategoryID")
Set tdfUpdate = Nothing

'Set tdfUpdate = dbsUpdate.TableDefs("Customers_Categories")
'tdfUpdate.Indexes.Delete ("CategoryID")
'tdfUpdate.Fields.Delete ("CategoryID")
'Set tdfUpdate = Nothing

Set tdfUpdate = dbsUpdate.TableDefs("Projects_Categories")
tdfUpdate.Indexes.Delete ("CategoryID")
tdfUpdate.Fields.Delete ("CategoryID")
Set tdfUpdate = Nothing

Debug.Print "DeleteFields Done"

Set dbsUpdate = Nothing
Set wrkDefault = Nothing

End Function

A primary key ...

Set tdf = db.CreateTableDef("tblNEWBReason")
Set fld = tdf.CreateField("NEWBCode", dbText, 50)

.....