VBA to create/delete table relationships

[LFNFan]

Hi

I'm a newbie to Access, but have a respectable amount of experience in Excel
VBA.

I have set up a 'DeleteObject' macro in Access to delete the main data table
as a precursor to importing the most up-to-date version of the same data into
a table with the same name as the deleted table via TransferSpreadsheet
macro. This was all hunky dory until I put in another 2 tables, joined
one-to-many to the main table. Now, when I try to run the DeleteObject query
I get the error message: 'You can't delete the table 'IssuesImport'; it is
participating in one or more relationships. If you want to delete this
table, first delete its relationships in the Relationships window'

Which is fine, but I don't want to have to delete and reinstate these joins
every time I import fresh data. I have looked in the 'Action' options within
Macros, and there doesn't seem to be a suitable 'Relationships' macro. So
I'm thinking VBA, but I can't get a handle on the Access object model, or
info from VBA Help re. the correct method and its arguments. I am thinking
this should be straight-forward, but...

As a 'starter for ten', I identified the Alter Table statement, and the
acCmdCreateRelationship and acCmdDiagramDeleteRelationship, but frustratingly
haven't the foggiest how to make these work!

Any help would be deeply appreciated (you all seem a friendly (and
knowledgeable)lot from looking at the activity in the Community here).

Thanks in advance

Paul

 

[Allen Browne]

Here's an example of how to create and delete a relationship in VBA code:

Sub CreateRelationDAO()
Dim db As DAO.Database
Dim rel As DAO.Relation
Dim fld As DAO.Field

'Initialize
Set db = CurrentDb()

'Create a new relation.
Set rel = db.CreateRelation("tblDaoContractortblDaoBooking")

'Define its properties.
With rel
'Specify the primary table.
.Table = "tblDaoContractor"
'Specify the related table.
.ForeignTable = "tblDaoBooking"
'Specify attributes for cascading updates and deletes.
.Attributes = dbRelationUpdateCascade + dbRelationDeleteCascade

'Add the fields to the relation.
'Field name in primary table.
Set fld = .CreateField("ContractorID")
'Field name in related table.
fld.ForeignName = "ContractorID"
'Append the field.
.Fields.Append fld

'Repeat for other fields if a multi-field relation.

End With

'Save the newly defined relation to the Relations collection.
db.Relations.Append rel

'Clean up
Set fld = Nothing
Set rel = Nothing
Set db = Nothing
Debug.Print "Relation created."
End Sub

Sub DeleteRelationDAO()
DBEngine(0)(0).Relations.Delete "tblDaoContractortblDaoBooking"
End Sub

 

[Klatuu]

There is a much better approach.
The problems with your current approach:
1. You loose your relationships
2. Using TransferSpreadsheet, Access will make it's own decisions of data
types and the field lengths for text fields will all be set at default length
(probably 255)
3. Access will see empty Excel cells that it expects to be numbers as Null
4. Data you expect to be numbers may end up as text and vise versa

A better approach is:
Link the spreadsheet rather than inport it
Delete all the rows in the table
Use an append query to copy the data from the spreadsheet into the table
Delete the link to the spreadsheet.

With either method, there are some problems:
How do the child tables maintain their references to the parent table?
If you have cascading deletes and you delete the data in the parent table,
all the child data will also be deleted.
To avoid the Null problem, you will need to wrap all your numeric fields in
the Nz function to avoid getting bad data in.

Also, to help you understand the Access Object Model, use the Object Browser
in VBA Editor. It may be confusing at first, but if you read it and follow
along, you will be able to figure it out

 

[LFNFan]

Hi Allen

Firstly, big thanks for your response! I have copied your code into a
module, and when I try to step thru it, get a 'User-defined type not defined'
error on the Dim db As DAO.Database line. I don't think I've done anything
obviously wrong (but you never know, I may have done...)

I'm on Access 2000 SP-3 if that helps.

 

[Klatuu]

You probably don't have a reference set to the DAO object library. In the
VBA Editor, Tools->References, and lookf for Microsoft DAO ?.? Object
Library. The ?.? is a number depending on which version you are using. For
2003, it is 3.6

 

[LFNFan]

Beaut! Now works a treat.

Klatuu, I like your idea of linking, not TransferSpreadsheet-ing. I will
see if I can get the VB to work, then I am going to see if I can get your
approach to work as well, partly because one of the fields I want to import
does have more than 255 characters.

Re. the problems you mentioned in your other post, 'how do the child tables
maintain their references to the parent table?' - I don't _think_ I will run
into this problem, as I'm mainly just querying and manipulating data, doing
some calculations and generating reports, not updating info in Access (the
source of record is a Lotus Notes database, which is particularly unfriendly
and untransparent, hence Access).

Can you - or anyone - recommend a good book for learning VB for Access?

Cheers

Paul

 

[Klatuu]

Personally, I use the June 1997 issue of Playboy for reference material
No, I really don't know of a good book for learning VBA. I am sure there is
something out there. I would cruise some bookstores with good technical
sections and see what makes sense to me. I never buy a tech manual of any
kind until I have had a change to skim through it to see how it works for me.

 

[LFNFan]

We now have the dubious distinction of being the only post in Access Database
that is returned when you search on 'Playboy'! Still, I am sure some good
will come of it.

 

[LFNFan]

Object Library reference now set!

Thanks in advance for your perseverence with this.... I am now getting a
Run-time error 3011 - "The Microsoft Jet database engine could not find the
object 'tblDaoSeverityIndicator'. Make sure the object exists and that you
spell its name and the path correctly." when I step to the .Fields.Append fld
line.

The table definitely exists, and the table and field names are accurately
typed into the code. Any ideas?

 

[Klatuu]

I think your syntax is the problem. Here is an example:

Set rstFees = New ADODB.Recordset
rstFees.Fields.Append "Stat", adVarChar, 10
rstFees.Fields.Append "Deadline", adVarChar, 2
rstFees.Fields.Append "Days", adVarChar, 20
rstFees.Fields.Append "Fee", adCurrency

 

[Tim Ferguson]

I have set up a 'DeleteObject' macro in Access to delete the main data
table as a precursor to importing the most up-to-date version of the
same data

Another alternative is just to empty the table:

db.Execute "DELETE FROM MyTable", dbFailOnError


then you don't have to mess about with relationships, creating objects and
so on. Probably less database bloat too, although I don't have objective
evidence of that.

Just a thought.


Tim F

 

[Klatuu]

That woud be one step in the process, and the correct step for removing the
old data prior to bringing in the new. My concern is that even with cascade
deletes turned off, when he imports the new data, how are the relationships
going to be maintained correctly? Can he be sure that the foreign keys in
the new data will be the same as they were in the old?

 

[LFNFan]

Hi Allen

Thought I had this licked, but I am now getting a Run-time error 3011 - "The
Microsoft Jet database engine could not find the object
'tblDaoSeverityIndicator'. Make sure the object exists and that you spell
its name and the path correctly." when I step to the .Fields.Append fld line.

The table definitely exists, and the table and field names are accurately
typed into the code.

I tried removing 'tblDao' from in front of the table name when specifying
the primary and related tables, then got a different error message at the
same point in the query: "Run-time error 3609 - no unique index found for the
referenced field of the primary table".

Klatuu suggested a syntax problem, but the code you supplied seems to stack
up with everything else I have found on my googlings. Any ideas?

 

[Tim Ferguson]

My concern is that even with cascade
deletes turned off, when he imports the new data, how are the
relationships going to be maintained correctly? Can he be sure that
the foreign keys in the new data will be the same as they were in the
old?

Perennial problem innit?

My instinctive answer is always to clean the data before importing -- this
could by upgrading the validation in the spreadsheet, running exception
reports looking for unmatched values, making a temp (unrelated) table and
doing "something" with illegal records then, etc etc. There is no single
always-appropriate technique.

It seems to me, though, that removing integrity constraints on the real
database is the worst option... but of course other people's MMV.

All the best


Tim F

 

[Klatuu]

I agree, this looks like a bad idea based on a weak design. Personally, I
would use VBA code to evaluate the data coming in and take the appropriate
action.

 

[Allen Browne]

Here's the code that creates the tables, so you can then test creating the
relation. Hopefully the example will help you pin-point what is wrong with
your case.

Sub CreateTableDAO()
'Purpose: Create two tables using DAO.
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

'Initialize the Contractor table.
Set db = CurrentDb()
Set tdf = db.CreateTableDef("tblDaoContractor")

'Specify the fields.
With tdf
'AutoNumber: Long with the attribute set.
Set fld = .CreateField("ContractorID", dbLong)
fld.Attributes = dbAutoIncrField + dbFixedField
.Fields.Append fld

'Text field: maximum 30 characters, and required.
Set fld = .CreateField("Surname", dbText, 30)
fld.Required = True
.Fields.Append fld

'Text field: maximum 20 characters.
.Fields.Append .CreateField("FirstName", dbText, 20)

'Yes/No field.
.Fields.Append .CreateField("Inactive", dbBoolean)

'Currency field.
.Fields.Append .CreateField("HourlyFee", dbCurrency)

'Number field.
.Fields.Append .CreateField("PenaltyRate", dbDouble)

'Date/Time field with validation rule.
Set fld = .CreateField("BirthDate", dbDate)
fld.ValidationRule = "Is Null Or <=Date()"
fld.ValidationText = "Birth date cannot be future."
.Fields.Append fld

'Memo field.
.Fields.Append .CreateField("Notes", dbMemo)

'Hyperlink field: memo with the attribute set.
Set fld = .CreateField("Web", dbMemo)
fld.Attributes = dbHyperlinkField + dbVariableField
.Fields.Append fld
End With

'Save the Contractor table.
db.TableDefs.Append tdf
Set fld = Nothing
Set tdf = Nothing
Debug.Print "tblDaoContractor created."

'Initialize the Booking table
Set tdf = db.CreateTableDef("tblDaoBooking")
With tdf
'Autonumber
Set fld = .CreateField("BookingID", dbLong)
fld.Attributes = dbAutoIncrField + dbFixedField
.Fields.Append fld

'BookingDate
.Fields.Append .CreateField("BookingDate", dbDate)

'ContractorID
.Fields.Append .CreateField("ContractorID", dbLong)

'BookingFee
.Fields.Append .CreateField("BookingFee", dbCurrency)

'BookingNote: Required.
Set fld = .CreateField("BookingNote", dbText, 255)
fld.Required = True
.Fields.Append fld
End With

'Save the Booking table.
db.TableDefs.Append tdf
Set fld = Nothing
Set tdf = Nothing
Debug.Print "tblDaoBooking created."

'Clean up
Application.RefreshDatabaseWindow 'Show the changes
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
End Sub

 

[Tim Ferguson]

I agree, this looks like a bad idea based on a weak design.
Personally, I would use VBA code to evaluate the data coming in and
take the appropriate action.

Yes - that too. It depends on what the correct procedure is for the
incorrect data. If it's a table of Cars, and the field is Colours:

how does a value of "Ref" get translated to "Red"?

how does a value of "Metallic Scarlet" get translated to this year's
equivalent, "Bright Red Lustre"?

how does a value of "Bright Green Lustre" get into the lookup table,
having been introduced in this month's catalogue?

Three different solutions required for errors in one field!

All the best


Tim F