Access migration to SQL server issue

[Mikusss]

Good day dear experts,

I have a global question about the access migration to MSSQL serv , a lot of solution in google, but no clear anwers...
well just want to see your proposions: i'm now splitting between two ways,
to use ODBC linked table connection and MDB file as front. or use ADP file as a frontend and DAO as a SQL technology.
I have a new access database system without any data at the moment for real estate bussiness, about 15 tables, 4 forms, biggest one is abot 30 fields.
So it not seems to be a very big overload on queries. The problem is i allready developed the system on the access, and it seems to work fine, but when I tryed to move it on SQL throught ADP project, You know, i was kind of confused. and the idea of rewriting all the code i've made dissapoint me deeply ...

So, can't wait for your proposes, about this issue...
Is it ODBC or is it DAO ???
If it will be DAO afterall, may be you can give a link to the clear tutorial...
"How to modify code for ADP files."
beacaose i realy dont have much time to read a lot of prologs about the technologies and so on, i need to deal with this issue till next week !!!

Thnks a lot in advance...
Mike

 

[tehNellie]

At a basic level you can go to using a standard .mdb and replacing your local tables with linked tables to the SQL Server and continue to use all your local queries, forms etc as you were before. I'm not sure that you'd even need to rewrite any of your error handling to be up and running in a short space of time. IT won't be an "optimal" method initially, but I believe it will be a far smoother initial migration than attempting to convert an existing .mdb to an .adp project, especially if you aren't currently that familiar with SQL server and don't have an SQL dba to fall back on for advice.

From a SQL Server point of view I'd suggest possibly linking to views rather than directly to the tables from the get go, even if your views start off being simply the equivalent of "SELECT * FROM Tablename". Ideally you really don't want to give your users direct access to the table structure even though this might take you a little more time in the initial setup, it will quite likely save you a lot of pain and trouble in the future.

If you can and you're running on a Windows network with Windows Security enabled on the server, link to your tables using a trusted connection, create a new group or groups in Active Directory, add this group to SQL Server, again possibly in a SQL server role or roles and give those SQL Server roles permission to the views/stored procedures. Then if you need a new user to have access to the db you'll only need to add their windows login to the relevant AD group and give them a copy of the db. It will also prevent any IT wannabe with a bit of time on their hands from linking or looking into your table structure and doing things(tm) or seeing information that you don't want them to.

But from that point on you can start to look at a more leisurely migration to using some of the more powerful functionality and performance within SQL server such as migrating some of your local queries and VBA code to calling Stored procedures rather than relying on local Access queries.