Access Backend integrated with SQLServer

Posted on 2006-05-17
Last Modified: 2010-04-23
Hello Experts,
Wonder if I could get some of your opinions.  I have a friend of mine who has two systems.  One is system is based off of an Access Back-end and is currently used by some of the staff for day-to-day operations

They are also in the process of having a website developed by an outside company.   Since some of the staff is very comfortable with the access program and to save on costs, they want to keep the internal application the way it is, instead of redeveloping the app in SQLSvr.  

But, they would like to be able to integrate the Access data into the website's back-end which will be SqlServer. So, my idea was to create an in-between application, that would post the day's updates into some tables in the SQLSrvr back-end.  But this would require that the app be able to use pull from both the Access Database and the SqlSrvr database in the same event, which I know requires two different types of DataAdapters/Datasets.  

So, if anyone has any experience with this and knows it can be done, or has a better idea, I'd appreciate any input.


Question by:Marc333
    LVL 5

    Accepted Solution

    From past experiences with related options, we found it easiest to copy the access tables to the sql server, then make link tables in the access program (name them the same as the old tables) and have each link table point to the new table on the sql server.  That way you let sql server handle everything, but the front end of the app is still in access for the users comfort.  We actually saw a huge performance increase after going to sql server for backend and just using link tables and keeping the access app.  That will also let your web have only one connection.  Worth looking into.

    LVL 3

    Author Comment

    Thanks for the input, exactly what I was looking for.  


    By the way, is that for Arkansas?
    LVL 5

    Expert Comment

    of course it is.... :)
    LVL 3

    Author Comment

    Well, have a Roll Tide weekend..  ;)


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Suggested Solutions

    This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
    Article by: Kraeven
    Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now