Upsizing just Tables verses Tables and Queries

[Sylvain Lafontaine]

Often, the upsizing Wizard will forget to set the primary key on the upsized
table; this might be the reason why you cannot add a new record but they are
other possibilities as well. Without you showing us the design of the
tables - both before and after the upsizing - and of the query used as the
record source of the form, it's hard to say anything more.

Usually, it's not hard to have a version that will work equally well against
either an Access/JET backend or a SQL-Server backend but you'll have to make
adjustement to your code here and there. For example, you often have to add
the dbSeeChanges option when executing code or opening a recordset and when
you need to retrieve a new identity value (the equivalent of Access'
autonumber field) for a new record on SQL-Server, its value is not known
until the record has been added to the database; so you'll have to adapt
your VBA code in order to distinguish between these two cases; for example:

' Creating a DAO recordset:
Dim rs As DAO.Recordset
Set rs = db.OpenRecordset("MyTable", dbOpenDynaset, dbAppendOnly Or
dbSeeChanges)

rs.AddNew

' Adding some data to the new record:
rs!MyNumber = 1
rs!MyName = "MyName"

' And now, retrieving the the ID value:
#If SQLServer = 1 Then
rs.Update
rs.Bookmark = rs.LastModified
NewId = rs!TableId
#Else
NewId = rs!TableId
rs.Update
#End If

In this example, "SQLServer=1" is a property that I've added in the VBA
Editor under Tools | Options | Property. Notice also the option
dbSeeChanges that I've added.
--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

I have an Access database that I have been developing for about 2 years
now.
I have about 150 tables and over 600 queries. Just the front-end of my
database is 30 Meg. One of my customers is now adding about 100 Meg of
data
per year and has asked me to give him a quote on a SQL back-end. They
already have the SQL Server. Note: My other customers are currently
low-end
users and may never outgrow Access.
So I'm looking at keeping two versions one with SQL and one with Access.
How likely is it if I just upsize the tables and leave the queries alone
that
I will be able to make my front-end work for both versions? I haven't
tried
SSMA yet but when I ran the upsizing wizard a couple of versions ago my
database appeared to work for existing records but I was unable to add new
through my forms using Access's built in add new records button. I never
took it beyond this initial test.
I'd love to be able to just upsize the tables and then still continue
development in Access Or should I just go for it and do both the queries
and
tables for the SQL version?
I'd appreciate any advice you can give me since my customers are
non-profits
and much of the work I do is pro-bono.
-Mark-
--
Mark Annett
ISPManSys.com

[Mark Annett]

Mark,
I'm more concerned that by just upsizing the tables, you may in fact
find some performance slowdowns. It's worth trying, but I wouldn't be
surprised if some functionality is slower using SQL through Access
linked tables.

<Mark> I've tried pretty hard to follow many of the tips you specify in
your slide show below. The one I haven't been able to do was was to
correctly implement coding so that I am not loading tabs until that
particular tab is accessed. If you have a reference for that I would really
appreciate it. Regardless of whether or not I successfully implement SQL my
current database could benefit from that technique.

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. It includes some
thoughts on when to use SQL Server, performance and security
considerations, concurrency approaches, and techniques to help
everything run smoothly.

<Mark> FYI, for your slide shows on your web site, you might want to
consider using Authorstream.com. It is essentially YouTube but for Power
Point. It is a free service and it gives you lots of options for embedding
Power point videos on your site and it will even automatically convert them
to videos that you can post to YouTube if you desire. You can see a sample
of how I use it on my site at
http://ispmansys.com/ISPMS/ISPMSSystemOverview.htm

One of the nice things is that it accepts of power points animation features
and can run full screen and there are no down loads.

Mark Annett
ISPManSys.com

[Mark Annett]

Post by Sylvain Lafontaine
Often, the upsizing Wizard will forget to set the primary key on the upsized
table; this might be the reason why you cannot add a new record but they are
other possibilities as well. Without you showing us the design of the
tables - both before and after the upsizing - and of the query used as the
record source of the form, it's hard to say anything more.

<MARK> I have a feeling it was something other than the primary keys because
I am pretty sure they were there. However, it doesn't really matter what
problems I had because am sure once I try SSMA I'll come up with new ones.

Post by Sylvain Lafontaine
Usually, it's not hard to have a version that will work equally well against
either an Access/JET backend or a SQL-Server backend but you'll have to make
adjustement to your code here and there.

<Mark> I'm very excited to hear this from you and similar comments from
John! Thanks for the tip below!

Post by Sylvain Lafontaine
For example, you often have to add
the dbSeeChanges option when executing code or opening a recordset and when
you need to retrieve a new identity value (the equivalent of Access'
autonumber field) for a new record on SQL-Server, its value is not known
until the record has been added to the database; so you'll have to adapt
Dim rs As DAO.Recordset
Set rs = db.OpenRecordset("MyTable", dbOpenDynaset, dbAppendOnly Or
dbSeeChanges)
rs.AddNew
rs!MyNumber = 1
rs!MyName = "MyName"
#If SQLServer = 1 Then
rs.Update
rs.Bookmark = rs.LastModified
NewId = rs!TableId
#Else
NewId = rs!TableId
rs.Update
#End If
In this example, "SQLServer=1" is a property that I've added in the VBA
Editor under Tools | Options | Property. Notice also the option
dbSeeChanges that I've added.
--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

I have an Access database that I have been developing for about 2 years
now.
I have about 150 tables and over 600 queries. Just the front-end of my
database is 30 Meg. One of my customers is now adding about 100 Meg of
data
per year and has asked me to give him a quote on a SQL back-end. They
already have the SQL Server. Note: My other customers are currently
low-end
users and may never outgrow Access.
So I'm looking at keeping two versions one with SQL and one with Access.
How likely is it if I just upsize the tables and leave the queries alone
that
I will be able to make my front-end work for both versions? I haven't
tried
SSMA yet but when I ran the upsizing wizard a couple of versions ago my
database appeared to work for existing records but I was unable to add new
through my forms using Access's built in add new records button. I never
took it beyond this initial test.
I'd love to be able to just upsize the tables and then still continue
development in Access Or should I just go for it and do both the queries
and
tables for the SQL version?
I'd appreciate any advice you can give me since my customers are
non-profits
and much of the work I do is pro-bono.
-Mark-
--
Mark Annett
ISPManSys.com

[Mark Annett]

Thanks John,

I'm feeling much more confident now about the possibility.

Yes, I have the front-end and back-end split.

Thanks for the link!
--
Mark Annett
ISPManSys.com

On Fri, 19 Jun 2009 09:42:01 -0700, Mark Annett

So I'm looking at keeping two versions one with SQL and one with Access.
How likely is it if I just upsize the tables and leave the queries alone that
I will be able to make my front-end work for both versions?

Works for me. I sure hope your Access-only application is split, with the
tables in a backend and the forms/reports/queries in individual user
frontends; if not, DO SO immediately.
See Tony Toews' hints about SQL upsizing at
http://www.granite.ab.ca/access/sqlserverupsizing.htm
--
John W. Vinson [MVP]

[Armen Stein]

On Sat, 20 Jun 2009 13:59:38 -0400, "Sylvain Lafontaine"

Post by Sylvain Lafontaine
#If SQLServer = 1 Then
rs.Update
rs.Bookmark = rs.LastModified
NewId = rs!TableId
#Else
NewId = rs!TableId
rs.Update
#End If
In this example, "SQLServer=1" is a property that I've added in the VBA
Editor under Tools | Options | Property. Notice also the option
dbSeeChanges that I've added.

Hi Sylvain,

The technique for SQL Server (using the LastModified property) works
fine for Access back-end tables too, so you don't actually need the
branch in your code. We just use the LastModified method in all
cases.

Mark,

I'm more concerned that by just upsizing the tables, you may in fact
find some performance slowdowns. It's worth trying, but I wouldn't be
surprised if some functionality is slower using SQL through Access
linked tables.

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. It includes some
thoughts on when to use SQL Server, performance and security
considerations, concurrency approaches, and techniques to help
everything run smoothly.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

[Tony Toews [MVP]]

I have an Access database that I have been developing for about 2 years now.
I have about 150 tables and over 600 queries. Just the front-end of my
database is 30 Meg. One of my customers is now adding about 100 Meg of data
per year and has asked me to give him a quote on a SQL back-end. They
already have the SQL Server. Note: My other customers are currently low-end
users and may never outgrow Access.
So I'm looking at keeping two versions one with SQL and one with Access.
How likely is it if I just upsize the tables and leave the queries alone that
I will be able to make my front-end work for both versions?

I've been thinking about this a bit thus my late reply.

I started an upsizing project on a very large system however due to
PHBs (Dilbert's Pointy Haired Boss) at my client I never finished it.

One thing I did do was create a bunch of VBA code which "upsized" the
queries to SQL Server. (This was long before SSMA was available.)
Given that I had stacked (or nested) queries (queries that referenced
other queries) I would rerun the code several times until it couldn't
upsize any more queries.

(A surprising number of queries ported across quite nicely. However
I don't know just how many were truly successful in terms of testing
as I didn't get that far into the project.)

I then read the list of queries on SQL Server and deleted those
queries from the Access FE and then linked to the queries.

My point is that you could do something similar to optimize the
performance of queries which are a bottleneck. Get the few queries
which are running slow converted to SQL Server and optimized. Then,
before shipping that client an update, have some code delete all local
queries which are also present on the SQL Server system.

Of course you'll need to ship the SQL Server scripts which delete and
recreate all queries on their system.

BTW do the performance testing using the clients live data on another
system. Don't do such testing with SQL Server running on your own
system. If you really want to do a decent job with performance
testing force that systems network adapter to run at 10 Mbps.

(If you want to do a great job of performance testing there's a Linux
distribution somewhere which fits on a floppy, runs on a 486 with 1 Mb
RAM, two network cards, and allows you to throttle network performance
down as low as you want such as dialup modem speeds. However this is
overkill.

I personally installed a gateway system running on a 386SX back in
about 1990 which ported packets between a 4 Mbps and a 16 Mbps Token
Ring LAN with no apparent slowdown in data. So a 486 would have no
troubles handling a 10 mbps connection. )

Tony

[Mark Annett]

Post by Tony Toews [MVP]
My point is that you could do something similar to optimize the
performance of queries which are a bottleneck. Get the few queries
which are running slow converted to SQL Server and optimized. Then,
before shipping that client an update, have some code delete all local
queries which are also present on the SQL Server system.

<Mark> Thanks Tony, I was thinking along those lines. I would first try
just the tables and if I don't lose anything in performance than I am good to
go.

Where I do have performance issues then I will push those queries. Some of
my reports are quite complicated and I think for one report I have a series
of at least 9 nested queries utilized to generate it. I'm kind of worried
about these. Thankfully for most of the cases I have multiple nested queries
it is in reports and not on forms. People are a lot more patient when a
report is slow then they are when the data is being displayed on a form. So
hopefully these will work and since I filter the data at the lowest possible
level in my queries to make them more efficient hopefully I will be okay.

[Tony Toews [MVP]]

Post by Mark Annett
Thankfully for most of the cases I have multiple nested queries
it is in reports and not on forms.

Yes, I found that to be the case as well.

Post by Mark Annett
People are a lot more patient when a
report is slow then they are when the data is being displayed on a form. So
hopefully these will work and since I filter the data at the lowest possible
level in my queries to make them more efficient hopefully I will be okay.

I pretty much used the OpenReport where clause to do the filtering.

The biggest report performance improvement I found was to move
subreports fields to the main report and turn the subreport fields
visible on and off as required. Those 100 page reports went from 30
minutes to 30 seconds to create.

(There were used to schedule welding jobs through a welding shop with
several hundred employees. The foremen had to balance component
availability for the final product along with client priorities and
what welding machines were available on what line. Etc, etc, etc.
Some of the components, such as large valves meant for 1000 F hydro
carbons at thousands of PSI required 12 and 18 month lead time.)

Tony

[Mark Annett]

Post by Tony Toews [MVP]
The biggest report performance improvement I found was to move
subreports fields to the main report and turn the subreport fields
visible on and off as required. Those 100 page reports went from 30
minutes to 30 seconds to create.

<Mark> What an excellent idea! I never thought to try that!

[Tony Toews [MVP]]

Post by Mark Annett

Post by Tony Toews [MVP]
The biggest report performance improvement I found was to move
subreports fields to the main report and turn the subreport fields
visible on and off as required. Those 100 page reports went from 30
minutes to 30 seconds to create.

<Mark> What an excellent idea! I never thought to try that!

Hey, that tip is buried way down in my performance FAQ. <smile>

Tony

[Tony Toews [MVP]]

Post by Tony Toews [MVP]
My point is that you could do something similar to optimize the
performance of queries which are a bottleneck. Get the few queries
which are running slow converted to SQL Server and optimized. Then,
before shipping that client an update, have some code delete all local
queries which are also present on the SQL Server system.
Of course you'll need to ship the SQL Server scripts which delete and
recreate all queries on their system.

When I think about this a bit more I'd refine that a bit more. I have
a check list of things to do when creating the install routine for my
software as occasionally I'll forget things.

So instead of making them a special release put that logic in the
startup of your app. If the backend is a SQL Server system, and
they're down a version then
- upgrade the table, field, indexes and relationships on then server,
- recreate the queries on the server
- delete the queries from the FE.

Of course this will require a version number in a table in the BE.

Tony

[Tony Toews [MVP]]

Post by Tony Toews [MVP]
- upgrade the table, field, indexes and relationships on then server,

I forgot to mention. The $10 version of the following tool creates
SQL Server scripts.

Compare'Em
http://home.gci.net/~mike-noel/CompareEM-LITE/CompareEM.htm

Tony

[Mark Annett]

Tony,

I could see how the tool below would be very very useful if you haven't
split the database. Does it have a use if you have?

Right now, to update folks, I simply just give them a new front end.
--
Mark Annett
ISPManSys.com

Post by Tony Toews [MVP]

Post by Tony Toews [MVP]
- upgrade the table, field, indexes and relationships on then server,

I forgot to mention. The $10 version of the following tool creates
SQL Server scripts.
Compare'Em
http://home.gci.net/~mike-noel/CompareEM-LITE/CompareEM.htm
Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
Granite Fleet Manager http://www.granitefleet.com/

[Tony Toews [MVP]]

Post by Mark Annett
I could see how the tool below would be very very useful if you haven't
split the database. Does it have a use if you have?

Absolutely. I use the VBA code but open the BE database directly
rather than via the linked tables.

Indeed all my action queries I use to clean up data or make multiple
tables from other tables have to have the IN clause in the SQL string.
It gets a bit interesting testing all

Post by Mark Annett
Right now, to update folks, I simply just give them a new front end.

And yes, all I give them is a new FE.

Tony

[John W. Vinson]

On Fri, 19 Jun 2009 09:42:01 -0700, Mark Annett

So I'm looking at keeping two versions one with SQL and one with Access.
How likely is it if I just upsize the tables and leave the queries alone that
I will be able to make my front-end work for both versions?

Works for me. I sure hope your Access-only application is split, with the
tables in a backend and the forms/reports/queries in individual user
frontends; if not, DO SO immediately.

See Tony Toews' hints about SQL upsizing at
http://www.granite.ab.ca/access/sqlserverupsizing.htm