Upgrade Access backend to SQL database

S

Simon J

I understand that I will achieve better performance if I link my Access front
end to SQL tables rather than the current Access table structure. Could you
please advise whether this is a reasonable assumption, and where I might find
a description of the process of converting my existing tables to SQL. Will
my front end queries, functions, reports and forms still run correctly? If
not is there an article on upgrading these too?
thanks
 
D

Duane Hookom

You can't assume your performance will increase. It all depends on how your
tables are structured, how many users, how many records, and lots of other
factors. If you aren't comfortable with SQL Server then don't go there.
 
G

Granny Spitz via AccessMonster.com

Simon said:
I understand that I will achieve better performance if I link my Access front
end to SQL tables rather than the current Access table structure. Could you
please advise whether this is a reasonable assumption
Click to expand...

It's not a reasonable assumption. If the database architecture is a poor one
in Access, switching to SQL Server with the same architecture usually won't
improve performance significantly, but it *will* increase costs significantly.
If the database is well designed in Access and then upgraded to SQL Server
with that same design, and the queries are altered so that the database
server is doing the work, not the client, and the database server has the
same load and networking capabilities as the Access database, and it has
sufficient hardware (cpu, multi-threading, memory, disk space, etc.), then
you can expect some significant gains in performance.
Will
my front end queries, functions, reports and forms still run correctly?
Click to expand...

They might. But even if they do, for best performance you're going to want
to alter these so that the database server is doing the work and only the
relevant data is being pulled across the network to the client.
If
not is there an article on upgrading these too?
Click to expand...

Hon, it's not an article, it's a whole book. 864 pages. Read Mary Chipman's
and Andy Baron's book, "Microsoft Access Developer's Guide to SQL Server."
It's a five-star book at Amazon.com for a very good reason.
 
S

Sandy

Do you know of a good source to find SQL Server and Conversion training and
Consultants that aid in transitioning?

Thanks,

Sandy
 
Top