Access and SQL server

[Mark CHP IMD]

Has anyone moved the back end (if you’re using client/server) of an Access
database to SQL Server? I am looking to find out how much coding is involved
in creating the stored procedures and other functionality that’s required in
SQL Server. The database is about 15 megs front end, 3 megs backend (no
data). The idea is to ‘thin’ out the front end and enable less demand on the
bandwidth. What is the process to move to SQL server and away from the Access
mdb backend?
Mark

 

[Ofer]

First, it is possible to change the back end to SQL server without the need
to change the code to SP.
I have 70 users on a FE in Access and the BE in SQL server, when we changed
the BE to SQL we moved only the heavy functions to SP, and alot of the
queries to pass through queries.
It is very hard to answer your question without seeing how many functions
you have and how complicated they are, when we moved we had 10 MB of FE, and
it took around 4 months, but as I said it can't apply to your application.
As I said, we have 70 users, with remote stations, and it works great, I
never heard any one complain about the system mean to slow.

 

[Dirk Goldgar]

Has anyone moved the back end (if you're using client/server) of an
Access database to SQL Server? I am looking to find out how much
coding is involved in creating the stored procedures and other
functionality that's required in SQL Server. The database is about 15
megs front end, 3 megs backend (no data). The idea is to 'thin' out
the front end and enable less demand on the bandwidth. What is the
process to move to SQL server and away from the Access mdb backend?

You can use the Upsizing Wizard to make a start on the job, but what
remains can be a big or a small job, depending on how your front-end is
built. Make sure, before you begin the upsizing process, that all your
tables have primary keys. If you use a lot of VBA functions in your
queries, whether those built-in functions or user-written ones, then
you're going to have to do a lot of work in SQL Server to match that
functionality, or else Access will have to pull all the data from the
tables across the network to perform the functions locally, and that
will lose any gains you might have hoped to gain by upsizing. On the
other hand, where your queries are simple and don't use VBA functions,
you may find that Access sends the query straight to SQL Server for
execution and just brings back the result set.

In addition to the basic changes your application may require to make it
work acceptably with a SQL Server back-end, if you want to reduce
network traffic you'll probably need to redesign some forms and
operations so that they fall more in line with the client/server way of
doing things. For example, where before you might have had a form with
a recordsource that brought back all records from a table, you'll
probably want to prompt the user first for which record she wants to
see, and then set the form's recordsource to bring back just that
record. The overall design is going to be a balance between providing
the user-interface functionality that is convenient to your users, and
reducing the demands on the network. You'll find the use of SQL
Profiler (which comes with most but not all installations of SQL Server,
IIRC) to be invaluable in tuning your application.

The whole upsizing process can be simple or hard, depending on the
current design of your application.

 

[Mark CHP IMD]

Dirk,
I do use VBA functions in my queries, and also use ‘DoCMD.RunSQL’ and load
the MS Jet often in my procedures. I understand the issue with a forms record
source not loading until the user requests an edit or new record. I assume my
procedures all have to be rewritten as stored procedures or particular views.
That’s going to be a lot of work! How does one call a procedure if it’s
placed on the SQL server as a stored procedure? Currently, I call a procedure
and pass along several variables. An example would be
=ProcessMaster(Forms(0),[Form],"Check Field Values"). I was thinking the
calls remain the same. Using the Jet is also a no no, this should be
accomplished with Transact SQL?
I have never used the Upsizing Wizard. Thanks for the info, I will explore
the benefits of that wizard.
Mark

 

[Arvin Meyer [MVP]]

I moved a very complex Access backend to SQL-Server 2000 in about 6 weeks. I
did not rewrite all the queries as view or Stored Procs. The 380 MB of data
were moved in 1 weekend, using BCP and DTS.

I've moved less complex apps in a few days, again oply rewriting a few major
queries as Stored Procs. Using SQL-Server BOL, study up on DTS to move the
data itself.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Dirk Goldgar]

Dirk,
I do use VBA functions in my queries, and also use 'DoCMD.RunSQL' and
load the MS Jet often in my procedures. I understand the issue with a
forms record source not loading until the user requests an edit or
new record. I assume my procedures all have to be rewritten as stored
procedures or particular views. That's going to be a lot of work!

Could be. As I said, it depends on what's in them.

How
does one call a procedure if it's placed on the SQL server as a
stored procedure? Currently, I call a procedure and pass along
several variables. An example would be
=ProcessMaster(Forms(0),[Form],"Check Field Values"). I was thinking
the calls remain the same.

A stored procedure that has no parameters can be invoked via a simple
pass-through query. One with parameters will usually require that you
use an ADO Command object to represent the procedure, fill in the
parameters, and execute it.

Using the Jet is also a no no, this should
be accomplished with Transact SQL?

I don't know what you mean by "Using Jet is ... a no no". Jet works
fine for basic operations on linked SQL Server tables. Views, stored
procedures, and user-defined functions that you define in the SQL Server
database will have to use T-SQL, naturally. And pass-through queries,
when you use them, will be written in T-SQL. But you can still do a lot
with Jet SQL operating on linked tables (or views linked as tables).