Wrox Programmer Forums
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old March 14th, 2005, 05:31 PM
Authorized User
Join Date: Feb 2005
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
Default Record Macro?


I have been told that there used to be an option to record a macro in access '97 so that it would record your steps and then this can be saved as a macro and run when needed.

I need to be able to have relationships automatically added between three tables and thought the best way would be to use this record macro option. It doesnt seem to be part of Access now..

does any one know anything about this.. or how I could get Access to assign relationships to the tables automatically?

Old March 15th, 2005, 09:07 AM
Friend of Wrox
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts


   Open VBA and do a search for "create relationship". Here is an example from Northwind:

"CreateRelation Method Example

This example uses the CreateRelation method to create a Relation between the Employees TableDef and a new TableDef called Departments. This example also demonstrates how creating a new Relation will also create any necessary Indexes in the foreign table (the DepartmentsEmployees Index in the Employees table).

Sub CreateRelationX()

    Dim dbsNorthwind As Database
    Dim tdfEmployees As TableDef
    Dim tdfNew As TableDef
    Dim idxNew As Index
    Dim relNew As Relation
    Dim idxLoop As Index

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")

    With dbsNorthwind
        ' Add new field to Employees table.
        Set tdfEmployees = .TableDefs!Employees
        tdfEmployees.Fields.Append _
            tdfEmployees.CreateField("DeptID", dbInteger, 2)

        ' Create new Departments table.
        Set tdfNew = .CreateTableDef("Departments")

        With tdfNew
            ' Create and append Field objects to Fields
            ' collection of the new TableDef object.
            .Fields.Append .CreateField("DeptID", dbInteger, 2)
            .Fields.Append .CreateField("DeptName", dbText, 20)

            ' Create Index object for Departments table.
            Set idxNew = .CreateIndex("DeptIDIndex")
            ' Create and append Field object to Fields
            ' collection of the new Index object.
            idxNew.Fields.Append idxNew.CreateField("DeptID")
            ' The index in the primary table must be Unique in
            ' order to be part of a Relation.
            idxNew.Unique = True
            .Indexes.Append idxNew
        End With

        .TableDefs.Append tdfNew

        ' Create EmployeesDepartments Relation object, using
        ' the names of the two tables in the relation.
        Set relNew = .CreateRelation("EmployeesDepartments", _
            tdfNew.Name, tdfEmployees.Name, _

        ' Create Field object for the Fields collection of the
        ' new Relation object. Set the Name and ForeignName
        ' properties based on the fields to be used for the
        ' relation.
        relNew.Fields.Append relNew.CreateField("DeptID")
        relNew.Fields!DeptID.ForeignName = "DeptID"
        .Relations.Append relNew

        ' Print report.
        Debug.Print "Properties of " & relNew.Name & _
            " Relation"
        Debug.Print " Table = " & relNew.Table
        Debug.Print " ForeignTable = " & _
        Debug.Print "Fields of " & relNew.Name & " Relation"

        With relNew.Fields!DeptID
            Debug.Print " " & .Name
            Debug.Print " Name = " & .Name
            Debug.Print " ForeignName = " & .ForeignName
        End With

        Debug.Print "Indexes in " & tdfEmployees.Name & _
            " TableDef"
        For Each idxLoop In tdfEmployees.Indexes
            Debug.Print " " & idxLoop.Name & _
                ", Foreign = " & idxLoop.Foreign
        Next idxLoop

        ' Delete new objects because this is a demonstration.
        .Relations.Delete relNew.Name
        .TableDefs.Delete tdfNew.Name
        tdfEmployees.Fields.Delete "DeptID"
    End With

End Sub


Old March 16th, 2005, 07:14 AM
Authorized User
Join Date: Feb 2005
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts

Thanks for the reply.. I'll have a look into that :)


Similar Threads
Thread Thread Starter Forum Replies Last Post
calling to xlam macro from macro inside xlsb SteveB Excel VBA 0 June 30th, 2008 06:43 PM
Delete a record row, not just the record. Coby Access VBA 1 April 30th, 2007 06:29 AM
how to add new record as first record in dataset hunzian78@yahoo.com ASP.NET 1.0 and 1.1 Professional 4 April 21st, 2006 05:23 AM
Record locking - user needs the next queued record cbtoolkit SQL Server 2000 0 December 6th, 2004 08:29 AM
Sub record not associating with Main record Ron V Access 1 August 31st, 2004 09:21 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.