Results 1 to 8 of 8
  1. #1
    cbende2's Avatar
    cbende2 is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Jun 2014
    Location
    Louisiana
    Posts
    370

    Moving split database back end to sql server

    Hello all,

    So the database I have right now is split, with the back end on our server.

    Everything has been going great, however there are several second time delays when loading large forms, queries, etc.

    I've heard that moving the back end to an sql server is the way to go for speed.



    Does anyone have any experience with this?

    Note: All forms and queries are on front end of database. Tables are located on the back end which is sitting on the server.

    How would I go about doing this, I haven't done much research on it yet and just wanted to see what you guy's have experienced with this.

    Thanks a bunch!

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,440
    The speed can be related to several things and moving to SQL, unless you already have SQL installed, may not be the solution due to expense.

    If you are using bound forms and your tables have tons of data in them that is just a limitation of MS access, if you have tons of code running on a particular form that is slow that may be the issue as well and looking at optimizing code (creating modules etc) might provide some relief as well.

  3. #3
    cbende2's Avatar
    cbende2 is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Jun 2014
    Location
    Louisiana
    Posts
    370
    SQL installed?

    I don't have much code in my database, just large forms with several fields.

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,440
    SQL server is a Microsoft product. you can get a free version but I don't know it's limitations, a full version may cost hundreds to thousands of dollars. If you don't know if you have it installed you don't have it installed in all probability. Moving the back end to the server does not mean the back end is on SQL server. Moving the back end to the server would involve installing a version of MS SQL Server, duplicating the table structure of your MS Access database within SQL Server, then migrating all your data to the SQL tables, and finally re-linking the SQL Server tables into your Access database.

  5. #5
    cbende2's Avatar
    cbende2 is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Jun 2014
    Location
    Louisiana
    Posts
    370
    So what is the point of changing from a split end database that has the back end on our server, to moving the back end onto an SQL server?

    Edit:Sorry if that response sounded bitter or attacking, I'm just trying to have some questions answered

  6. #6
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2003
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    2,165
    Quote Originally Posted by cbende2 View Post
    Hello all,

    So the database I have right now is split, with the back end on our server.

    Everything has been going great, however there are several second time delays when loading large forms, queries, etc.

    I've heard that moving the back end to an sql server is the way to go for speed.

    Does anyone have any experience with this?

    Note: All forms and queries are on front end of database. Tables are located on the back end which is sitting on the server.

    How would I go about doing this, I haven't done much research on it yet and just wanted to see what you guy's have experienced with this.

    Thanks a bunch!
    You may find the following link useful: http://www.granite.ab.ca/access/performancefaq.htm
    If this helped, please click the star at the bottom left of this posting and add to my reputation. Many thanks.
    Not everyone can access an accdb, you may reach a broader audience by attaching in mdb format.

    Bob Fitzpatrick

    I have never failed. I have found a thousand ways that do not work!

    Microsoft Community Contributor Award (2011)



  7. #7
    lfpm062010 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Location
    US
    Posts
    415
    Quote Originally Posted by cbende2 View Post
    Hello all,

    So the database I have right now is split, with the back end on our server.

    Everything has been going great, however there are several second time delays when loading large forms, queries, etc.

    I've heard that moving the back end to an sql server is the way to go for speed.

    Does anyone have any experience with this?

    Note: All forms and queries are on front end of database. Tables are located on the back end which is sitting on the server.

    How would I go about doing this, I haven't done much research on it yet and just wanted to see what you guy's have experienced with this.

    Thanks a bunch!
    Maybe this article woudl explain the advantage and disadavantage.
    http://www.expresstechnology.com/sup...er-over-access

    For my own personal experience, I gained some and lost some. This is some of the Advantage and Disadvantage.

    Advantage:
    1) Security
    2) No more 2 GB Limit with Access.
    3) Speed is questionable. Depends on how you do it. Access Query Vs. Pass Thru Query.

    Disadvantage:
    1) Flexible on change structures.
    2) Complexity on Query (mostly on Pass Thru Query).

  8. #8
    cbende2's Avatar
    cbende2 is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Jun 2014
    Location
    Louisiana
    Posts
    370
    Thanks for the replies guys!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Moving an existing Access Database backend into a SQL Server?
    By IncidentalProgrammer in forum SQL Server
    Replies: 9
    Last Post: 09-16-2014, 08:13 AM
  2. Replies: 27
    Last Post: 05-01-2014, 12:21 PM
  3. Replies: 4
    Last Post: 11-27-2013, 09:51 AM
  4. Moving the Back End of a split Access DB.
    By Robeen in forum Access
    Replies: 1
    Last Post: 11-06-2013, 12:09 PM
  5. Moving split database to sharepoint
    By Cindy morgan in forum SharePoint
    Replies: 0
    Last Post: 09-17-2013, 10:09 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums