locked
Access 2003 db to SQL Server 2008 db RRS feed

  • Question

  • Hi,

    A team in our company has Access 2003 database with front end and back end. They want us(another team) to host their backend in our SQL Server 2008 R2 database. What are the possible solutions to do it? The Access 2003 team will be using the front end to enter their data into their dataase. Since the front end is used by many people for data entry purpose, I m thinking of creating some sort of web service that would connect to their Access and every write to tables would also write to our SQL server tables that we host. I m not quite sure if its doable?  If its doable then can anyone share similar experience? or is there any better solution to do it?

    Thanks.

    Wednesday, August 8, 2012 10:49 AM

Answers

  • Hello,

    If they have a front end created with Access, as I explained above the changes needed are not much. It is an easy migration. However, with a VB application the connection strings and maybe programming code should have to suffer some changes.

    Yes, you can create an Access Project in Access 2003, but I have tested Access projects 2003 with SQL Server 2000 databases only.

    I would recommend you to change the front end as I mentioned above: changing the source property of forms and reports. This will allow users to keep working as usual.

    After that, start creating an Access project as your new front end and deliver it on stages, deliver forms and reports to specific departments. Your actual front-end and the Access project can coexist until you finally deliver the new front end to all departments in the organization.

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    • Marked as answer by Maggie Luo Thursday, August 16, 2012 4:28 PM
    Thursday, August 9, 2012 8:53 PM

All replies

  • Hello,

    You can do this migration an easy one. You can migrate all tables to SQL Server, and on the front end application create linked tables (using ODBC) to all tables that reside now on SQL Server.

    Change forms and reports on the front table to use the linked tables in SQL Server. All forms and reports will stay the same.

    Hope this helps.

    Regards,
    Alberto Morillo
    SQLCoffee.com

    Wednesday, August 8, 2012 11:04 AM
  • Hi Alberto,

    Thanks for your prompt response. I haven't played much with Access db before. However I m familiar with SQL.

    If you could elaborate on 'how do I migrate all my access 2003 tables to SQL Server", that would be a good start for me. The next thing is how do I create linked tables using ODBC to all the tables that now reside in SQL Server? and how do I change forms/reports on the front table to use the linked tables in SQL Server?

    I have access 2010 and SQL Server 2008 on my machine. I want to play in my environment to  give a nice little demo that Access people could use it or I could help them implement this. Your help is greatly appreciated. Thanks.

    While researching, I found this post..http://khanrahim.wordpress.com/2010/02/19/using-upsizing-wizard-in-ms-access-2003-to-link-tables-with-ms-sql-server-2005/

    Do you mean something like that? I will try the above post too. But this post doesn't say about changing forms/reports on the front end to use the linked tables in SQL Server? Any ideas will be helpful to me. Thanks again.

    Wednesday, August 8, 2012 11:11 AM
  • Hello,

    That article is exactly what you need to create the linked tables.

    After you finish the process explained in the article, please examine the properties of forms and reports and make sure on the source property they use a linked SQL Server table instead of an Access table.

    That’s it. Quick and easy.

    Hope this helps.

    Regards,
    Alberto Morillo
    SQLCoffee.com

    Wednesday, August 8, 2012 11:44 AM
  • Hi Alberto,

    I did that tutorial and it seemed to work. That's fine. But our Access 2003 team would like to be able to connect to our SQL Server database hosted in our server to write to the tables in our SQL Server. Since they have access front end application already, what are the avenues that our team can provide them so that they can connect to our SQL Server to write into their tables?

    Thanks.

    Wednesday, August 8, 2012 7:42 PM
  • Hello,

    As I mentioned on my previous post, just change the source attribute (on the properties) of the forms and reports to point to the linked tables you just created. Those linked tables reside on SQL Server.

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    Wednesday, August 8, 2012 8:10 PM
  • Hi Alberto,

    I beg your pardon if I was not accurate in explaining what I want.

    Let's say access 2003 team has an application written in VB code to develop access front end, where a user typically logs in to get into their system. can we provide some sort of method for them so that they could connect to their db in our SQL server and enter data there? I m not worried about forms and reports.

    Just want to know if there's a way to let them connect to our SQL server, from their Front end system like using odbc, adodb, connection string? Then both the teams can continue on checking if they could establish a connection to our SQL backend from their front end application. Thanks.

    Wednesday, August 8, 2012 8:56 PM
  • Hello,

    Is the application written on VBA (not VB)? Is it a Microsoft Access front end?  In the case they have an Access front end that uses VBA, they just have keep using the same Microsoft Access back end, since the Access back end has all tables linked to SQL Server. They do not have to make changes on the VBA programming. Just make sure the Access back end has the same name of tables expected by the front end application.

    Later they can make the front end application an Access Project.

    http://office.microsoft.com/en-us/access-help/create-an-access-project-HA010167953.aspx

    You will need to create a login in SQL Server and provide access to the database.

    http://msdn.microsoft.com/en-us/library/ms189751.aspx

    http://msdn.microsoft.com/en-us/library/ms365345.aspx

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    Thursday, August 9, 2012 11:57 AM
  • Hi Alberto,

    I sincerely appreciate your help in promptly providing me answers to my 'new-bie' questions. Thanks again for your time.

    Ok, what difference does it mae if its a Microsoft Access front end written in VBA or if its a separate VB application currently using Access 2003 as a backend? Looks like theirs is Microsoft Access front end (with VBA).

    Can we create an access project in MS Access 2003?

    Thanks.




    Thursday, August 9, 2012 2:02 PM
  • Hello,

    If they have a front end created with Access, as I explained above the changes needed are not much. It is an easy migration. However, with a VB application the connection strings and maybe programming code should have to suffer some changes.

    Yes, you can create an Access Project in Access 2003, but I have tested Access projects 2003 with SQL Server 2000 databases only.

    I would recommend you to change the front end as I mentioned above: changing the source property of forms and reports. This will allow users to keep working as usual.

    After that, start creating an Access project as your new front end and deliver it on stages, deliver forms and reports to specific departments. Your actual front-end and the Access project can coexist until you finally deliver the new front end to all departments in the organization.

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    • Marked as answer by Maggie Luo Thursday, August 16, 2012 4:28 PM
    Thursday, August 9, 2012 8:53 PM
  • Check out DBConvert from MS Access to ASP.NET + MS SQL . It is able to convert MS Access tables, queries and forms to MSSQL+ ASP.NET

    But VBA code should be converted manually.


    • Edited by DMSoft Wednesday, August 22, 2012 9:58 PM
    Wednesday, August 22, 2012 9:54 PM