Using the ADO Execute method when it returns an error.

[Guest]

I have three tables: Classes, Students, and ClassStudentLookup. I want to
populate the Lookup table from the other two tables using an Orthogonal
query. If Classes has 3 records and students has three records, I want the
Lookup table to contain 9 records. Student, Class is the Primary key in this
table.

I am using the following code:

Dim adoCmd As New ADODB.Command
adoCmd.ActiveConnection = CurrentProject.Connection
adoCmd.CommandType = ADODB.CommandTypeEnum.adCmdText
adoCmd.CommandText = "INSERT INTO StudentClassLookup ( Class, Student )
SELECT Classes.Class, Students.Student FROM Classes, Students;"
adoCmd.Execute

This code runs fine if StudentClassLookup table is empty. It returns a
duplicate values in index error if a record already exists. What I need is a
way to either (1) have the execute method execute for those records that
don't already exist or (2) find a way to tweak the query so that it makes the
Execute method happy. Any help will be appreciated.

 

[Guest]

Hi -

Is this a homework assignment for a class by any chance? Seems like a
classic linking table. The assumption that every student takes every class
doesn't quite seem "real world" but what the heck.

I take it you are wanting to run the query an additional time after adding a
couple students or classes to your source tables? Easiest solution: delete
the linking table records before rerunning. This is often more efficient than
trying to identify dups.

Another approach: use outer joins to link to the linking table, and set the
criteria to "is null" (see the New Query "Find Unmatched Query Wizard" for an
example).

- Phil Freihofner

 

[Guest]

This is real world. I simplified the situation to make it easier to explain.

I have about 14 users and 95 parameters. I want to track a value for each
user-parameter setting. I tried various approaches by using outer joins,
nulls, etc. I could not find one that worked.

If you would like to give it a try, Assume that table 1 contains 2 columns:
Parameter and Default value (95 records). Table 2 contains a single column:
User (14 records). Table 3 contains three columns: User, Parameter,
UserValue. In this instance, Table 3 should contain 95*14 records. If a
record is missing, I want to create the record using the Default value from
table 1 as the UserValue. It seems like a simple query should be possible
but I could not find one. I ended up doing a clutzy For Next loop but would
like a simpler solution if one exists.

 

[Dale Fye]

I strongly suggest you make a backup of your database before continuing.

1. Create your orthoganal query as a select query; call this query 1

2. Create a second query that links query1 to Table3, use a left Join from
Query1 to Table3. The purpose of this query is to identify all of the
records in Query1, that don't already exist in Table3.

SELECT Q.User, Q.Parameter, Q.DefalutValue
FROM Query1 Q
LEFT JOIN Table3 T
ON Q.User = T.User AND Q.Parameter = T.Parameter
WHERE T.User IS NULL

Once you have this working, change the query to an Append query, Select
Table3 as the table to append to, and run your query.

If you have to do this all as a single SQL string, it would look like:

SELECT Q.User, Q.Parameter, Q.DefalutValue
FROM (SELECT U.User, P.Parameter, P.DefaultValue
FROM User U, Parameters P) as Q
LEFT JOIN Table3 T
ON Q.User = T.User AND Q.Parameter = T.Parameter
WHERE T.User IS NULL

HTH
Dale