Moving Access DB to MS-SQL Server

[Guest]

We have an existing Access DB which pulls data from an MS SQL DB and creates
desktop reports. We are redesigning the SQL DB and therefore need to change
the Access queries and move them to the MS SQL server. What type of facility
exists to enable me to understand the Access queries and reports in terms of
the data sources and the SQL behind the scenes?

 

[Arvin Meyer [MVP]]

Although performance may sometimes be improved by running a query on the
Server, it isn't always necessary to use views on the server to replace the
Access queries. As long as you are not using functions within the queries,
many of the queries will be directly usable in SQL-Server. If you've checked
the use ANSI 92 box for queries under Tools ... Options, they all should be.

By looking at the graphical query grid (QBE) you should be able to figure
out every query.
--
Arvin Meyer, MCP, MVP
Free MS-Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Guest]

My issue isn't the performance (although I expect a vastly improved
performance). It is simpler. I don't know how to dissect the rpt itself and
determine what queries lie behind it. I have tried using "analyze", but it
doesn't give me that information. Since we are changing the base tables, we
need to change the queries that access the data in those tables to pull in
data from the newly modeled tables. Thank you in advance for your help on
this.

 

[Arvin Meyer [MVP]]

If you expect vastly improved performance, you may be in for a big
disappointment. Depending upon configuration, numbers of users, quality of
network and hardware, and skill of the developer, performance can vary
significantly with either database engine. We get excellent performance with
slightly over 50 users on a hundred MB JET database.

To view a report's source, simply open it in design view. Open the property
sheet and click on the data tab and look at the recordsource. If you click
on the ellipses (...) at the end of the recordsource line, it will take you
directly into the underlying query or table.
--
Arvin Meyer, MCP, MVP
Free MS-Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com