A question on technique! 4

[Smitty020]

OK, here's what I am trying to do. I have a function that queries two different tables in a DB using 2 queries. I eventually want to get the info from both of these queries into one array, and pass it to another function to be printed out.

What I did was set each SQL statement = to a recordset.
Now, I am attempting to read each recordset into the same array. I just not sure what I am doing wrong.

Does this make any sense.....am I on the right track? Or does anyone know of a better method?

You help is greatly appreciated!

 

[johnwm]

Look up the SQL UNION operator in VBHelp - it will do just what you're after straight into one recordset Let me know if this helps
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[TomKane]

I don't see why that can't be done - Without knowing what it it that's going wrong exactly, a couple of things I'd say are:

1) Be careful you don't overwrite the contents of your array with the second recordset (apologies if I'm stating the obvious)

2) You could use a collection instead of an array

3) Maybe you could use UNION to join the two queries into one

Hope this helps....

 

[Smitty020]

Thanks for the help guys, the thing is though, and what I meant to explain in the first place is this.

The first query gets half of the attriutes from one table, in one database.

Ex Name, Address ect.....


The second query gets the other half of the attributes from another table in a DIFFERENT DB.

Ex. Employee#, Office_Num, ect....

So what I want is all of this info in an array....

Name, Address, Employee#, Office #

then I want to pass the array to another function.

We've been using arrays for alot of things, and we're trying to stay as consistent as possible.


How would a union work here? Do you see what I am trying to do or am I being too confusing?

Thanks for the help!

 

[woyler]

Another way would be to define your own recordset. Then loop through your fist recorset, populating the the fields in your custom rs, then repeat for the second rs. At that point you can return 1 custom recordset populated by the 2 queries

 

[Smitty020]

Thanks Woyler, that's what I think I'm going to end up doing. Let me ask you though, can I combine them into an array instead.

Like this:

Do until RS.EOF
NewArray(x,y) = RS!Field1
x = x + 1
NewArray(x,y) = RS!Field2
x = x + 1
NewArray(x,y) = RS!Field2
x = 0
RS.Movenext
Loop

Then just add the second RS on after that?

I already have 2 recordsets.

Thanks for your help!

 

[woyler]

I think it may be a bit easier to maintain if you create one custom rs out of the two(like we discussed above), then populate the array based on just the one rs

 

[LlomaxX]

Since your question is on technique . . . I think most VBers would create an Employee object, with properties, like:

Employee.Name
Employee.Address
Employee.Number
Employee.OfficeNumber

Then, create a collection of Employee Objects (You can call the collection Employees). Set the key as something unique, like the Employee Number. Now, you can reference the Employee by Number:

Employees(varEmpNum).Name

You can also enumerate through the collection:

Dim Emp as clsEmployee

For Each Emp in Employees
If Emp.OfficeNumber > 15 Then
[whatever]
End if
Next Emp

And, you can pass this collection to other functions or forms. (Every tried passing an array to anything? Try it sometime!)

Anyway...that's my opinion, for what it's worth.

 

[StewartJ]

I think you just need to join your tables in the one SQL as :

SELECT ed.Name, ed.Address, od.OfficeNo, od.TelNo
FROM Employees ed JOIN Offices od
ON od.EmployeeNo = ed.EmployeeNo

 

[Smitty020]

They are in different DB's though, and I would really like to leave everything on the back-end the way it is. Any other suggestions. I like to stick with the arrays. So if anyone could help me with that, I'd appreciate it. I think I'm doing it correctly, however I'm getting no data.

Here's what I've done so far.

**************************************
1. Built two queries to get the needed info.
2. Set the queries = to recordsets.
3. Inserted the first recordset into an array.
4. Inserted the second recordset into the same array.
5. Now I want to pass the array to another function ByVal
*********************************************

Thanks for your help!

 

[GJParker]

Smitty020 I can't help but agree with LlomaxX that it would be far easier for you to create an Employee object that you can then add into a collection.

Although I understand your wish for consistency ask yourself is this the correct approach for the job in hand. Gary Parker
Systems Support Analyst

 

[Smitty020]

OK ok ok ok. I give in! You guys are right. I guess this hassle isn't worth consisently staying consistent. However, I don't have very much experience using collections. Would you happen to know anywhere that I could find a brief tutorial/example. I do see what LlomaxX put up, but I'd like to read into it a little more.

Thanks guys!

 

[woyler]

Any reason you do not want to just pass the recordset?

 

[Smitty020]

Well, the function that I am calling it to prints the data to a chart that I made in Word using Bookmarks.

Basically I wanted to do a

For i = 0 to UBound(Array)
'Place the data in the chart
Next i

I have 2 recordsets also. Coming from 2 different DB's, however they do have a common attribute (ID)

It's not that I am strict in my methods, it's that I am newer to VB and am looking for the easiest but most appropriate way to conquer this problem. I welcome any ideas that you futher have. Also, if your talking about passing recordsets, (remember I have 2), how exactly would you do that?

Thanks woyler!

 

[woyler]

Maybe, It would help if you post the structures of the recordsets you have.

 

[CmPaiva]

Hi. Have you tried to create a JOIN query, using a link to the table in the external DB. With this, you would get a single recordset, filled with all info from both tables...

Tell me if this helps.

Carlos Paiva

 

[Smitty020]

For example, if I had

RS1 =

ID1 Name1 Address1 Zip1
ID2 Name2 Address2 Zip2
ID3 Name3 Address3 Zip3
ID4 Name4 Address4 Zip4

and:

RS2 =

ID1 Age1 DOB1
ID2 Age2 DOB2
ID3 Age3 DOB3
ID4 Age4 DOB4


What I want is to pass:

ID1 Age1 DOB1 Name1 Address1 Zip1
ID2 Age2 DOB2 Name2 Address2 Zip2
ID3 Age3 DOB3 Name3 Address3 Zip3
ID4 Age4 DOB4 Name4 Address4 Zip4

to another function to display.

Remember those Recordsets come from different DB's and I don't/wouldn't know how to do a join that way(even if it's possible, and I don't want to keep the back-end the way it is!) This is why I wanted to combine the recordsets into an array in the first place.

If you have any ideas/critcism/thoughts.......I'm all for it. Thanks for helpin me out!

 

[woyler]

Do the numbers (ID1,ID2,Zip1,Zip2) denote records or are they the actual field names?
Will the ID field always have a match in the other rs?

 

[Smitty020]

Yes, the zip both represent numbers and ID1 will match the other ID1