molvo
asked on
how to loop through table1 to select from table2, insert into table3
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. **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(tblCriter ia, 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
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. **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(tblCriter
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
>> [execute strSQL? help needed] <<
docmd.runSQL strSQL
docmd.runSQL strSQL
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks. The execute syntax was the hole I knew I was missing, but I was hoping for some help getting the rest to run as well. Mind if I ask for a bit more? After filling in the execute SQL line, I get a variable not defined error on tblCriteria in the If IsNull statements, e.g.,
If IsNull(tblCriteria.Name) Then '****this is the line where the error occurs
strWHERE = strWHERE & " AND tblCriteria.Name = tblOriginal.Name"
End If
Any ideas?
And does the rest of the logic, structure, etc. look right?
BTW, eventually it will be shared by people with SQL Server DBs, so that's why I was thinking ADO, but I'm okay taking it slowly in DAO for now.
Thanks again.
If IsNull(tblCriteria.Name) Then '****this is the line where the error occurs
strWHERE = strWHERE & " AND tblCriteria.Name = tblOriginal.Name"
End If
Any ideas?
And does the rest of the logic, structure, etc. look right?
BTW, eventually it will be shared by people with SQL Server DBs, so that's why I was thinking ADO, but I'm okay taking it slowly in DAO for now.
Thanks again.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hmm, without knowing what data your criteria table should hold, I couldn't say if the logic is right or not. However, some corrections you need to make to the code:
Set rec = db.OpenRecordset(tblCriter ia, dbOpenSnapshot)
should read:
Set rec = db.OpenRecordset("tblCrite ria", dbOpenSnapshot)
This opens a recordset based on the table called tblCriteria. The name has to be passed to OpenRecordset as a string, or a string literal (as I have done here). Now, whereever you have:
tblCriteria.
replace with:
rec!
So in your line:
If IsNull(tblCriteria.Name) Then
this will now read:
If IsNull(rec!Name) Then
Basically, now that you have opened a recordset called rec that is based on the table tblCriteria, rec contains all of the fields (and data) from tblCriteria. So you reference the recordset, not the table. Also note that you should never use "Name" as the name of a field - this can confuse Access, since "Name" is a reserved word. If you do use it, always enclose it between [] in code. So the above line should really read:
If IsNull(rec![Name]) Then
Set rec = db.OpenRecordset(tblCriter
should read:
Set rec = db.OpenRecordset("tblCrite
This opens a recordset based on the table called tblCriteria. The name has to be passed to OpenRecordset as a string, or a string literal (as I have done here). Now, whereever you have:
tblCriteria.
replace with:
rec!
So in your line:
If IsNull(tblCriteria.Name) Then
this will now read:
If IsNull(rec!Name) Then
Basically, now that you have opened a recordset called rec that is based on the table tblCriteria, rec contains all of the fields (and data) from tblCriteria. So you reference the recordset, not the table. Also note that you should never use "Name" as the name of a field - this can confuse Access, since "Name" is a reserved word. If you do use it, always enclose it between [] in code. So the above line should really read:
If IsNull(rec![Name]) Then
Also, I suspect lines like this:
strWHERE = strWHERE & " AND tblCriteria.Name = tblOriginal.Name"
should read:
strWHERE = strWHERE & " AND tblOriginal.Name=" & rec![Name]
since you are trying to select from tblOriginal where the fields are equal to whatever data you have stored in tblCriteria (which you have just picked out in your recordset).
strWHERE = strWHERE & " AND tblCriteria.Name = tblOriginal.Name"
should read:
strWHERE = strWHERE & " AND tblOriginal.Name=" & rec![Name]
since you are trying to select from tblOriginal where the fields are equal to whatever data you have stored in tblCriteria (which you have just picked out in your recordset).
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ah, much better. Thanks. I also realized that my If IsNull...Then statements were backward; I needed an If Not IsNull for that to execute correctly.
A belated clarification, everything is text fields except the ID field in tblOriginal.
Thanks for the reminder about Name being a reserved word -- no problems in my actual table because the real fieldname is longer. I simplified the field names for use here.
So now I'm focusing on the SQL statement itself. It still seems wrong. I'm thinking I need to build something like:
INSERT INTO tblAltNames SELECT StID, Name, Pre, Type FROM tblOriginal WHERE tblOriginal.Name = ...
I'll post this as a separate question here for more points. Thanks for all your help.
A belated clarification, everything is text fields except the ID field in tblOriginal.
Thanks for the reminder about Name being a reserved word -- no problems in my actual table because the real fieldname is longer. I simplified the field names for use here.
So now I'm focusing on the SQL statement itself. It still seems wrong. I'm thinking I need to build something like:
INSERT INTO tblAltNames SELECT StID, Name, Pre, Type FROM tblOriginal WHERE tblOriginal.Name = ...
I'll post this as a separate question here for more points. Thanks for all your help.
CurrentDb.Execute strSQL
No real need to use ADO unless your back-end is something other than Jet.