NOTE This is a draft and will be edited in the near future. This process has worked in the past, but is not yet something we are comfortable calling a Best Practice.

Importing Connections from a Non-Metrix Source

The Process

Connections are not part of the Import Wizard. The data structure of Connections is rather complex, in that there are two records required for every Connection (a parent record and a child record), and there are fields in the table that refer to other records in the Connections table. Study tblConnections for a bit to make sure you have a sense of what data are stored there. You can get technical information about the table in our Data Dictionary. This method adds these two types of records (parent and child) in batches.

This method was developed while working with a complex build--about 8,000 contacts and 25,000 connections! The original data was in a format quite different from what we needed for Metrix, so the first step was to whip the data into shape--a worksheet that had ContactID and then a field for each of the different Connection types the organization had. Here are the first few records from that table, after importing into an mdb. Getting it into this format does take quite a while, but it allows you to be systematic about creating the Connections, which hopefully means that others will be able to use this process. (Actually, this table only shows a few of the Connection types. In reality there were 12.)

Another trick is to modify tblConnections. Copy tblConnections from the Metrix back end to the mdb and then follow these instructions:

• Change the name of the ConnectionID field to PKey--this will be the autonumber field.
• Add a new field called ConnectionID--this is the field we update in the operations below.
• Copy the table to a new table called "Connections", but only copy the structure, not the data. This makes sure that your autonumber field starts at 0--not necessary, but it makes it easier to sanity check your data.

Once it is in this format and imported to the mdb, you'll have to build a series of update queries, and write some code to run the queries in the appropriate order (or just use the code included here, if you match our naming conventions). Doing it like this (and making backups) enables you to run through the process a bunch of times to make sure you are getting the results you need.

Sample Query List

Below is the list of queries from the build on which this was first developed. Starting with qryAlternateToStudent1Front you can see that there are two queries per connection type. The first three queries are different--one or two of them get run after each of the other queries, adding the reciprocal IDs and connection IDs. First we'll walk you through the code and then we can take a look at some of the individual queries.
 
 

The Code

Here's the basic process (this happens for each Connection type):

• Insert, into a modified copy of tblConnections, the parent records for one type of Connection.
• Update the ConnectionID for those records (based on an autonumber field in my copy of tblConnections).
• Insert the child records for that same type of Connection, including the ReciprocalID, taken from the parent records added in the first step.
• Update the ReciprocalIDs in the child records, using the ConnectionIDs in the parent records.
• Update the ConnectionIDs in the child records, using the ReciprocalIDs in those same child records.

Here's the code that calls all of the queries:

Function RunAllTheQueries()
Dim strSql As String
Dim rst As DAO.Recordset

'Create a recordset of all of the queries named for specific connection types.
strSql = "SELECT Name FROM mSysObjects WHERE Type = 5 AND left(Name, 3) = 'qry' ORDER BY Name"
Set rst = CurrentDb.OpenRecordset(strSql, dbOpenSnapshot)

'Loop through the queries one at a time.
Do Until rst.EOF
 'Run the query.
 DoCmd.OpenQuery (rst\!Name)

If InStr(1, rst\!Name, "1") > 0 Then 'This creates the first half of the records for this connection type.
 'Set the reciprocal ID for these records
 DoCmd.OpenQuery ("a1SetRecipInOrig")
 End If
 If InStr(1, rst\!Name, "2") > 0 Then 'This creates the second half of the records for this connection type.
 'Set the reciprocal ID for these records
 DoCmd.OpenQuery ("a2SetRecipInRecip")

'Set the Connection Id for these records
 DoCmd.OpenQuery ("a3SetConnInRecip")
 End If
 rst.MoveNext
Loop
End Function

The Queries from one Connectin Type

Below are the SQL strings of the queries run to build one Connection type. Note that there are five of them:

• Build the "parent" records.
• Update the ConnectionID for those records.
• Insert the child records.
• Update the ReciprocalIDs in the child records.
• Update the ConnectionIDs in the child records.

Here's the SQL of one of the "parent" queries, qryStudentSchool1Front:

INSERT INTO Connections (ParentID, ParentType, ChildID, ChildType, 
  RelationshipDescription, fPrimaryOrgContact )
SELECT Sandbox.tmpContactID, Sandbox.ContactRecordType, Sandbox_1.tmpContactID, 
  Sandbox_1.ContactRecordType, "StudentToSchool" AS Expr2, No AS Expr1
FROM Sandbox AS Sandbox_1 INNER JOIN Sandbox ON Sandbox_1.ContactName = Sandbox.[Student-School]
ORDER BY Sandbox_1.tmpContactID DESC;

The Sandbox table is the table shown above. If the record represents a student, the field "Student-School" holds the name of that student's school. If the record represents a school, the field "ContactName" holds the name of the school. By linking two copies of the table to each other, on these two fields, we link the student to his or her school. The query then inserts a row in Connections to represent the parent side of this connection.

Once that is run, the code runs a1SetRecipInOrig. The SQL for that is:

UPDATE connections SET connections.ConnectionID = ([pkey]*2)-1
WHERE (((connections.ConnectionID)=0 Or (connections.ConnectionID) Is Null));

This updates the ConnectionID field, based on the PKey field.

Next the code runs the "child" query for this connection type. In this case it's qryStudentSchool2Back:

INSERT INTO Connections ( AccessReciprocalID, ParentID, ParentType, ChildID, 
  ChildType, RelationshipDescription, fPrimaryOrgContact )
SELECT Connections.ConnectionID, Connections.ChildID, Connections.ChildType, Connections.ParentID, 
  Connections.ParentType, "SchoolToStudent" AS Expr1, No AS Expr2
FROM Connections
WHERE (((Connections.RelationshipDescription)="StudentToSchool"));

Now the code runs two more queries, a2SetRecipInRecip and a3SetConnInRecip.
Here's a2SetRecipInRecip:

UPDATE Connections AS Connections_1 
INNER JOIN Connections ON Connections_1.AccessReciprocalID = Connections.ConnectionID 
SET Connections.AccessReciprocalID = [connections_1].[AccessReciprocalID]+1
WHERE (((Connections.AccessReciprocalID) Is Null Or (Connections.AccessReciprocalID)=0));

And here's a2SetRecipInRecip:

UPDATE Connections AS Connections_1 
INNER JOIN Connections ON Connections_1.AccessReciprocalID = Connections.ConnectionID 
SET Connections.AccessReciprocalID = [connections_1].[AccessReciprocalID]+1
WHERE (((Connections.AccessReciprocalID) Is Null Or (Connections.AccessReciprocalID)=0));

Moving the Data Back to Metrix

Now it's time to get the data back into tblConnections in the Metrix back end.

This portion of the process needs more thorough documentation, which will come shortly. In the original build, I believe I simply linked to the tblConnections in the mdb, from the Metrix back end and then ran an insert query from that linked copy to the live tblConnections, though there were, I think, some hoops to jump through on this, because of the Identity field.