DoCmd.OutputTo acOutputQuery/Copying query slow

  • Thread starter Thread starter Crombie
  • Start date Start date
C

Crombie

I have a current issue with a linked ODBC table from Access 2000 to SQL
Server. Basically in some queries whether they be complex or simple the
query results will not export using acOutputQuery or copy in under
several minutes. The "fix" I have found for this is to change the query
into a Union query from a simple Select query. So:

Select dbo_UserID, dbo_LastName, dbo_FirstName, dbo_SchoolYear,
dbo_SchoolTerm
FROM
SchoolStudents
WHERE dbo_SchoolTerm=[Forms]![frm_AwesomeFrontEnd]![drpTerm]
AND dbo_SchoolYear=[Forms]![frm_AwesomeFrontEnd]![drpYear];

becomes:

Select dbo_UserID, dbo_LastName, dbo_FirstName, dbo_SchoolYear,
dbo_SchoolTerm
FROM
SchoolStudents
WHERE dbo_SchoolTerm=[Forms]![frm_AwesomeFrontEnd]![drpTerm]
AND dbo_SchoolYear=[Forms]![frm_AwesomeFrontEnd]![drpYear]
UNION
Select dbo_UserID, dbo_LastName, dbo_FirstName, dbo_SchoolYear,
dbo_SchoolTerm
FROM
SchoolStudents
WHERE dbo_SchoolTerm=[Forms]![frm_AwesomeFrontEnd]![drpTerm]
AND dbo_SchoolYear=[Forms]![frm_AwesomeFrontEnd]![drpYear];

The data provider is at a remote location, and is not sure why this is
happening. The table is indexed, and he has refreshed it once for me
already. The odd thing is this also happens in other tables of ours, and
even bringing out the most basic information I cannot copy or export the
data with any speed. The interesting thing is the "Make-Table" query type
seems to run quickly in all of these instances, but we would prefer not
to have any of the data store locally at our location if at all possible.
Is there a technical reason anyone can think of why this happens?
Something I/my data provider could tweak? More information I can gather
to assist you further?
 
I have a current issue with a linked ODBC table from Access 2000 to
SQL Server. Basically in some queries whether they be complex or
simple the query results will not export using acOutputQuery or copy
in under several minutes. The "fix" I have found for this is to change
the query into a Union query from a simple Select query. So:

Select dbo_UserID, dbo_LastName, dbo_FirstName, dbo_SchoolYear,
dbo_SchoolTerm
FROM
SchoolStudents
WHERE dbo_SchoolTerm=[Forms]![frm_AwesomeFrontEnd]![drpTerm]
AND dbo_SchoolYear=[Forms]![frm_AwesomeFrontEnd]![drpYear];

becomes:

Select dbo_UserID, dbo_LastName, dbo_FirstName, dbo_SchoolYear,
dbo_SchoolTerm
FROM
SchoolStudents
WHERE dbo_SchoolTerm=[Forms]![frm_AwesomeFrontEnd]![drpTerm]
AND dbo_SchoolYear=[Forms]![frm_AwesomeFrontEnd]![drpYear]
UNION
Select dbo_UserID, dbo_LastName, dbo_FirstName, dbo_SchoolYear,
dbo_SchoolTerm
FROM
SchoolStudents
WHERE dbo_SchoolTerm=[Forms]![frm_AwesomeFrontEnd]![drpTerm]
AND dbo_SchoolYear=[Forms]![frm_AwesomeFrontEnd]![drpYear];

The data provider is at a remote location, and is not sure why this is
happening. The table is indexed, and he has refreshed it once for me
already. The odd thing is this also happens in other tables of ours,
and even bringing out the most basic information I cannot copy or
export the data with any speed. The interesting thing is the
"Make-Table" query type seems to run quickly in all of these
instances, but we would prefer not to have any of the data store
locally at our location if at all possible. Is there a technical
reason anyone can think of why this happens? Something I/my data
provider could tweak? More information I can gather to assist you
further?
Click to expand...

Anyone?

Dave R
 
I have a current issue with a linked ODBC table from Access 2000 to
SQL Server. Basically in some queries whether they be complex or
simple the query results will not export using acOutputQuery or copy
in under several minutes. The "fix" I have found for this is to change
the query into a Union query from a simple Select query. So:

Select dbo_UserID, dbo_LastName, dbo_FirstName, dbo_SchoolYear,
dbo_SchoolTerm
FROM
SchoolStudents
WHERE dbo_SchoolTerm=[Forms]![frm_AwesomeFrontEnd]![drpTerm]
AND dbo_SchoolYear=[Forms]![frm_AwesomeFrontEnd]![drpYear];

becomes:

Select dbo_UserID, dbo_LastName, dbo_FirstName, dbo_SchoolYear,
dbo_SchoolTerm
FROM
SchoolStudents
WHERE dbo_SchoolTerm=[Forms]![frm_AwesomeFrontEnd]![drpTerm]
AND dbo_SchoolYear=[Forms]![frm_AwesomeFrontEnd]![drpYear]
UNION
Select dbo_UserID, dbo_LastName, dbo_FirstName, dbo_SchoolYear,
dbo_SchoolTerm
FROM
SchoolStudents
WHERE dbo_SchoolTerm=[Forms]![frm_AwesomeFrontEnd]![drpTerm]
AND dbo_SchoolYear=[Forms]![frm_AwesomeFrontEnd]![drpYear];

The data provider is at a remote location, and is not sure why this is
happening. The table is indexed, and he has refreshed it once for me
already. The odd thing is this also happens in other tables of ours,
and even bringing out the most basic information I cannot copy or
export the data with any speed. The interesting thing is the
"Make-Table" query type seems to run quickly in all of these
instances, but we would prefer not to have any of the data store
locally at our location if at all possible. Is there a technical
reason anyone can think of why this happens? Something I/my data
provider could tweak? More information I can gather to assist you
further?
Click to expand...

Anyone?

Dave R
Click to expand...

Bueller?
 
I have a current issue with a linked ODBC table from Access 2000 to
SQL Server. Basically in some queries whether they be complex or
simple the query results will not export using acOutputQuery or copy
in under several minutes. The "fix" I have found for this is to
change the query into a Union query from a simple Select query. So:

Select dbo_UserID, dbo_LastName, dbo_FirstName, dbo_SchoolYear,
dbo_SchoolTerm
FROM
SchoolStudents
WHERE dbo_SchoolTerm=[Forms]![frm_AwesomeFrontEnd]![drpTerm]
AND dbo_SchoolYear=[Forms]![frm_AwesomeFrontEnd]![drpYear];

becomes:

Select dbo_UserID, dbo_LastName, dbo_FirstName, dbo_SchoolYear,
dbo_SchoolTerm
FROM
SchoolStudents
WHERE dbo_SchoolTerm=[Forms]![frm_AwesomeFrontEnd]![drpTerm]
AND dbo_SchoolYear=[Forms]![frm_AwesomeFrontEnd]![drpYear]
UNION
Select dbo_UserID, dbo_LastName, dbo_FirstName, dbo_SchoolYear,
dbo_SchoolTerm
FROM
SchoolStudents
WHERE dbo_SchoolTerm=[Forms]![frm_AwesomeFrontEnd]![drpTerm]
AND dbo_SchoolYear=[Forms]![frm_AwesomeFrontEnd]![drpYear];

The data provider is at a remote location, and is not sure why this
is happening. The table is indexed, and he has refreshed it once for
me already. The odd thing is this also happens in other tables of
ours, and even bringing out the most basic information I cannot copy
or export the data with any speed. The interesting thing is the
"Make-Table" query type seems to run quickly in all of these
instances, but we would prefer not to have any of the data store
locally at our location if at all possible. Is there a technical
reason anyone can think of why this happens? Something I/my data
provider could tweak? More information I can gather to assist you
further?
Click to expand...

Anyone?

Dave R
Click to expand...

Bueller?
Click to expand...

So I take it this is "variable unknown" type of question? I mean don't
all jump out at once now.

Dave
 
Back
Top