Semi-beginner question for a VBA module in Access 2002 (we all have to
start somewhere!):

I have three tables in Access.
One is the original data (tblOriginal), containing 4 fields: StID,
Name, Pre, Type. One contains multiple records of selection criteria
(tblCriteria), three of which are used to match those in tblOriginal:
Name, Pre, and Type. The third table is the destination table
(tblAltNames), into which I want to insert matching records with all
four fields from tblOriginal.

I would like to loop through tblCriteria, building an SQL query for
each record that SELECTs FROM TblOriginal and INSERTs INTO
tblAltNames.

FYI - I'm using DAO so far because I figured it would be an easier
learning curve but it doesn't seem to be helping... Ultimately it
should be ADO anyway, so I'd appreciate advice for either.

Here's what I have:

Public Sub BuildAltNamesTable()

Dim db As Database
Dim rec As Recordset
Dim strSELECT As String
Dim strFROM As String
Dim strINTO As String
Dim strWHERE As String
Dim strSQL As String

strSELECT = "StID, Name, Pre, Type"
strFROM = "tblOriginal"
strINTO = "tblAltNames"

Set db = CurrentDb()
Set rec = db.OpenRecordset(tblCriteria, dbOpenSnapshot)

rec.MoveFirst
Do While Not rec.EOF

 If IsNull(tblCriteria.Name) Then
   strWHERE = strWHERE & " AND tblCriteria.Name = tblOriginal.Name"
 End If 

 If IsNull(tblCriteria.Pre) Then
   strWHERE = strWHERE & " AND tblCriteria.Pre = tblOriginal.Pre"
 End If 

 If IsNull(tblCriteria.Type) Then
   strWHERE = strWHERE & " AND tblCriteria.Type = tblOriginal.Type"
 End If 

 strSQL = "SELECT " & strSELECT
 strSQL = strSQL & "FROM " & strFROM
 strSQL = strSQL & "INTO " & strINTO
 If strWHERE <> "" Then 
   strSQL = strSQL & "WHERE " & Mid$(strWHERE, 6)
 End If

[execute strSQL? help needed]

 rec.MoveNext
Loop

rec.Close
Set rec = Nothing

End Sub

Hi polkadot-ga,

Replace [execute strSQL? help needed] with the following lines of code:

db.Execute strSQL    ' Execute the sql statement
strWHERE = ""        ' Ready for next iteration

Hope this helps.

thanks for the suggestion -- this was what I was looking for, and I
managed to fix some other trouble myself (and with some help at
experts-exchange.com)