defining keys and relationships in VBA

  • Thread starter Thread starter Robb
  • Start date Start date
R

Robb

I have a procedure that imports an Excel spreadsheet as a
new table into my Access database. Once I get the table
imported, I manually have to set the primary key (which
consists of three fields) and define the one-to-one
relationship with cascade update between the new table and
the db table.

Once that is done, I have a procedure that runs an update
query to update the db table with the imported data (which
comes from an oracle database) and another query that asks
the user to give a user-defined date and then updates a
field in a subset of the db table with that date. Then the
procedure deletes the imported table since it is no longer
needed.

I would like to automate the whole process. Is there a way
to define a primary key (that consists of three fields) in
a table and then create relationships and enforce ref
integrity in VBA, or will this always be a manual
operation? Thanks for any help!

Robb
 
Assuming you've got a reference set to DAO, a couple of changes to the
following from the Help file should do the first step (creating the Primary
Key):

Sub NewIndex()
Dim dbs As Database, tdf As TableDef
Dim idx As Index
Dim fldLastName As Field, fldFirstName As Field

' Return reference to current database.
Set dbs = CurrentDb
' Return reference to Employees table.
Set tdf = dbs.TableDefs!Employees
' Return reference to new index.
Set idx = tdf.CreateIndex("FullName")
' Create and append index fields.
Set fldLastName = idx.CreateField("LastName", dbText)
Set fldFirstName = idx.CreateField("FirstName", dbText)

idx.Fields.Append fldLastName
idx.Fields.Append fldFirstName
' Append Index object and refresh collection.
tdf.Indexes.Append idx
tdf.Indexes.Refresh
Set dbs = Nothing
End Sub

The changes are name the index PrimaryKey, rather than FullName (actually,
this isn't actually mandatory) and add the line

idx.Primary = True

before you append idx to the tdf object.

To create a relationship in code, this example (also from the Help file)
should do it:

Sub NewRelation()
Dim dbs As Database, rel As Relation, fld As Field

' Return reference to current database.
Set dbs = CurrentDb
' Create new Relation object and specify foreign table.
Set rel = dbs.CreateRelation("CategoryProducts", "Categories", "Products")
' Set attributes to enforce referential integrity.
rel.Attributes = dbRelationUpdateCascade And dbRelationDeleteCascade
' Create field in Relation object.
Set fld = rel.CreateField("CategoryID")

' Specify field name in foreign table.
fld.ForeignName = "CategoryID"
' Append Field object to Fields collection of Relation object.
rel.Fields.Append fld
' Append Relation object to Relations collection.
dbs.Relations.Append rel
dbs.Relations.Refresh
Set dbs = Nothing
End Sub

In the second example, if you've also got a reference set to ADO, you should
change the declaration from fld As Field to fld As DAO.Field, since both the
DAO and ADO models have a Field object in them, and you want to ensure that
you get the correct one.
 
It's close on the first step. I keep getting a type
mismatch error. Here's the code:

Function UpdateData()

Dim dbs As Database, tdf As TableDef
Dim idx As Index
Dim fldWONUM As Field, fldWoLine As Field, fldOperation As
Field

DoCmd.TransferSpreadsheet
acImport, , "UpdateData", "C:\Documents and
Settings\brunanskyr\My Documents\UpdateData.xls", True
DoCmd.OpenTable "UpdateData", acViewDesign, acEdit

Set dbs = CurrentDb
Set tdf = dbs.TableDefs!UpdateData
Set idx = tdf.CreateIndex("PrimaryKey")
Set fldWONUM = idx.CreateField("WO NUM", dbText) <<<<THE
ERROR OCCURS AT THIS STEP>>>>
Set fldWoLine = idx.CreateField("Wo Line", dbText)
Set fldOperation = idx.CreateField("Operation", dbText)

idx.Fields.Append fldWONUM
idx.Fields.Append fldWoLine
idx.Fields.Append fldOperation

idx.Primary = True

tdf.Indexes.Append idx
tdf.Indexes.Refresh
Set dbs = Nothing

End Function


Any ideas? Thanks
-----Original Message-----
Assuming you've got a reference set to DAO, a couple of changes to the
following from the Help file should do the first step (creating the Primary
Key):

Sub NewIndex()
Dim dbs As Database, tdf As TableDef
Dim idx As Index
Dim fldLastName As Field, fldFirstName As Field

' Return reference to current database.
Set dbs = CurrentDb
' Return reference to Employees table.
Set tdf = dbs.TableDefs!Employees
' Return reference to new index.
Set idx = tdf.CreateIndex("FullName")
' Create and append index fields.
Set fldLastName = idx.CreateField("LastName", dbText)
Set fldFirstName = idx.CreateField("FirstName", dbText)

idx.Fields.Append fldLastName
idx.Fields.Append fldFirstName
' Append Index object and refresh collection.
tdf.Indexes.Append idx
tdf.Indexes.Refresh
Set dbs = Nothing
End Sub

The changes are name the index PrimaryKey, rather than FullName (actually,
this isn't actually mandatory) and add the line

idx.Primary = True

before you append idx to the tdf object.

To create a relationship in code, this example (also from the Help file)
should do it:

Sub NewRelation()
Dim dbs As Database, rel As Relation, fld As Field

' Return reference to current database.
Set dbs = CurrentDb
' Create new Relation object and specify foreign table.
Set rel = dbs.CreateRelation
Click to expand...
("CategoryProducts", "Categories", "Products")
 
Sorry: I missed the fact that there were Field objects in the first sample
as well.

As I mentioned, if you have references set to both ADO and DAO, you need to
use As DAO.Field to ensure you get a DAO field, not an ADO field.

The list of objects with the same names in the 2 models is Connection,
Error, Errors, Field, Fields, Parameter, Parameters, Property, Properties
and Recordset
 
Robb,

Do not create a new table each time.

Create the table, define the Primary Key and the relationships etc.

Then import the data from Excel.

Use a Delete query to remove the contents of the table when you are done.

The table remains ready for the next import.
 
Back
Top