|  | Opinions on SQL Server upsize | | |
| | | Beetle |  |
|  Posted: Tue Sep 02, 2008 4:31 pm Post subject: Opinions on SQL Server upsize |  |
| |  | |
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.
--
_________
Sean Bailey |
| |
| | | Klatuu | |
| | Posted: Tue Sep 02, 2008 5:31 pm Post subject: RE: Opinions on SQL Server upsize | |
| | | |
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.
--
Dave Hargis, Microsoft Access MVP
"Beetle" wrote:
| Quote: | 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.
--
_________
Sean Bailey |
|
| |
| | | Roger Carlson | |
| | Posted: Tue Sep 02, 2008 5:35 pm Post subject: Re: Opinions on SQL Server upsize | |
| | | |
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: LINK
Want answers to your Access questions in your Email?
Free subscription:
LINK
"Beetle" <Beetle@discussions.microsoft.com> wrote in message
news:DEC07758-33F0-4C4E-B4ED-A40A08F910D0@microsoft.com...
| Quote: | 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.
--
_________
Sean Bailey |
|
| |
| | | Beetle | |
| | Posted: Tue Sep 02, 2008 5:54 pm Post subject: RE: Opinions on SQL Server upsize | |
| | | |
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.
--
_________
Sean Bailey
"Klatuu" wrote:
| Quote: | 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.
--
Dave Hargis, Microsoft Access MVP
"Beetle" wrote:
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.
--
_________
Sean Bailey |
|
| |
| | | Beetle | |
| | Posted: Tue Sep 02, 2008 6:02 pm Post subject: Re: Opinions on SQL Server upsize | |
| | | |
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
"Roger Carlson" wrote:
| Quote: | 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: LINK
Want answers to your Access questions in your Email?
Free subscription:
LINK
"Beetle" <Beetle@discussions.microsoft.com> wrote in message
news:DEC07758-33F0-4C4E-B4ED-A40A08F910D0@microsoft.com...
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.
--
_________
Sean Bailey
|
|
| |
| | | Albert D. Kallal | |
| | Posted: Tue Sep 02, 2008 6:25 pm Post subject: Re: Opinions on SQL Server upsize | |
| | | |
"Beetle" <Beetle@discussions.microsoft.com> wrote in message
news:DEC07758-33F0-4C4E-B4ED-A40A08F910D0@microsoft.com...
\>
| Quote: | 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.
| Quote: | 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.
| Quote: | 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:
LINK
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....
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com |
| |
| | | Tony Toews [MVP] | |
| | Posted: Tue Sep 02, 2008 6:53 pm Post subject: Re: Opinions on SQL Server upsize | |
Beetle <Beetle@discussions.microsoft.com> wrote:
| Quote: | So what you're saying is "SQL Server is 'DED' and has been for the last
decade"? 
|
Exactly! <chuckle>
| Quote: | 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
LINK
Tony's Microsoft Access Blog - LINK |
| |
| | | Tony Toews [MVP] | |
| | Posted: Tue Sep 02, 2008 6:53 pm Post subject: Re: Opinions on SQL Server upsize | |
"pietlinden@hotmail.com" <pietlinden@hotmail.com> wrote:
| Quote: | 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.
|
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
LINK
Tony's Microsoft Access Blog - LINK |
| |
| | | Beetle | |
| | Posted: Tue Sep 02, 2008 7:46 pm Post subject: Re: Opinions on SQL Server upsize | |
| | | |
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.
--
_________
Sean Bailey
"Albert D. Kallal" wrote:
| Quote: | "Beetle" <Beetle@discussions.microsoft.com> wrote in message
news:DEC07758-33F0-4C4E-B4ED-A40A08F910D0@microsoft.com...
\
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:
LINK
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....
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com
|
|
| |
| | | pietlinden@hotmail.com | |
| | Posted: Tue Sep 02, 2008 8:22 pm Post subject: Re: Opinions on SQL Server upsize | |
On Sep 2, 2:35 pm, "Roger Carlson" <RogerCarl...@noemail.noemail>
wrote:
| Quote: | 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. |
| |
| Page 1 of 2 .:. Goto page 1, 2 Next | |
|
|
