Linking 2003 ACCESS front end to SQL Server 2005 back end (1 Viewer)

Hello,

I have a front end which is in ACCESS 2003 and want to link it to the back end which is a SQL Server 2005 db residing on a different machine. How would I do this? This is the first time I am doing this process, so I really need help on this.

Thank you very much.

Hi Mapat I am also a learner here but I am guessing as your question is quite a big one without a specific problem you may not get that much of a response.

First the way I did it with SQL Server 2000 was just try it. One concept that took me a while to understand is that you can "link" your tables just like you did when you had a MSAccess front end and back end.

But also you do not need to link the tables you can write VBA to connect to the Server get a RecordSet and ask for information as well. (I use a combination of both) meaning that I use DAO and ADODB.

So to get you started how about trying to find your Server.

On File > Get External Data > Link Tables (just like if MS Access was your backend)

Then you need to change the file type to the bottom selection ODBC

Then you will have to select you data source.

Once again the choices here are also numerous - remember I am not an expert but you can have a DSN file which includes the info for the connection. You can start like that by making a new dsn. Here you need to read and understand the different options.

You will find tutorials on this. Here you will need to find your server and enter user name and password.

Once you have created your DSN you can use it to connect.

Once you get that you will then be given the choice of tables which you can link.

Have a go and see how far you get.

There are 3 ways to doing this.
1. Creating a Link tables connecting with SQL server database
2. Use Upsizing property of MS-Access 2003 (new features in Access 2K3 and later version)
3. Create an * .adp projects and make the application Client /server model where you queries will get converted into the Views,User Defiind functions and Stored Procedures. and forms & Reports, code module will still resides in the Access project file.
Apart from above for data type conversion you may take the help of SQL Serever Integration Services help....
It may not be possible to migration result will be 100%. you have to check the tables properly but you can the result 90% success rate.

There are also some limitation in the migration , for that you can visit http://msdn.microsoft.com/en-au/library/bb188204.aspx and
http://www.microsoft.com/Sqlserver/2005/en/us/migration-access.aspx#3

Hope you will get some idea ....


Thanks,
Dhruvendra

"There is no shortcut of Success"