Upsizing an existing .mdb that uses DAO extensively

[robert demo via AccessMonster.com]

I have an Access application that consists of a front end and backend. The
backend is Jet. The front end uses DAO exclusively and you can assume that
there are no stored queries (I originally wanted to use just VB but was
forced to go Access without realizing some of the benefits of Access's
handling of queries; too late now). However, for security the front-end does
use RWOP queries.


What's the best way for me to convert my application to using SQL Server as
the backend. Will I have to get rid of the RWOP queries. Also, the front-
end does some very complicated processing and has some very complicated SQL
statements so I do not want to have to rewrite it if possible.

Also, I must confess that I have been lazy about defining keys on many tables
of lesser importance.

Thanks for any help.

 

[Brendan Reynolds]

What is 'best' rather depends on what your goals and priorities are, but the
approach that will require the fewest modifications to your existing
application is to use ODBC to link to the SQL Server tables. You will,
however, need to add a unique index or constraint to any tables that lack
them if you want users to be able to update data in those tables. Any
ODBC-linked table without a unique index or constraint is read-only.

 

[robert demo via AccessMonster.com]

So, let me see if I understand.

1) Since I want to just try this first without spending a lot of money for
SQL Server can I try this first with MSDE. can I use the Upsizing Wizard to
migrate my tables over to an MSDE database. Am I correct in assuming that if
I get my application to work using MSDE, then it will automatically work if
we move the MSDE to SQL Server.

2) On the code side, I will need to modify any Access SQL that is not
compatible with SQL Server SQL. For example instead of '&' for concatenation
I need to use '+'

3) Also, I generally use the following to create recordsets.

Set wsR = DBEngine.Workspaces(0)

'Open the back-end Database
Set dbR = wsR.OpenDatabase(CurrentProject.FullName)

'Open the Recordset on the Database
Set rsR = dbR.OpenRecordset(strSQLR)

How will I need to change this in order to implement it with SQL Server (MSDE)
?

Thanks.

What is 'best' rather depends on what your goals and priorities are, but the
approach that will require the fewest modifications to your existing
application is to use ODBC to link to the SQL Server tables. You will,
however, need to add a unique index or constraint to any tables that lack
them if you want users to be able to update data in those tables. Any
ODBC-linked table without a unique index or constraint is read-only.

I have an Access application that consists of a front end and backend. The
backend is Jet. The front end uses DAO exclusively and you can assume

[quoted text clipped - 18 lines]

Thanks for any help.

 

[Van T. Dinh]

Ans to Q1: MSDE is more or less a cut-down version of SQL Server with
restrictions so nothing is 100% percent guaranteed but if your database work
fines in MSDE, it should work fine in SQL Server.

Ans to Q2: You don't even need to modify Access / JET SQL if you query
against the ODBC-linked Tables to make them work. However, there are
scenarios where it is more efficient to use View or SP on the server rather
than Access Query / JET SQL. The advantage is that you can do "easy"
conversion and get the database to work using Queries against Linked Tables
and then try to improve the efficiency later using the features of the SQL
Server T-SQL, etc ...

Ans. to Q3: In lots of opening Recordset statement, you will need to use
the "dbSeeChanges" option in the OpenRecordset Method. Check Access VB Help
on the OpenRecordset Method for this option.

--
HTH
Van T. Dinh
MVP (Access)

So, let me see if I understand.

1) Since I want to just try this first without spending a lot of money for
SQL Server can I try this first with MSDE. can I use the Upsizing Wizard
to
migrate my tables over to an MSDE database. Am I correct in assuming that
if
I get my application to work using MSDE, then it will automatically work
if
we move the MSDE to SQL Server.

2) On the code side, I will need to modify any Access SQL that is not
compatible with SQL Server SQL. For example instead of '&' for
concatenation
I need to use '+'

3) Also, I generally use the following to create recordsets.

Set wsR = DBEngine.Workspaces(0)

'Open the back-end Database
Set dbR = wsR.OpenDatabase(CurrentProject.FullName)

'Open the Recordset on the Database
Set rsR = dbR.OpenRecordset(strSQLR)

How will I need to change this in order to implement it with SQL Server
(MSDE)
?

Thanks.

What is 'best' rather depends on what your goals and priorities are, but
the
approach that will require the fewest modifications to your existing
application is to use ODBC to link to the SQL Server tables. You will,
however, need to add a unique index or constraint to any tables that lack
them if you want users to be able to update data in those tables. Any
ODBC-linked table without a unique index or constraint is read-only.

I have an Access application that consists of a front end and backend.
The
backend is Jet. The front end uses DAO exclusively and you can assume

[quoted text clipped - 18 lines]

Thanks for any help.