ADO Recordset Question

[NJLDoc]

Is there anyway to create a recordset object based on two separate yet password protected access databases.

Not syntactically correct obviously but this is what I want to accomplish.

Select FieldA, FieldB From tblOne in database One and then select FieldC, FieldD From tblTwo in database Two into a single recordset I can interrogate.

The idea I have tried unsuccessfully was

With rsCombined
For Each fld In rsOne.Fields
.Fields.Append fld.Name, fld.Type, fld.DefinedSize, adFldIsNullable, fld
.Update
Next fld
For Each fld In rsTwo.Fields
.Fields.Append fld.Name, fld.Type, fld.DefinedSize, adFldIsNullable, fld
.Update
Next fld
End With

I am using ADO 2.7 and MSAccess 97

Any help would be greatly appreciated.

 

[harebrain]

That would make a great UNION query. Problem is, I don't know if you can do that in Access 97 (works in Access 2000.) Go to your help file and search for "union_query". I hope you find it, because it would make things very simple for you, as in

SELECT a, b FROM tblX
UNION
SELECT c, d FROM tblY

That, of course, can be executed in an ADO command to return a recordset.

HTH,
David

 

[NJLDoc]

I have used union queries effectively in the past to create a combined list of records from different tables in the same database. The issue here is that I need the records to come from two different databases each of which is password protected. The syntax of the union query I believe prevents this.

 

[harebrain]

Can you create a link in the first db to the table in the second db? Then you ought to be able to do a union query on the linked table, no?

 

[NJLDoc]

The concern I have is that one database resides on the server and the local database gets synchronized with a mobile device. This means linked tables do not synchronize and the data I want to retrieve has to be managed in code.

I am missing something in my understanding of the ADO fld .append logic. If I conceptualize a recordset as 'table like' then adding fields to the recordset gives me combined fields (not Union Records to common fields) with data from tableOne in DatabaseOne and different fields with data from tableTwo in DatabaseTwo.

I hope I am starting to make sense and I do certainly appreciate your efforts.

 

[harebrain]

Ok, now I see the snag.

Paragraph two: sounds like you have the idea correct.

I'm out of ideas on this now, though; will have to think about it more.

 

[NJLDoc]

I really hope you can come up with something. This is driving me nuts.

Again, thanks for all your efforts.

 

[VBOldTimer]

Use the "IN" keyword

 

[vbkris]

try this,
open two different connections and recordsets.
while displaying combine the recordsets.

if u have to use a while clause for fields contained in the query do the following
instead of displaying the recordsets insert them into a new table, run the where clause on that table.

am i to gibberish?

 

[NJLDoc]

Thanks for the ideas.

VBOldTimer: Your suggestion of the IN key word has served me well previously when usind DAO and with databases not having passwords. I have absolutely no idea of the syntax required using ADO and requiring a password. If you can answer the syntax problem I have then you have solved my problem. In fact the IN word effectively has limited the need for temporary tables which is my ideal solution.

vbkris: Your suggestion will work quite clearly though means I have to use a temporary table or at least a table for managing what I think should be "doable" in recordsets alone.

To take things a step further, the recordset I am trying to create will also be used as a source of data for Crystal Reports. The effective code I use while a recordset is based on single database is.
With myReport
.Database.AddADOCommand myRS.ActiveConnection, myRS.ActiveCommand
.AutoSetUnboundFieldSource crBMTName
End With

The following code accomplishes the recordset but gives errors in the report causing fatal error in VB6 and shuts down the VB IDE completely.

Set rsCombined = New ADODB.Recordset
With rsCombined
For Each fld In myRSdb1.Fields
.Fields.Append fld.Name, fld.Type, fld.DefinedSize
Next fld
For Each fld In myRSdb2.Fields
.Fields.Append fld.Name, fld.Type, fld.DefinedSize
Next fld
.Open
.AddNew

For Each fldCombined In rsCombined.Fields
For Each fld In myRSdb1.Fields
If fld.Name = fldCombined.Name Then
.Fields(fld.Name) = fld.Value
End If
Next fld
For Each fld In myRSdb2.Fields
If fld.Name = fldCombined.Name Then
.Fields(fld.Name) = fld.Value
End If
Next fld
Next fldCombined
.Update

End With

Set myRS = rsCombined 'myRS is a public ADODB.Recordset variable

 

[CCLINT]

Even when using ADO, you are passing (or ADO rather) the sql here to JET to be processed, so you need to stilluse JET's syntax, as you did when using DAO. It will be JET, not ADO, that will create the second connection to the external DB:

"conn" is an ADO connection.

conn.Execute("SELECT * From TheTable IN '' [;DATABASE=E:\Test\myData.MDB;pwd=123456789]&quot

 

[NJLDoc]

CClint I have found your suggestions in other areas enlightening and pleased this inquiry spurred your interest. I have a complex, at least to me, query to construct and the syntax of using the IN key word still escapes me. Brain freeze perhaps but still don't see the light.

This in essence is what I would like in syntactically correct format if possible.

SELECT * From tblOne IN dbOne AND * From tblTwo IN dbTwo WHERE tblOne.fldOne = tblTwo.fldTwo

Both dbOne and dbTwo are passwordprotected database.

 

[CCLINT]

The below example uses JET to perform a Union query on two identical tables in two diffrent Mdbs, each password protected with different passwords, and passes the results back to an ADO recordset object.

Sub SomeUnionQuery(conn As ADODB.Connection)
Dim rs As ADODB.Recordset
Dim sqlString As String
Set rs = New ADODB.Recordset

sqlString = "SELECT * From SomeTable " _
& "IN '' [;DATABASE=C:\Data1\First.mdb;pwd=987654321] " _
& "UNION " _
& "SELECT * From SomeTable " _
& "IN '' [;DATABASE=C:\Data2\Second.mdb;pwd=123456789]"

With rs
.ActiveConnection = conn
.CursorLocation = adUseServer
.CursorType = adOpenStatic
.Source = sqlString
.Open Options:=adCmdText Or adAsyncFetch
End With
End Sub

 

[NJLDoc]

I do not want a Union select as the fields from each table are of different data types and need to comprise the same record. Using the structure you have provided makes me wonder why the following does not work:

SELECT tblOne.*,tblTwo.*" _
& " FROM tblOne IN '' [;DATABASE=C:\Data1\First.mdb;pwd=987654321], tblTwo IN '' [;DATABASE=C:\Data2\Second.mdb;pwd=123456789]" _
& " WHERE tblOne.fldFilter='" & strFilter & "'" _
& " AND tblOne.fldOne = tblTwo.fldOne" _
& " AND tblOne.fldTwo = tblTwo.fldTwo;"

 

[CCLINT]

You can adjust for the different field names in a Union query using alias field names, but as for different field types, how do you expect this to work?
How should the fields combine? A Numeric field and a Date field: What is the result field Type? And value?

The only way to combined records and fields of different types into fields with an identical data type would be to use a temporary table, with all fields set to TEXT, and using an action query to fill the table with data from both tables.

If you are using ADO and JET 4, you will need then to set the JET's Global Partial Bulk Ops parameter to 1, otherwise, it will error out on NULL values (missing dates or numbers).

But this:
SELECT tblOne.*,tblTwo.*"
Would only make sense if both tables had different fields and a foreign key such as a Customer number, and table one has customer details and table two has order details, or something like that.
And that, is a JOIN statement. Not a UNION query.

You stated:
"I have used union queries effectively in the past to create a combined list of records from different tables in the same database..."
So, I am assuming you know that a union query combines records with identical fields, and doesn't join fields from two tables for a certain record.
And that this was your goal.

And I was assuming it was a matter of it just not being possible from two different data sources.
Therefore my answer to your comment on the syntax.

Then you stated:
"I am missing something in my understanding of the ADO fld .append logic. If I conceptualize a recordset as 'table like' then adding fields to the recordset gives me combined fields (not Union Records to common fields) with data from tableOne in DatabaseOne and different fields with data from tableTwo in DatabaseTwo."

So now we are talking about a JOIN query, combining fields from two tables having a common relation.

So, are you looking to combine fields for a common record:

First Table's fields: OrderNo, ProductID, Amount
Second Table's fields: ProductID, ProductName

JOIN: CustomerID, OrderNo, ProductID,ProductName, Amount

Or:
First Table's fields: OrderNo, ProductID, Amount
and 30 records

Second Table's fields: OrderNo, ProductID, Amount
and 20 records

UNION: OrderNo, ProductID, Amount
Returns 50 records

 

[NJLDoc]

Sorry for the confusion I've caused CClint and I do appreciate your continued efforts to help. I am aware that a UNION query merges fields from different tables and this is not what I want. The JOIN concept is exactly what I want only the two tables to be joined exist in different password protected databases.

Your option one of:

First Table's fields: OrderNo, ProductID, Amount
Second Table's fields: ProductID, ProductName

JOIN: CustomerID, OrderNo, ProductID,ProductName, Amount

This is the outcome I would like

 

[CCLINT]

>UNION query merges fields[records]...

Then you will not beable to do this in a single query.
There is only one "IN" allowed per SELECT statement (a UNION query has multiple SELECT statements)

But there is a way. And it is not too difficult.

1. Add reference to the ADOX library

2. Using ADOX, link the table from the one DB to the current DB used in the connection object.

3. Use a simple JOIN query:

SELECT Table1.ID, Table1.FieldX, Table2.FieldZ
FROM Table1
INNER JOIN Table2
ON Table1.ID = Table2.ID

4. After the recordset is no longer needed, or closed, just remove the linked table.

 

[NJLDoc]

Excellent help. Thank you all.