how to loop through table1 to select from table2, insert into table3
Posted on 2004-08-29
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
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. **Bonus
points** for ADO sol'n.
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)
Do While Not rec.EOF
If IsNull(tblCriteria.Name) Then
strWHERE = strWHERE & " AND tblCriteria.Name = tblOriginal.Name"
If IsNull(tblCriteria.Pre) Then
strWHERE = strWHERE & " AND tblCriteria.Pre = tblOriginal.Pre"
If IsNull(tblCriteria.Type) Then
strWHERE = strWHERE & " AND tblCriteria.Type = tblOriginal.Type"
strSQL = "SELECT " & strSELECT
strSQL = strSQL & "FROM " & strFROM
strSQL = strSQL & "INTO " & strINTO
If strWHERE <> "" Then
strSQL = strSQL & "WHERE " & Mid$(strWHERE, 6)
[execute strSQL? help needed]
Set rec = Nothing