linking to SQL Server db table

[colmkav]

Hi,

I may need to convert data stored in an Access database to a SQL
server db due to space issues.

Can you pls tell me whether I can simply link to the SQL Server table
through linked tables and use all the same queries that work on my
Access tables? What issues do I need to resolve if any?

thx
Colm

 

[Ron Weiner]

colmkav formulated the question :

Hi,

I may need to convert data stored in an Access database to a SQL
server db due to space issues.

Can you pls tell me whether I can simply link to the SQL Server table
through linked tables and use all the same queries that work on my
Access tables? What issues do I need to resolve if any?

thx
Colm

The simple answer is "Yes You Can". However there are many security
and performance gains that you might avail yourself of as you gain
experience with Sql. I suspect you will be pleasantly surprised at how
easy this change is.

Rdub

 

[colmkav]

colmkav formulated the question :





The simple answer is "Yes You Can".  However there are many security
and performance gains that you might avail yourself of as you gain
experience with Sql.  I suspect you will be pleasantly surprised at how
easy this change is.

Rdub

Thanks. So you are saying I can just use the same queries I had that
work on my Access tables and now can work on SQL Server tables?

I havent programmed in VBA Access for awhile but I recall runing
stored procedures (writen in PL SQL) when performing some tasks on the
(Oracle tables). Any reason why I would do this rather than the linked
tables? Perhaps speed of execution?

regards
Colm

 

[colmkav]

Thanks.

You are saying that I can still use all the same Access queries
without changing them? I previously recall using stored procedures
consisting of PL SQL on Oracle databases. Any reason I should have
done that? Perhaps speed of execution? \

I havent coded in VBA for quite sometime so have forgetten the reasons
for doing things certain ways.

cheers
Colm

 

[Ron Weiner]

colmkav formulated on Wednesday :

Thanks. So you are saying I can just use the same queries I had that
work on my Access tables and now can work on SQL Server tables?

I havent programmed in VBA Access for awhile but I recall runing
stored procedures (writen in PL SQL) when performing some tasks on the
(Oracle tables). Any reason why I would do this rather than the linked
tables? Perhaps speed of execution?

regards
Colm

Yup, in almost every case the Access queries will work the same way
they did against Access tables, and you might even see an increase in
performance with out changing anything.

However if you want/need to dramatically increase your apps performance
you will probably want to use Access pass-thru queries written with Sql
Servers TSql language.

If you are competent in writing efficient Access queries you'll
discover that there are some minor syntax and key word differences.
However once learned, you could see VERY impressive performance
differences on some queries.

Also Sql Server is FAR more fault tolerant that a file based Access
backend database.

Good luck with your project.

Ron W

 

[Tony Toews]

Can you pls tell me whether I can simply link to the SQL Server table
through linked tables and use all the same queries that work on my
Access tables? What issues do I need to resolve if any?

You've got some very good answers already.

See my Random Thoughts on SQL Server Upsizing from Microsoft Access
Tips page at http://www.granite.ab.ca/access/sqlserverupsizing.htm.
Also see my blog postings on this topic at
http://msmvps.com/blogs/access/archive/tags/SQL+Server/default.aspx

There is a tool from the SQL Server group which is better than the
included SQL Server Upsizing Wizard.
SQL Server Migration Assistant for Access (SSMA Access)
http://www.microsoft.com/sqlserver/en/us/product-info/migration-tool.aspx#Access
--
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/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/

 

[colmkav]

You've got some very good answers already.

See my Random Thoughts on SQL Server Upsizing from Microsoft Access
Tips page athttp://www.granite.ab.ca/access/sqlserverupsizing.htm.
Also see my blog postings on this topic athttp://msmvps.com/blogs/access/archive/tags/SQL+Server/default.aspx

There is a  tool from the SQL Server group which is better than the
included SQL Server Upsizing Wizard.
SQL Server Migration Assistant for Access (SSMA Access)http://www.microsoft.com/sqlserver/en/us/product-info/migration-tool....
--
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/
For a convenient utility to keep your users FEs and other files
  updated seehttp://www.autofeupdater.com/

thanks for the help!