SQL Server Performance Forum – Threads Archive

Access interface Sql server database

Hello, sorry for that kind of ignorance.
I have a database and application in access, after a database became too big I’m going to transfer the data to SQL server. The question is how can I configure Access to use data from Sql server?
Thank you in advance.

Applications that use the forms and reports found in the Access user interface can access SQL Server using linked tables. If your application will use linked tables, make sure that all Access tables get moved to SQL Server to increase performance. Creating queries against a mix of Access (Jet) and SQL Server using linked tables can be very resource-intensive. Add your Access DB as linked server to your MS SQL Server, and then use syntax like this: SELECT * FROM <linked server name>.<linked DB>..SomeTable Read Books Online for more info about linked servers. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

What version of Access do you use? –Frank
http://www.insidesql.de

I’m using Access 2002.
satya, I did what you advice me to do, it’s good!!
Please, tell me : about views, should I transfer them to sql server or leave in Access?

IN the terms of performance I would recommend to export them to SQL Server side and handle it.
And also I would like to hear opinion from fellow peers. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

With Access 2002 you can take advantage of an ADP, which I believe to be better than linked tables. You can directly create, edit, and delete SQL Server Object in your database from within the Access interface. As for your question about views:
I would move them to SQL Server, but be aware that in most cases this will require some rewrite as SQL Server does not know IIf, First or Last, which are commonly used by Access developers. –Frank
http://www.insidesql.de

In my modest opinion, be aware of the connection problems :: try to get rid of using JET all the time, and attached tables will certainly provoke a lack of performance …. Recordsets should never be "dynaset" (dont use the Rs.update ever!) and there are much more key features of Access against SQL that could be taken under consideration … the masters probably can give u some link … satya, congrats for ur 5000 posted contributions …..

When I use Access, I prefer to do everything in SQL Server except the interface. You can have all of your tables and stored procedures in SQL Server. With the use of VBA to drive the forms, there really isn’t ever a reason to use linked tables even. It makes a great reporting and prototyping tool when used like this. It does take a little bit of a learning curve to create Access databases like this, but it’s well worth it. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

]]>