Opinions on SQL Server upsize

[Beetle]

I have an Access application that I created for the organization I work for.
Created in A2003, saved as A2000 for compatibility. It's a regularly used app
with 300-500 new records per day. We have some locations accross town
that access our network via WAN, that need to be able to use the app. The IT
guys say Terminal Services isn't an option, so we have decided to upsize to
SQL Server. The IT guys (I'm not in the IT dept.) know/have control of SQL
Server but don't know Access. I know the Access side but not much about
SQL Server.

All forms/reports in this app are based on queries, nothing based directly
on a table. We have already run an upsize on a test copy of the app and
that went very smoothly, but I am wondering if there is anything I should
be aware/beware of before upsizing the real app. I don't want to convert
it and then realize it doesn't perform well because I zigged when I should
have zagged.

As long as your name isn't Aaron Kempf, I would be grateful for any
opinions/advice one way or the other.

 

[Klatuu]

What, are you a nut case?????
Don't you know that SQL Server is an overrated piece of $#@$#r????
Microsoft plans to discontine support for SQL Server after 2008.
Access is the only way to go. You can do anything with it!!

Sorry, beetle. After your disclaimer, I couldn't resist

I would just thoroughly test everything.
If you find some slow spots, you might look at them. I found in a few
instances where chaning an Access query to an SQL View helped performance.
And in some cases, a pass through query made a big difference. I think SQL
Server needs more specific index specification than Access.

Basically, anything you can hand of to SQL Server to do is a good idea. For
examples, any action queries would be better as Store Procedures.

Good luck.

 

[Roger Carlson]

Get a copy of "Microsoft Access Developer's Guied to SQL Server" by Chipman
and Baron. It has a lot of good advice for creating Access apps with SQL
Server backends.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Beetle]

So what you're saying is "SQL Server is 'DED' and has been for the last
decade"? ;-)

Thanks for the response Dave. I had seen it mentioned before (I think by you)
that Views / Pass Through queries might be a better way to go when
using a SQL Server back end. I assume these would be used as a recordset
just like an Access query (forgive my ignorance, I don't know much about
SQL Server - we can't all be as brilliant as Aaron).

I'll take your advice and test everything, then go from there if anything
seems
to be running to slowly.

 

[Beetle]

Thanks for the tip Roger. I will definitely look into that, as this is probably
not the only Access app I will be developing that will need a SQL Server
back end. The app I am currently working with was not originally developed
with a SQL Server BE in mind, so I'm hoping it won't need any major
overhaul.
--
_________

Sean Bailey

Get a copy of "Microsoft Access Developer's Guied to SQL Server" by Chipman
and Baron. It has a lot of good advice for creating Access apps with SQL
Server backends.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[pietlinden]

Get a copy of "Microsoft Access Developer's Guied to SQL Server" by Chipman
and Baron.

Since this hasn't been updated in the last 8 years or so, I assume the
information still holds? You should be able to get this really cheap
on Amazon. I don't think I paid more than $15 for it.

 

[Albert D. Kallal]

\>

All forms/reports in this app are based on queries, nothing based directly
on a table.

The above issue never made a difference in performance in terms of form load
times anything else in MS access. So using queries or the base tables for
forms or reports really doesn't make a difference. If you upsize to SQL
server, you'll find the above issues a nonissue also.

We have already run an upsize on a test copy of the app and
that went very smoothly, but I am wondering if there is anything I should
be aware/beware of before upsizing the real app.

Well you have two possibilities here, if you have a lot of existing code,
then you'll simply want upsize the back end data, and use odbc linked
tables.

If you don't have any/much existing code then you might consider converting
your application into an access data project. Of course if you convert to an
access data project, the problem is your existing record set and dao code
needs to be rewritten. It is for this reason I don't consider using an
access data project a good idea for existing applications with any amounts
of code.

So your best choice for upsizing is to keep your application as it is,
and simply use link tables (odbc) to the SQL server. That means your
front end part will remain a mdb/mde part.

There's really only a few things you have to watch for in code, for example
any code that adds a record, you can get the primary right away when using
JET, but with sql server, you have to first save the reocrd, and THEN grab
the primary key.

I don't want to convert
it and then realize it doesn't perform well because I zigged when I should
have zagged.

Keep in mind that simply moving the data to SQL server will not yield you
any performance increase at all, and in some cases you find things run
slower.

The critical concept to keep in mind is that you do not want to
load up a form more records than is necessary. So for example you never just
going to open a form attached to a query or large table without also
including some kind of where clause. This concept of asking
the user *before* you load up a form in the following little article of
searching:

http://www.members.shaw.ca/AlbertKallal/Search/index.html

In the above what it simply means you try to ask the user for the invoice
number, and then launch the form with a where clause to only load the one
record (and all the sub-form reocrds). So, for reasons of performance you
avoid any case in which you load up a form to a large table and the user
has to scroll through it, or do the find *inside* the form....

 

[Tony Toews [MVP]]

So what you're saying is "SQL Server is 'DED' and has been for the last
decade"? ;-)

I'll take your advice and test everything, then go from there if anything
seems to be running to slowly.

But some queries which are updatable in Access aren't updatable as
views in SQL Server. There are little gotchas that you'll come
across as you continue in your journey.

Tony

--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

Since this hasn't been updated in the last 8 years or so, I assume the
information still holds? You should be able to get this really cheap
on Amazon. I don't think I paid more than $15 for it.

The basics haven't changed much.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Beetle]

Albert,

Thank you for your very informative reply. I wasn't clear in my original post
but the reason I use queries as recordsource is because I don't load the
entire recordset, only what is necessary, so hopefully that won't be an issue.

I wasn't necessarily expecting an increase in performance. The main reason
we are doing this is because of the information I have read (much of it on
your site) about the reasons why Access should not be used over WAN.

I do have a bit of code in the FE, so I had already ruled out trying to
convert
to ADP.

When we did the test upsize I made a copy of the FE, imported the tables
and "unsplit" it so to speak, then we did the upsize. I don't know why it
didn't occur to me to just upsize the BE. It seems rather obvious now that
you've mentioned it. Anyway, when you upsize the BE, do the established
links remain (in other words, do the links get "converted" also) or will
they need to be re-established?

Again, thanks for your time.

 

[Larry Linson]

But some queries which are updatable in Access aren't updatable as
views in SQL Server. There are little gotchas that you'll come
across as you continue in your journey.

But, in my experience, the Queries that benefitted _most_ from use of server
Views were Record Source for Reports. They tended to be the most complex
Queries in the applications, which was why forcing data manipulation to the
SQL Server side cut down so much on the data transmitted across the network.
Often, we still used an Access Query, but used a View as one of the data
sources in that Query, rather than using the View as the Record Source.

Larry Linson
Microsoft Office Access MVP

 

[david]

Don't assume that SQL Server will be usable over the WAN -- you
have to test that too. I've never had a customer that was actually
satisfied with SQL Server over WAN, and that is using disconnected
recordsets.

Of course this is all relative: I've never had a customer who was actually
satisfied with any ODBC database over WAN, but mostly they don't
have a choice, because the database is state-wide and specified by head
office, and when you are doing client contact, you just talk to the client
while you wait for the page to come up. If you only have a few remote
clients, probably they are people who management doesn't care about
anyway. Unless they are in sales...

Also note that DAO transactions are broken against SQL Server - you
may need to recode them as stored procedures or ADO transactions.
This may be related to Server Version - the transaction lock types have
changed over the years - but it's mostly Jet 4. Jet 3.5 worked OK.

(david)

 

[Albert D. Kallal]

Anyway, when you upsize the BE, do the established
links remain (in other words, do the links get "converted" also) or will
they need to be re-established?

actually I suggest you make a copy of the backend, and then upsize that
(make sure you choose the option to create links to the SQL server when you
do this).

You then go to your front end, delet all your current existing links, and
then import the new links that will be created in that copy of the backend.

This is a good way to get new links to SQL server into your front end....

 

[Grover Park George]

Since this hasn't been updated in the last 8 years or so, I assume the
information still holds?  You should be able to get this really cheap
on Amazon.  I don't think I paid more than $15 for it.

I believe this book is still as good as it gets in this arena.

BTW: If you'll forgive a slight brag. My copy was personally
autographed by Mary when she spoke to our local Access User Group last
spring.

George