Jonathan said:
Hi, I have a database that contains nested queries and I import these queries.
That is, the database contains queries that have queries as data source
(don't mean to dumb it down, just want the situation to be explicitly clear).
In the top query the source queries are joined on related fields.
The problem is that when I import (using DoCmd.TransferDatabase acImport)
all the queries involved, the joins are dropped. That is the source queries
still exist in the top query. However the joins between source queries no
longer exist.
Why is that? More importantly how can I avoid this happening?
I was unable to reproduce your problem. Perhaps some joined Table or
Query was missing, so Access blasted the links into oblivion; this would
be normal behavior.
If you want to try using my code, Tables, and Queries, maybe that will
shed light on your situation. (I omitted my usual error-checking code
from the VBA.) These procedures worked both when expressed as a Macro
and when defined using the following code:
'------------------------------------------------------------
' Import_Queries imports the specified Queries
' and the Tables on which they depend
'
'------------------------------------------------------------
Public Function ImportQueries()
Const strSourceFile As String = _
"c:\vrj\AccessHelp\db5K13a_Orders.mdb"
' Import [Q_Reasons]
Import1 acQuery, "Q_Reasons"
' Import [Q_Shipped Stuff]
Import1 acQuery, "Q_Shipped Stuff"
' Import [Q_Shipped?]
Import1 acQuery, "Q_Shipped?"
' Import [tblOrder]
Import1 acTable, "tblOrder"
' Import [tblOrderShip]
Import1 acTable, "tblOrderShip"
End Function 'ImportQueries()
'------------------------------------------------------------
' Import1() imports the specified object
'
'------------------------------------------------------------
Public Sub Import1( _
ByVal ObjectType As AcObjectType, _
ByVal ObjectName As String)
'ByVal ObjectType As AcObjectType 'acQuery _
or acTable
'ByVal ObjectName As String 'Name of Table _
or Query to import
Const strSourceFile As String = _
"c:\vrj\AccessHelp\db5K13a_Orders.mdb"
' Import [Q_Reasons]
DoCmd.TransferDatabase acImport, _
"Microsoft Access", _
strSourceFile, _
ObjectType, ObjectName, _
ObjectName, False
End Sub 'ImportQueries()
The (complete) contents of the two Tables are shown here:
[tblOrderShip] Table Datasheet View:
OrderShipID OrderID ShipDate ReasonNotShipped
----------- ----------- ---------- ----------------
-418235218 1945178752 Fungus
402604161 -1152314661 11/10/2005
[tblOrder] Table Datasheet View:
OrderID Status
----------- -------
-1152314661 Shipped
1945178752 Stalled
The three imported Queries are defined as follows; notice that
[Q_Shipped Stuff] includes a JOIN on [Q_Shipped?] and [Q_Reasons].
[Q_Reasons] SQL:
SELECT TblOrderShip.OrderID,
TblOrderShip.ReasonNotShipped
FROM TblOrderShip
ORDER BY TblOrderShip.ReasonNotShipped;
[Q_Shipped?] SQL:
SELECT TblOrder.OrderID, TblOrder.Status,
Count(TblOrderShip.ShipDate) AS CountOfShipDate,
[CountOfShipDate]>0 AS [IsShipped?]
FROM TblOrder INNER JOIN TblOrderShip
ON TblOrder.OrderID = TblOrderShip.OrderID
GROUP BY TblOrder.OrderID, TblOrder.Status
HAVING (((Count(TblOrderShip.ShipDate)) Is Not Null))
ORDER BY TblOrder.OrderID;
[Q_Shipped Stuff] SQL:
SELECT [Q_Shipped?].OrderID, [Q_Shipped?].Status,
Q_Reasons.ReasonNotShipped
FROM [Q_Shipped?] INNER JOIN Q_Reasons
ON [Q_Shipped?].OrderID = Q_Reasons.OrderID
WHERE ((([Q_Shipped?].[IsShipped?])=No));
[Q_Shipped Stuff] Query Datasheet View:
OrderID Status ReasonNotShipped
---------- ------- ----------------
1945178752 Stalled Fungus
-- Vincent Johns <
[email protected]>
Please feel free to quote anything I say here.
