If I have a query: sql = "SE

[Perra]

If I have a query:

sql = "SELECT Record.Rnr, Sname, SLength FROM Record, Song, Record_Song WHERE Title = 'Mr Music Hits vol 5' AND Record.Rnr = Record_Song.Rnr and Record.Snr = Song.Snr"

If I want to retrieve results into a VB application, the above query takes into consideration three tabels, which have to be joined.

How do I get this query to work in VB?
How do I manage the "intrinsic data control" or "ADODC" with a query like this?

I think I have to operate with data controls for each table, but I don´t know how to cope with the above query, since each data control only works with the recordsource it is set to.

I want to work with SQL-statements, because I feel comfortable with SQL.
I know I can do the above by defining "criterias" and so on, but the strength of SQL must be
possible to exploit in the above case?

The Logical Database Design model with its relations is as folows:

Record --> Song(N:N) via the connection-relation Record_Song

Artist --> Song(1:N)

All in all there are four tables.

I have Primary Keys as counters in each parent-relation and the corresponding FK:s in the child-relations with the datatype "number" as is usual.

The tables are:

Record Artist
------ ------
Rnr (pk) Anr (pk)
Title Aname
Date
Notes


Song
----
Snr (pk)
Sname
Length
Anr (fk_artist)


Record_Song (to allow many-to-many)
-----------
Rnr (pk)
Snr (pk)
Notes
//Rnr, Snr is an aggregate
//primary key in this relation

This was a long one (puh) :-Q

Hope anyone finds this question as interesting as I do...

/Perra

 

[FerakIII]

In my understanding, you can set the RecordSource of any ADO recordset to an SQL statement.

so something lik

Dim Db As Database, Rs As Recordset

Set Db = Workspaces(0).OpenDatabase("Music.mdb&quot
Set Rs = Db.OpenRecordset("SELECT Record.Rnr, Sname, SLength FROM Record, Song, Record_Song WHERE Title = 'Mr Music Hits vol 5' AND Record.Rnr = Record_Song.Rnr and Record.Snr = Song.Snr&quot

then use the rs as normal I think...