Attached SQL Server Table 
Author Message
 Attached SQL Server Table

Any help or advice on this problem much appreciated.

I've got an mdb database on a network server that has several native Access
tables and one very large attached SQL Server(6.0) table residing on the
same server.

I need to generate a table who's records will come from the SQL Server
table based on 1) whether a date field falls in a certain range, and 2)
whether a character ID field is in a subset of one of the Access tables.  

In other words:

"Select SQLServertbl.* from SQLServertbl where (datefield between
'#mm/dd/yy#' and '#mm/dd/yy#') OR (charidfield in (Select distinct charid
from Accesstbl))"

An ugly heterogeneous join that is causing Jet to bring over 30-50 meg of
data to the local machine in order to process  the query - not to mention
it takes forever.

What's my best approach to get this data from the attached table when the
query depends on the results of a Jet database query on an Access table?
Am I better off using a cursor to step thru the SQL Server table testing
the field values individually?

Somehow I've got to, if nothing else, get the majority of the processing
moved back to the server as opposed to the client.

Thanks -

J.D. Bell
Bell Interactive Development



Sat, 10 Jul 1999 03:00:00 GMT  
 Attached SQL Server Table



Quote:
>Any help or advice on this problem much appreciated.
>I've got an mdb database on a network server that has several native Access
>tables and one very large attached SQL Server(6.0) table residing on the
>same server.
>I need to generate a table who's records will come from the SQL Server
>table based on 1) whether a date field falls in a certain range, and 2)
>whether a character ID field is in a subset of one of the Access tables.  
>In other words:
>"Select SQLServertbl.* from SQLServertbl where (datefield between
>'#mm/dd/yy#' and '#mm/dd/yy#') OR (charidfield in (Select distinct charid
>from Accesstbl))"
[snip]
>Somehow I've got to, if nothing else, get the majority of the processing
>moved back to the server as opposed to the client.

How about writing a stored procedure on the server, which writes the
data to a temporary table, and then reading the temp-table?

Preferably, I'd include the user-/machine-name in the temp-table,
enabling several concurrent users to use the same table.

Hope this helps,

Trond Solberg
Systems Consultant
Mandatum as
----

http://www.mandatum.no



Sun, 11 Jul 1999 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Updating attached SQL Server Tables

2. Open attached SQL Server table w/out prompting

3. Attached SQL Server Table

4. Attaching to SQL server tables

5. Acc2000: Access tables attached to SQL-Server won't update

6. SQL Server with Access Attach table?

7. Attached table and not attached table

8. Cannot delete rows in SQL attached table

9. Attaching SQL tables questions??

10. Attach MS SQL tables via ODBC

11. Attaching SQL tables questions??

12. Attached tables, identifying users and un-attaching

 

 
Powered by phpBB® Forum Software