Strange query help

[Amy Blankenship]

First, let me say that this is difficult even for me to follow, so hopefully
I can explain it right. My client has a program that he wants users to be
able to run on only one computer at a time, but they need to be able to
transfer their records. The records reside in a local mdb file on the
machine, and users will be able to select one of their removable drives to
store their records on. So, we're able to copy the records database onto a
floppy, thumb, or whatever. So far so good.

Now, we need to get the records transferred to a new machine, or back to an
existing machine. However, we can't just plonk that database back in the
records directory, because the database may contain more than one user, and
pasting the database in on top of an existing database might destroy the
records of the other users that could be contained in the database. So, I'm
trying to use INSERT INTO MyTable IN otherDatabase to try to get all of the
data, including original record ID's and time stamps, into the database on
the target machine.

The problem is that the Session table contains information on many levels of
the program, distinguished by their ID and LevelID, so the Program is level
1, Category is level 2, Topic is level 3, etc. So the ID is the ID from the
Program table, the ID from the category table, etc. I already have
parameter queries that allow me to figure out all the sessions that relate
to a given program. Unfortunately, I can't find a way to pass the path to
the database into a query as a parameter, so I can't make use of the
existing parameter queries. This has me deconstructing a working chain and
trying to make it work again, as a really, really long query.

So the existing chain is:

Join of all the levels of the Program (ProgramObjectTree)
Union query to place the ObjectID's and LevelID's in just 2 columns so the
program can get a handle on what belongs to it (ProgramObjects)
Join between that query and the sessions table to determine what sessions
belong to the program (ProgramObjectSessions)

Additionally, I need to narrow the sessions down to just the sessions for
the user in question.

Now, ultimately this needs to work from outside Access, but right now I'm
working on the syntax. Access seems to be coughing on the Union Operation
in the subqueries. For now, my query looks like this:

INSERT INTO SESSION IN 'C:\Documents and Settings\Amy\Application
Data\Pegasus\Records.mdb' SELECT Session.SessionID, Session.StudentID,
Session.LevelID, Session.ObjectID, Session.StartTime, Session.EndTime,
Session.MarkedCorrect FROM Session INNER JOIN ((SELECT DISTINCT ProgramID AS
ObjectID, 1 AS LevelID FROM ProgramObjectTree) UNION (SELECT DISTINCT
CategoryID AS ObjectID, 2 AS LevelID FROM ProgramObjectTree) UNION (SELECT
DISTINCT TopicID AS ObjectID, 3 AS LevelID FROM ProgramObjectTree) UNION ALL
(SELECT DISTINCT PageID AS ObjectID, 4 AS LevelID FROM ProgramObjectTree)
UNION (SELECT DISTINCT QuestionID AS ObjectID, 5 AS LevelID FROM
ProgramObjectTree)) AS ProgramObjects ON (Session.ObjectID =
ProgramObjects.ObjectID) AND (Session.LevelID = ProgramObjects.LevelID)
WHERE Session.StudentID = (SELECT UserID FROM Users WHERE UserName =
'JessicaSimposon') AND Session.SessionID NOT IN (SELECT SessionID FROM
Session IN 'C:\Documents and Settings\Amy\Application
Data\Pegasus\Records.mdb');

Can anyone give any advice as to how to make Access see those Union
operators as just part of the inner query?

Thanks;

Amy

 

[John W. Vinson/MVP]

First, let me say that this is difficult even for me to follow, so
hopefully I can explain it right. My client has a program that he wants
users to be able to run on only one computer at a time, but they need to
be able to transfer their records.

Just a suggestion: rather than "rolling your own", you should consider using
Access Replication for this purpose. That's exactly what it's designed to
do, and it does a good job - although it is complicated and non-intuitive to
set up! Search at support.microsoft.com for the Replication Whitepaper and
study and apply it carefully; there's also a
microsoft.public.access.replication newsgroup with some of the real
replication experts as regulars.

 

[Amy Blankenship]

Replication won't work for this for a number of reasons, first of which is
that you can't get the replication manager.

 

[JethroUK©]

i might have missed something - but if you only want one user to use one
database at any one time - why not just pass the same database around ?

put it on a pendrive and plug it in as/where ness

Replication won't work for this for a number of reasons, first of which is
that you can't get the replication manager.

 

[Amy Blankenship]

I suspect you missed this sentence:

"Now, we need to get the records transferred to a new machine, or back to an
existing machine. However, we can't just plonk that database back in the
records directory, because the database may contain more than one user, and
pasting the database in on top of an existing database might destroy the
records of the other users that could be contained in the database."

i might have missed something - but if you only want one user to use one
database at any one time - why not just pass the same database around ?

put it on a pendrive and plug it in as/where ness