Using ADO recordset in a query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all, sorry to be so dense, however, I have an application where I need to
connect to the same table in many different databases, hence, a linked table
will not work. The way that I decided to go about this was to create an ADO
recordset. Actually I have several ADO recordsets that I have created, and I
am trying to run a make table query or an append query against the recordsets
(3 tables in 2 connections) that I just created. When I run the code, I get
a runtime error 3078, The Microsoft Jet Database Engine cannot find the
table....

Can anyone explain to me what I'm doing wrong?

Thanks!
 
For clarification, here is more details on what I am doing...

Set Cxn1 = New ADODB.Connection
Cxn1.Open "Provider=" & strADOProv & "Data Source=" & strFL

Set rstMS = New ADODB.Recordset
rstMS.Open "Mailstream", Cxn1, adOpenKeyset

Set Cxn2 = New ADODB.Connection
Cxn2.Open "Provider=" & strADOProv & "Data Source=" & frm!DataFilePath

Set rstAR = New ADODB.Recordset
rstAR.Open "AnalysisResults", Cxn2, adOpenKeyset

Set rstCD = New ADODB.Recordset
rstCD.Open "cmbClientData", Cxn2, adOpenKeyset

strQualData = "INSERT INTO BHProductionReport ( MailstreamNumber,
MailstreamName, 5Digit, 3Digit, AADC, MADC, Upgrade35, UpgradeMA, Upgrade,
Reject, TotalFed, PlanetCode, SabrePlus11, SabrePlus ) " _
& "SELECT Mailstream.MailstreamNumber,
Mailstream.MailstreamName,
Sum(IIf([AnalysisResults.TrayType]=1,[Barcoded],0)) AS 5Digit,
Sum(IIf([AnalysisResults.TrayType]=2,[Barcoded],0)) AS 3Digit,
Sum(IIf([AnalysisResults.TrayType]=3,[Barcoded],0)) AS AADC,
Sum(IIf([AnalysisResults.TrayType]=4,[Barcoded],0)) AS MADC,
Sum(IIf([AnalysisResults.TrayType]=5 Or
[AnalysisResults.TrayType]=6,[FiveDigit],0)) AS Upgrade35,
Sum(IIf([AnalysisResults.TrayType]=7 Or
[AnalysisResults.TrayType]=8,[FiveDigit],0)) AS UpgradeMA,
[Upgrade35]+[UpgradeMA] AS Upgrade, CmbClientData.TotalReject,
CmbClientData.TotalFed, CmbClientData.TotalPlanetCodeApplied,
CmbClientData.TotalSabrePlus11, CmbClientData.TotalSabrePlus " _
& "FROM (Mailstream INNER JOIN CmbClientData ON
Mailstream.MailstreamNumber = CmbClientData.Stream) INNER JOIN
AnalysisResults ON Mailstream.MailstreamNumber = AnalysisResults.Streams " _
& "GROUP BY Mailstream.MailstreamNumber,
Mailstream.MailstreamName, CmbClientData.TotalReject, CmbClientData.TotalFed,
CmbClientData.TotalPlanetCodeApplied, CmbClientData.TotalSabrePlus11,
CmbClientData.TotalSabrePlus5;"

Set rstQualData = dbs.OpenRecordset(strQualData, dbOpenDynaset)
 
Nick DiPaolo said:
For clarification, here is more details on what I am doing...

Set Cxn1 = New ADODB.Connection
Cxn1.Open "Provider=" & strADOProv & "Data Source=" & strFL

Set rstMS = New ADODB.Recordset
rstMS.Open "Mailstream", Cxn1, adOpenKeyset

Set Cxn2 = New ADODB.Connection
Cxn2.Open "Provider=" & strADOProv & "Data Source=" & frm!DataFilePath

Set rstAR = New ADODB.Recordset
rstAR.Open "AnalysisResults", Cxn2, adOpenKeyset

Set rstCD = New ADODB.Recordset
rstCD.Open "cmbClientData", Cxn2, adOpenKeyset

strQualData = "INSERT INTO BHProductionReport ( MailstreamNumber,
MailstreamName, 5Digit, 3Digit, AADC, MADC, Upgrade35, UpgradeMA,
Upgrade, Reject, TotalFed, PlanetCode, SabrePlus11, SabrePlus ) " _
& "SELECT Mailstream.MailstreamNumber,
Mailstream.MailstreamName,
Sum(IIf([AnalysisResults.TrayType]=1,[Barcoded],0)) AS 5Digit,
Sum(IIf([AnalysisResults.TrayType]=2,[Barcoded],0)) AS 3Digit,
Sum(IIf([AnalysisResults.TrayType]=3,[Barcoded],0)) AS AADC,
Sum(IIf([AnalysisResults.TrayType]=4,[Barcoded],0)) AS MADC,
Sum(IIf([AnalysisResults.TrayType]=5 Or
[AnalysisResults.TrayType]=6,[FiveDigit],0)) AS Upgrade35,
Sum(IIf([AnalysisResults.TrayType]=7 Or
[AnalysisResults.TrayType]=8,[FiveDigit],0)) AS UpgradeMA,
[Upgrade35]+[UpgradeMA] AS Upgrade, CmbClientData.TotalReject,
CmbClientData.TotalFed, CmbClientData.TotalPlanetCodeApplied,
CmbClientData.TotalSabrePlus11, CmbClientData.TotalSabrePlus " _
& "FROM (Mailstream INNER JOIN CmbClientData ON
Mailstream.MailstreamNumber = CmbClientData.Stream) INNER JOIN
AnalysisResults ON Mailstream.MailstreamNumber =
AnalysisResults.Streams " _ & "GROUP BY
Mailstream.MailstreamNumber,
Mailstream.MailstreamName, CmbClientData.TotalReject,
CmbClientData.TotalFed, CmbClientData.TotalPlanetCodeApplied,
CmbClientData.TotalSabrePlus11, CmbClientData.TotalSabrePlus5;"

Set rstQualData = dbs.OpenRecordset(strQualData, dbOpenDynaset)


Nick DiPaolo said:
Hi all, sorry to be so dense, however, I have an application where I
need to connect to the same table in many different databases,
hence, a linked table will not work. The way that I decided to go
about this was to create an ADO recordset. Actually I have several
ADO recordsets that I have created, and I am trying to run a make
table query or an append query against the recordsets (3 tables in 2
connections) that I just created. When I run the code, I get a
runtime error 3078, The Microsoft Jet Database Engine cannot find
the table....

Can anyone explain to me what I'm doing wrong?

Thanks!
Click to expand...
Click to expand...

If I understand correctly what you're trying to do, it won't work the
way you're trying to do it. The fact that you have those recordsets
open isn't going to make them or their contents known to your append
query. And incidentally, this line:
Set rstQualData = dbs.OpenRecordset(strQualData, dbOpenDynaset)
Click to expand...

doesn't really make sense, since you're not going to get a recordset
back from an append query.

But maybe you can use the IN clause in your append query to identify
the databases where your source tables reside. Something like this:

strQualData = _
"INSERT INTO BHProductionReport " & _

' ... field list goes here ...

"SELECT Mailstream.MailstreamNumber,

' ... bulk of SQL string goes here ...

"FROM (Mailstream IN '" & strFL & "' " & _
"INNER JOIN CmbClientData IN '" & frm!DataFilePath & "' " & _
"ON Mailstream.MailstreamNumber = CmbClientData.Stream) " & _
"INNER JOIN AnalysisResults IN '" & frm!DataFilePath & "' " & _
"ON Mailstream.MailstreamNumber = AnalysisResults.Streams " & _
"GROUP BY " & _

' ... remainder of SQL string goes here ....
 
Dirk,

Correct in your assumption. I am trying to create the recordsets from the
tables in the remote databases, run the sql statement against the recordsets
that I just created, and send the data to a table in my BE database.

The reason for the last statement:

If I open the sql statement without the INSERT INTO, I do get a recordset
back, I'm just trying to aviod having to loop though it and write each
individual value into the new table (just being lazy).

I think part of my problem is that I have two connections open, and Access
can only work with one active connection, however, that's just a guess.

I'm also dabbling in ADO vs DAO, however I'm not convinced it's better...

I see your suggestion of:

I'll give it a try.

Thanks a bunch!
Nick
Dirk Goldgar said:
Nick DiPaolo said:
For clarification, here is more details on what I am doing...

Set Cxn1 = New ADODB.Connection
Cxn1.Open "Provider=" & strADOProv & "Data Source=" & strFL

Set rstMS = New ADODB.Recordset
rstMS.Open "Mailstream", Cxn1, adOpenKeyset

Set Cxn2 = New ADODB.Connection
Cxn2.Open "Provider=" & strADOProv & "Data Source=" & frm!DataFilePath

Set rstAR = New ADODB.Recordset
rstAR.Open "AnalysisResults", Cxn2, adOpenKeyset

Set rstCD = New ADODB.Recordset
rstCD.Open "cmbClientData", Cxn2, adOpenKeyset

strQualData = "INSERT INTO BHProductionReport ( MailstreamNumber,
MailstreamName, 5Digit, 3Digit, AADC, MADC, Upgrade35, UpgradeMA,
Upgrade, Reject, TotalFed, PlanetCode, SabrePlus11, SabrePlus ) " _
& "SELECT Mailstream.MailstreamNumber,
Mailstream.MailstreamName,
Sum(IIf([AnalysisResults.TrayType]=1,[Barcoded],0)) AS 5Digit,
Sum(IIf([AnalysisResults.TrayType]=2,[Barcoded],0)) AS 3Digit,
Sum(IIf([AnalysisResults.TrayType]=3,[Barcoded],0)) AS AADC,
Sum(IIf([AnalysisResults.TrayType]=4,[Barcoded],0)) AS MADC,
Sum(IIf([AnalysisResults.TrayType]=5 Or
[AnalysisResults.TrayType]=6,[FiveDigit],0)) AS Upgrade35,
Sum(IIf([AnalysisResults.TrayType]=7 Or
[AnalysisResults.TrayType]=8,[FiveDigit],0)) AS UpgradeMA,
[Upgrade35]+[UpgradeMA] AS Upgrade, CmbClientData.TotalReject,
CmbClientData.TotalFed, CmbClientData.TotalPlanetCodeApplied,
CmbClientData.TotalSabrePlus11, CmbClientData.TotalSabrePlus " _
& "FROM (Mailstream INNER JOIN CmbClientData ON
Mailstream.MailstreamNumber = CmbClientData.Stream) INNER JOIN
AnalysisResults ON Mailstream.MailstreamNumber =
AnalysisResults.Streams " _ & "GROUP BY
Mailstream.MailstreamNumber,
Mailstream.MailstreamName, CmbClientData.TotalReject,
CmbClientData.TotalFed, CmbClientData.TotalPlanetCodeApplied,
CmbClientData.TotalSabrePlus11, CmbClientData.TotalSabrePlus5;"

Set rstQualData = dbs.OpenRecordset(strQualData, dbOpenDynaset)


Nick DiPaolo said:
Hi all, sorry to be so dense, however, I have an application where I
need to connect to the same table in many different databases,
hence, a linked table will not work. The way that I decided to go
about this was to create an ADO recordset. Actually I have several
ADO recordsets that I have created, and I am trying to run a make
table query or an append query against the recordsets (3 tables in 2
connections) that I just created. When I run the code, I get a
runtime error 3078, The Microsoft Jet Database Engine cannot find
the table....

Can anyone explain to me what I'm doing wrong?

Thanks!
Click to expand...
Click to expand...

If I understand correctly what you're trying to do, it won't work the
way you're trying to do it. The fact that you have those recordsets
open isn't going to make them or their contents known to your append
query. And incidentally, this line:
Set rstQualData = dbs.OpenRecordset(strQualData, dbOpenDynaset)
Click to expand...

doesn't really make sense, since you're not going to get a recordset
back from an append query.

But maybe you can use the IN clause in your append query to identify
the databases where your source tables reside. Something like this:

strQualData = _
"INSERT INTO BHProductionReport " & _

' ... field list goes here ...

"SELECT Mailstream.MailstreamNumber,

' ... bulk of SQL string goes here ...

"FROM (Mailstream IN '" & strFL & "' " & _
"INNER JOIN CmbClientData IN '" & frm!DataFilePath & "' " & _
"ON Mailstream.MailstreamNumber = CmbClientData.Stream) " & _
"INNER JOIN AnalysisResults IN '" & frm!DataFilePath & "' " & _
"ON Mailstream.MailstreamNumber = AnalysisResults.Streams " & _
"GROUP BY " & _

' ... remainder of SQL string goes here ....


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Nick DiPaolo said:
I'm also dabbling in ADO vs DAO, however I'm not convinced it's
better...
Click to expand...

For working with Jet databases (MDB files), DAO is better.
I see your suggestion of:

_

I'll give it a try.
Click to expand...

I'm not promising that I have the syntax exactly right. Check it out in
your Jet SQL help file.
 
I'll try to stick with DAO, however, if I convert my BE to SQL Server at a
later time, will I have to re-do the code in ADO?

Just for posterities sake, I think the format for the external databases is
as follows:

FROM strFL.Mailstream INNER JOIN frm!DataFilePath.cmbClientData

i.e. FROM [C:\TEMP\DATA.MDB].Mailstream...

I've done some preliminary testing with a quickie contrived set of databases
and it appears to work. I'll test in the real app later today.

Thanks again for your help Dirk!
 
Nick DiPaolo said:
I'll try to stick with DAO, however, if I convert my BE to SQL Server
at a later time, will I have to re-do the code in ADO?
Click to expand...

I don't see why you would. You just have to supply the connect string
in the query, either in the qualifier you show below or in the IN clause
as I proposed.
Just for posterities sake, I think the format for the external
databases is as follows:

FROM strFL.Mailstream INNER JOIN frm!DataFilePath.cmbClientData

i.e. FROM [C:\TEMP\DATA.MDB].Mailstream...
Click to expand...

That's one valid format. The IN syntax I proposed should also work for
a Jet database, or (if you need to provide a connect string), this:

SELECT * FROM TableName IN "" "<connect string here";
I've done some preliminary testing with a quickie contrived set of
databases and it appears to work. I'll test in the real app later
today.
Click to expand...

Very good.
Thanks again for your help Dirk!
Click to expand...

You're welcome.
 
Back
Top