Retrieve from 2 tables

[findermi]

I wrote a program that retrieved employee information from a sql table. The new version of software has split that table into two tables and now I must get data from both tables using a common field, employee number. I wrote the original program with an ado control. What would be the best way to access the second table... another ado control or some other way?
Bob

[andyleno]

The best way to retreive this info from both the table is to create a StoredProcedure, which is probably not something you want to get lost in.

So a simple select statement:

SelectString = "SELECT * FROM TABLE1,TABLE2 WHERE EmployeeNumber=''"




If you need code to help you with the recordset object just let me know.

Andy

Don't forget to rate!!!!

Andrew Lennon (Berlitz)
Automation Dev Engineer

[findermi]

Thanks for the reply. A little while ago I tried a different approach but still run into a problem. Here is a bit of the code:

'open the recordset and move to the first record
With adoNewHireMain.Recordset
.MoveFirst

'loop thru write process X times = to number of records found

Do While Not .EOF

' determine employ number of current employee
strEmployID = !EMPLOYID

' start building the select text for UPR00102
strSelectText = "select * from UPR00102 where EMPLOYID = " & strEmployID

strSSN = !SOCSCNUM
LSet strLSSN = strSSN

strFirstName = !FRSTNAME
LSet strLFirstName = strFirstName

strMI = !MIDLNAME
LSet strLMI = strMI

strLastName = !LASTNAME
LSet strLLastName = strLastName

' get the info from the other table

cmdUPR00102.CommandType = adCmdText
cmdUPR00102.CommandText = strSelectText

'*******************************************
'start getting upr00102 info

strEmployeeAddress1 = !ADDRESS1
LSet strLEmployeeAddress1 = strEmployeeAddress1


The problem occurs when I hit the last couple of lines trying to get the Address into which is on the second table. I get the error message, "Item cannot be found in the collection corrersponding to the requested name or ordinal." I suspect it's trying to read the ADDRESS1 field of the first table instead of the second one.
Bob

[findermi]

Could you give me help with the recordset object?

Bob

[TH1]

Join the tables in your sql statement

sql="select Table1.Field1,Table1.Field2,Table1.Fieldn,
Table2.field1,Tabl2.Field2,Table2.fieldn from Table1 inner join Table2
on Table1.Employee_number=Table2.Employee_no"



Obviously change Table1 and Table2 to your table names and Field1,Field2 etc to the fields from your tables.