Upgrade Access backend to SQL database

[Guest]

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

 

[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.

 

[Granny Spitz via AccessMonster.com]

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

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?

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?

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.

 

[Guest]

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

Thanks,

Sandy

 

[Granny Spitz via AccessMonster.com]

Do you know of a good source to find SQL Server and Conversion training

Not formal training in a classroom. But Microsoft is offering several free
online SQL Server 2005 courses for the next month.
https://www.microsoftelearning.com/sqlserver2005/

Do you know ...
Consultants that aid in transitioning?

Yes, but they aren't taking on any new clients for the foreseeable future.
Sorry.