?
Solved

how to loop through table1 to select from table2, insert into table3

Posted on 2004-08-29
9
Medium Priority
?
569 Views
Last Modified: 2012-05-05
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(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
0
Comment
Question by:molvo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
9 Comments
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11927861
To execute your SQL, just do:

CurrentDb.Execute strSQL

No real need to use ADO unless your back-end is something other than Jet.
0
 
LVL 7

Expert Comment

by:rockmuncher
ID: 11927864
>> [execute strSQL? help needed]  <<

docmd.runSQL strSQL


0
 
LVL 41

Assisted Solution

by:shanesuebsahakarn
shanesuebsahakarn earned 800 total points
ID: 11927873
A couple of points in favour of the Execute method over RunSQL - it doesn't pop up the confirmation dialogs, and can be around 25% faster. Both will work though.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:molvo
ID: 11928634
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.
0
 
LVL 7

Assisted Solution

by:rockmuncher
rockmuncher earned 200 total points
ID: 11928646
You haven't set tblCriteria to be anything.  You should be using the rec recordset variable instead, like this

If IsNull(Rec!Name) Then
   strWHERE = strWHERE & " AND tblCriteria.Name = tblOriginal.Name"
 End If
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11928655
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(tblCriteria, dbOpenSnapshot)
should read:
Set rec = db.OpenRecordset("tblCriteria", 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
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11928666
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).
0
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 800 total points
ID: 11928668
Sorry, if those are text fields, they should read:
strWHERE = strWHERE & " AND tblOriginal.Name=""" & rec![Name] & """"
0
 

Author Comment

by:molvo
ID: 11932521
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.
0

Featured Post

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question