Performing a Query from SQL Server Against Access?

[Joe_A]

Hi,

We have a legacy analysis application in Microsoft Access 2003 (X) that
reads a large subset of a second MS Access 2003 database (Y) into temporary
tables and performs certain operations on the temporary data set. To improve
performance, we have began to port the application (X) to C# on SQL Server
2005 64bit edition. Unfortunately, the MS Access 2003 database (Y) that
serves as the original source for the data has to remain in MS Access 2003
and is updated via a feed throughout the day. Our analysis application (X)
needs to continue importing its initial data set from Access everytime a user
initiates an analysis request.

What are the various methods we could consider to execute import of various
queries from MS Access 2003 to SQL Server 2005 64bit, given the large size of
data being imported on demand?

Thank you

 

[James A. Fortune]

Hi,

We have a legacy analysis application in Microsoft Access 2003 (X) that
reads a large subset of a second MS Access 2003 database (Y) into temporary
tables and performs certain operations on the temporary data set. To improve
performance, we have began to port the application (X) to C# on SQL Server
2005 64bit edition. Unfortunately, the MS Access 2003 database (Y) that
serves as the original source for the data has to remain in MS Access 2003
and is updated via a feed throughout the day. Our analysis application (X)
needs to continue importing its initial data set from Access everytime a user
initiates an analysis request.

What are the various methods we could consider to execute import of various
queries from MS Access 2003 to SQL Server 2005 64bit, given the large size of
data being imported on demand?

Thank you

X - A2K3 app with temp tables that does analysis on Y
Y - A2K3 app
C# - Port of X to 64 bit SQL Server

Here's a shot in the dark. Whenever the data in Y changes, have Y also
change the data that mirrors it in SQL Server using ADO. Then no update
is needed and the C# program can use SQL Server directly to do the
analysis. The mirroring methods will differ depending on whether you
are using bound or unbound forms. You can also look into DTS:

http://en.wikipedia.org/wiki/Data_Transformation_Services

James A. Fortune
(e-mail address removed)