Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2004

    Red face Unanswered: Access to SQL conversion!

    Hi Everyone,

    To start off, can I just say that I know very little about SQL databases but I have a MS access database which we are outgrowing and I think we will need to make some changes. I was wondering if some one could offer some advice. There are many programs available for upsizing to SQL but I presume these are for the back end only, is this correct? Is upsizing a bad idea or a very difficult one?

    I was also wondering about keeping my Access front end and connecting it to a SQL back end, is this a good idea?

    Any alternative suggestions would be appreciated.



  2. #2
    Join Date
    Nov 2002
    It depends on a lot of things....

    It depends how the app is written....more than likely your form controls are all open record sets...

    What's your data access model?

    That's make access front end to sql server crawl....

    How normalized is your data, if you have to rewite it'd be good to have a good data model...

    How much data are we talking about? How big is Access in mb?

    How many users?

    Do you know how much sql server costs?

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2004
    How 'bout Visual Basic on a SQL Server back end? We are converting, albeit at a snails pace, our processes using Access backend to SQL Server. This is a different(another job) than the other issue I posted on re: Read Only in SQL Server Stored procedures. Here at least, I have full privileges, even updates on table than my a.m. job. Is there a good tutorial page on VB with SQL Server? I know both to some extent though my VB is rusty.


  4. #4
    Join Date
    Jun 2003
    Longmont, CO
    I have upgraded Access apps to SQL Server a few times and been surprised how much the built-in Upgrade Wizard can do correctly for you. I'd give it a try. Note that you don't want to convert to an Access 'Project' file if you need to have front-end tables as well as a back-end database. You can't use front-end tables in the .adp files.

    Depending on the complexity of your relationships in the DB you can get almost everything converted over with the Wizard. Some basic testing will show you where things flopped. You won't have too much trouble figuring out how to re-create things in SQL Server--it's very user-friendly.

    If you will keep your front end in Access I highly recommend the enormous Access 2000 Developer's Handbook, Volume 2: Enterprise Edition (Litwin, Getz & Gilbert) Hopefully they have a 2002 version out by now but the 2000 version saved my you-know-what many times and gives great info on how to work with SQL Server from Access.

    We have several large production apps running in Access to SQL Server. In terms of SQL Server and Access playing nicely, they generally do. Using the upsizing wizard will help ensure that the back end is set up to talk to Access properly.

    Good Luck.
    Last edited by Fly Girl; 04-26-04 at 16:59.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts