Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2008
    Posts
    4

    Wink Hi...need help moving Access to SQL

    Hello All,
    I just joined this forum for your expert advice and to use as my support for the task ahead of me.

    I currently use MS Access for all my data on the local end, and it is converted for MySQL on the server. I want to migrate to use a SQL server on the local side, and I am wondering the best route to go. I do however want to keep access to use as my front end for the data input as we have put a great amount of time and effort into customizing it.

    I guess my question is what would be the fastest and most efficient way to access the data in the MySQL database on the server, and what would provide the best results in our day to day operations. I would like to pull only the data relevant to each query and not have to load complete tables. I aslo do not want to be weighted down with slow ODBC drivers. I need speed.

    So, that being said, does anyone have opinions or good advice on how I should go about this and what would be the best SQL tuse on the local end.

    Thanks very much!
    Fiona

  2. #2
    Join Date
    Mar 2008
    Posts
    2

    RE: Help moving Access to SQL

    That depends on a couple of things, Fiona. You've got options ranging from embedded scale with SQL Server Compact Edition through Express Edition and on up to Workgroup Edition, Standard Edition, etc. If it's an occasionally connected scenario, Sync Services for ADO.NET would be a platform option to keep the edge databases in sync with the primary.

  3. #3
    Join Date
    Mar 2008
    Posts
    4
    Thanks David! I had not even thought about which edition I should look at. I guess I do need something pretty large scale. It would be used and connected to pretty much all day by about 20 or so people. There is a lot of data and I would want to be able to pull just what I need at the moment and not have to load the entire tables, which is very cumbersome.

    I'm not exactly sure which way I should go, but an advice is greatly appreciated.
    Thanks again!

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    why not keep your existing SQL.. if you have spent all that time and effort then keep the existing JET SQL that is bundled with Access.

    otherwise I think a significant re-write is in order, and cut out all the local tables and talk direct to the remote server.

  5. #5
    Join Date
    Mar 2008
    Posts
    4
    I plan on keeping my existing MySQL on the live production server. My migration will be from Access (all my data on the local end is in Access) to a bigger SQL server on the local end. It has just grown way too large to stay with access. I have customer service reps accessing the data throughout the day. We also input a lot of data locally and then upload it to the MySql server many times throughout the day.

  6. #6
    Join Date
    Mar 2008
    Posts
    2
    If you're looking to keep a local store in sync with another remote DB, you might want to consider using SQLCE on the client and use Sync Framework to keep your local cache up2date: http://msdn2.microsoft.com/en-us/sync/default.aspx. There are some limitations with SQLCE, so you'll want to evaluate carefully before leaping into a development effort; SQL Server Express Edition is more full-featured than SQLCE (and still the same price ;-).

  7. #7
    Join Date
    Mar 2008
    Posts
    4
    Thanks David. I will look into it.

Posting Permissions

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