UNION query MS Jet database engine cannot find the input table or query. (1 Viewer)

casey

Registered User.
Local time
Today, 12:21
Joined
Dec 5, 2000
Messages
448
Hello All,

I'm trying to run a UNION query that joins five queries through a MS WorkSpace into a DAO.recordset in VB. I'm pulling the data from a SQL Server Database through VB in Access. I'm attempting to open a recordset with a query passed to it as a string. The query is below. For some reason, I'm receiving a message: "MS Jet database engine cannot find the input table or query. Runtime Error 3078".

Here's what's puzzling. When I run a single query without any UNION statement, the code finds the table and runs fine without error, but anytime I join two or more queries with a UNION statement in the VB, it gives me the error.

I've executed the same UNION query in both Access Query Builder and SQL Server's Query Analyzer and they work fine in both environments. It's only when I call the query from a DAO.Recodset with VB that it causes this problem. The following is a sample of the UNION query joining two of the five queries. Does anyone have any idea what could be the problem? The following query executes in about 5 seconds so I don't think there's a "time-out" issue. I'm thinking that the UNION statement may be the culprit. Maybe there's another way to approach joining these separate queries? Any help would be most appreciated. Thanks.

SELECT SalespersonID, Sum([SlsPrice]-[RtnPrice]-[SlsDiscnt]+[RtnDiscnt]) AS fldPrice FROM MyTable WHERE (((Source)='d') AND ((DistrictID)='01') AND ((CategoryID) = 'HCPROD') AND ((BrandID)<>'CSS')) AND (((BrandID)<>'1356')) AND (((BrandID)<>'1400')) AND (((BrandID)<>'1551')) AND (((BrandID)<>'555')) AND (((BrandID)<>'66'))
AND (TransDate >= 07/01/2005) AND (TransDate <= 07/31/2005) GROUP BY SalespersonID
UNION
SELECT SalespersonID, Sum([SlsPrice]-[RtnPrice]-[SlsDiscnt]+[RtnDiscnt]) AS fldPrice FROM MyTable WHERE (((Source)='d') AND ((DistrictID)='01') AND ((ProductID) = '0029800')) AND (TransDate >= 07/01/2005) AND (TransDate <= 07/31/2005) GROUP BY SalespersonID

Set wrkJet = CreateWorkspace("", "pw", "", dbUseJet)
Set db = wrkJet.OpenDatabase("DW", _
dbDriverNoPrompt, True, _
"ODBC;DATABASE=DW;DSN=DW2")
'Set rs1 = db.OpenRecordset(strSQL)
 

FlyerMike

Registered User.
Local time
Today, 08:21
Joined
Oct 26, 2005
Messages
47
What happens when you use the following...

Set db = Currentdb
Set rs1 = db.OpenRecordset(strSQL)

Still a problem with the UNION query?
 

casey

Registered User.
Local time
Today, 12:21
Joined
Dec 5, 2000
Messages
448
Actually, that works in both Query Builder if the table exits within the currentdb and MS-SQL Query Analyzer. Unfortunately, I'm trying to call a table that's not in the currentdb. That is why I'm using the Workspace. All queries work in this way so far, but when I try to use the UNION statement to join these queries, I get the error. Does that help to explain?
 

FlyerMike

Registered User.
Local time
Today, 08:21
Joined
Oct 26, 2005
Messages
47
I think the problem might be with the connection to the table and not the UNION query per se.
 

casey

Registered User.
Local time
Today, 12:21
Joined
Dec 5, 2000
Messages
448
You could be right, but what gets me is that, everything works with this connection except when I use the UNION statement. When I just call one query at a time, the VB returns the recordset without error??? So I'm thinking the UNION statement is causing some conflict. Perhaps some of the connection properties need to be adjusted for this to work? Let me know if you have any other suggestions. Thanks.
 

FlyerMike

Registered User.
Local time
Today, 08:21
Joined
Oct 26, 2005
Messages
47
Let's examine this for a second.....

Given your existing code for connecting to the table, you can create a recordset. No error is thrown.

Can you open the recordset of the single connected table, traverse a record or two, do a debug.print of a few fields? No errors?
 
Last edited:

casey

Registered User.
Local time
Today, 12:21
Joined
Dec 5, 2000
Messages
448
Given your existing code for connecting to the table, you can create a recordset. No error is thrown.

Can you open the recordset of the single connected table, traverse a record or two, do a debug.print of a few fields? No errors?

Yes. I'm actually currently processing about 20,000 records this way per selected timeframe. I've created another sub to create the UNION query and upon trying to reconnect, the error is thrown.

Can you see a problem with the connection?
 

FlyerMike

Registered User.
Local time
Today, 08:21
Joined
Oct 26, 2005
Messages
47
Can you describe the code in a little more detail?

Sub1 connects to the table, does something with 20,000 records....
Sub2 tries to do a UNION query involving the same table, with a new recordset and new connection.

Perhaps Sub1 is conflicting with Sub2? After Sub1 is finished, it disconnects the table? The connection is terminated?

What happens if

Code:
Sub1()
  Connect to table
  Process the table as usual
  Call Sub2 from within Sub1, and pass the Recordset by reference.  
  You wouldn't have to to disconnet the table, and try to reconnect to it.
 

casey

Registered User.
Local time
Today, 12:21
Joined
Dec 5, 2000
Messages
448
Sorry for not responding sooner. The following is the basic logic.

SubMain
set wrkJet = Workspace
set db = wrkJet.DbConnection
strQuery = SQL statement on non-local table
set rs = Open.Recordset(strQuery)
Calls Sub1(calculates approx 20,000 records using connection and returns to SubMain)

'DbConnection does not change
strQuery = SQL UNION on non-local table
set rs = Open.Recordset(strQuery) ******triggers error message*****

Any ideas? Thanks for your time.
 

jwlankford

New member
Local time
Today, 05:21
Joined
Sep 18, 2008
Messages
8
Try putting a ";" at the end of each SQL statement. When parsing SQL, you have to let the system know you will are sending multiple SQL statements. Even with UNION, MS Access considers the SELECTs two seperate SQL's.

SELECT SalespersonID, Sum([SlsPrice]-[RtnPrice]-[SlsDiscnt]+[RtnDiscnt]) AS fldPrice FROM MyTable WHERE (((Source)='d') AND ((DistrictID)='01') AND ((CategoryID) = 'HCPROD') AND ((BrandID)<>'CSS')) AND (((BrandID)<>'1356')) AND (((BrandID)<>'1400')) AND (((BrandID)<>'1551')) AND (((BrandID)<>'555')) AND (((BrandID)<>'66'))
AND (TransDate >= 07/01/2005) AND (TransDate <= 07/31/2005) GROUP BY SalespersonID;
UNION
SELECT SalespersonID, Sum([SlsPrice]-[RtnPrice]-[SlsDiscnt]+[RtnDiscnt]) AS fldPrice FROM MyTable WHERE (((Source)='d') AND ((DistrictID)='01') AND ((ProductID) = '0029800')) AND (TransDate >= 07/01/2005) AND (TransDate <= 07/31/2005) GROUP BY SalespersonID;
 

Users who are viewing this thread

Top Bottom