Access DB to SQL Server

[m stroup]

I have an up and running split Access 2003 DB with four or five users. I
have quite a bit of VBA code in forms and modules.
I was told we will be moving this to an SQL server to allow for internet use.

I have only been exposed to sql through the queries I have written.

How easy is it to perform such a migration/conversion? In addition we will
be moving to Office 2007. Does the VBA have to be changed to sql?
etc. etc..

If you could point me to some info resources, I would appreciate it.

Thanks in advance.

 

[David H]

SQL Server is a Database Engine. Access is a DBE that includes the ability to
present a spit and polished user interface. If your Access database is split,
all you have to do is upsize the backend from Access to SQLServer. As long as
the object names do not change you shouldn't have any major issues with the
code. In terms of implementation, you will only have to point the front end
to the back end. Access doesn't really care where the data is kept in terms
of the backend. However, there may be a few instances were you have to modify
SQL statements depending on how they were written as I believe that there are
minor syntax differences between the SQL implementation for Access and that
for SQLServer.

Commonly accepted paranoid fears and phobias in QA/Testing would call for a
thorough system test just to be certain that there aren't any issues.

 

[m stroup]

Thanks for the reply David.

Once migrated, will forms/queries be created in the same way?

 

[Albert D. Kallal]

Thanks for the reply David.

Once migrated, will forms/queries be created in the same way?

Yes, for the most part they should not have to be changed. The "major" work
that occurs in your migration project is that you find that sql server will
run a LOT slower then how your application ran before. So, where the work
comes in is you trying to restore performance to the application.

Keep in mind, that if you already have a working application, then do NOT
use a access data project, but use linked tables

If you starting from scratch, then an access data project makes sense, but
if you already have a working application, then access data projects force
you to re-write all of your dao recordset code.....

 

[david]

Using Linked Tables, you don't have to use SQL Server SQL.

Access was designed to connect to multiple kinds of data sources,
and one of the interfaces which was included is an ODBC interface.

This translates Jet SQL to ODBC SQL. SQL Server supports
ODBC SQL as a subset of SQL Server SQL.

Although there are minor syntax differences between JET SQL
and ODBC SQL, you don't have to worry about them if you
used linked tables, because Access translates for you.

There are also several different ways to use ODBC SQL or
SQL Server SQL instead of JET SQL, but you don't have
to do that to start with if you use linked tables in an MDB.

You may have problems with Transactions.

The MOVE methods (movefirst, movelast etc) won't work
with linked tables, including SQL Server linked tables.

(david)

 

[Armen Stein]

Yes, for the most part they should not have to be changed. The "major" work
that occurs in your migration project is that you find that sql server will
run a LOT slower then how your application ran before. So, where the work
comes in is you trying to restore performance to the application.

Hi Albert,

I think you should add the word "may", as in:

"The "major" work that occurs in your migration project is that you
MAY find that sql server will run a LOT slower..."

It's true that some things will run slower after switching the
back-end to SQL Server - but it depends on the complexity of the
application and the size of the database. It's possible that it will
run just fine without changes - makes sense to try it out first.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Armen Stein]

The MOVE methods (movefirst, movelast etc) won't work
with linked tables, including SQL Server linked tables.

Not sure what you mean here. We use the Move methods with SQL linked
tables. Maybe we're talking about different things - can you clarify?

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Armen Stein]

I have an up and running split Access 2003 DB with four or five users. I
have quite a bit of VBA code in forms and modules.
I was told we will be moving this to an SQL server to allow for internet use.

If you could point me to some info resources, I would appreciate it.

If you use code that retrieves the Identity (was AutoNumber) key of a
just-created record before the .Update, it won't work after moving to
SQL Server. You need to use the LastModified bookmark after the
..Update instead.

I've written a slideshow on techniques for using Access as a
client-server front-end to SQL Server databases. It's called "Best of
Both Worlds" at www.JStreetTech.com/Downloads.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[David H]

....and of course you don't have any SQL statements hard coded in your SUB's
or FUNCTIONS.

(Hard coding statements in code creates a situation where the database
engine (regardless if its Acces, SQLServer, Oracle, etc.) has to optimize the
query each and everytime that its executed which MAY be a NOTICABLE
performance issue.

 

[David W. Fenton]

If you starting from scratch, then an access data project makes
sense

....make sense if you are interested in going down a path that MS is
clearly not supporting any longer as its main toolbox for working
with SQL Server.

At this late date, it seems to me that it would be very foolish to
start a brand-new ADP.

 

[David W. Fenton]

It's true that some things will run slower after switching the
back-end to SQL Server - but it depends on the complexity of the
application and the size of the database. It's possible that it
will run just fine without changes - makes sense to try it out
first.

Absolutely. Since 1998, I've been designing all my apps with an eye
to upsizing to a server back end. This means efficient data
retrieval, no binding to full tables, loading combo box rowsources
only when displayed, limiting combo boxes to small rowcounts, etc.
The result is that your Access app runs faster with a Jet back end,
and when upsized, very few things cause problems.

The biggest upsizing project I've done was a piece of cake.
Performance on basic operations improved, though a few surprising
things slowed down significantly. Some of the things I was sure
beforehand would need to be re-architected turned out to work fine,
and a few things I didn't even think about turned out to be
disastrously slow. I ended up doing about the amount of work I
expected, just not on the parts I'd forecast in advance.

The key point:

The front-end design that makes an efficient client/server front end
will also make a nice, efficient front end to Jet.

 

[David W. Fenton]

If you use code that retrieves the Identity (was AutoNumber) key
of a just-created record before the .Update, it won't work after
moving to SQL Server. You need to use the LastModified bookmark
after the .Update instead.

Or just use SELECT @@IDENTITY, which works not only with SQL Server,
but with Jet (starting with Jet 4).

 

[David W. Fenton]

(Hard coding statements in code creates a situation where the
database engine (regardless if its Acces, SQLServer, Oracle, etc.)
has to optimize the query each and everytime that its executed
which MAY be a NOTICABLE performance issue.

Not true. SQL Server will evaluate SQL statements that come in and
compile and cache the compilation. If the same statement comes
again, it will use the compiled query plan.

You can get the same results from MySQL by using the ADODB add-in,
which will do the same kind of caching for identical statements (but
not for equivalent statements with different criteria in the WHERE
clauses).

 

[David H]

Uh, you left out never, never, never hard coding SQL statements in code so
that they only have to be optimized once as opposed to optimized each and
everytime that they're executed.

 

[David H]

Even if the criteria changes?

Not true. SQL Server will evaluate SQL statements that come in and
compile and cache the compilation. If the same statement comes
again, it will use the compiled query plan.

You can get the same results from MySQL by using the ADODB add-in,
which will do the same kind of caching for identical statements (but
not for equivalent statements with different criteria in the WHERE
clauses).

 

[Albert D. Kallal]

On Wed, 22 Apr 2009 00:37:11 -0600, "Albert D. Kallal"

It's true that some things will run slower after switching the
back-end to SQL Server - but it depends on the complexity of the
application and the size of the database. It's possible that it will
run just fine without changes - makes sense to try it out first.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

In all fairness, I had some forms and quires speed up quite remarkable by
"just" changing to sql server. So, sure, in all fairness, this issue goes
both ways. Often something will run better on sql server without any changes
to the application at all, I just wanted to point out it not a given...

 

[david]

Not sure what you mean here. We use the Move methods with SQL linked
tables. Maybe we're talking about different things - can you clarify?

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

Yes, that was badly said.

Rather,

The Index method won't work with linked tables, including SQL Server linked
tables, and linked tables have no 'current index'.

That means that the MOVE methods (movefirst, movelast etc) have no defined
record order.

(david)

 

[David W. Fenton]

Uh, you left out never, never, never hard coding SQL statements in
code so that they only have to be optimized once as opposed to
optimized each and everytime that they're executed.

You obviously did not read my post. Your advice is simply COMPLETELY
WRONG.

 

[David W. Fenton]

Even if the criteria changes?

Yes, assuming the criteria are on the same columns. In other words,
the *values* in the SQL statement's WHERE clause makes no
difference, only the columns.

That is, that's the way it works in SQL Server. For the MySQL ADODB
add-in, it works in some cases and not in others (so you don't want
to depend on it).

 

[m stroup]

Thank you all very much for your input. I found your website, Armen, from a
different post as well. Thanks for sharing.