dcsimg
Page 2 of 2 FirstFirst 12
Results 41 to 50 of 50

Thread: [RESOLVED] Should I upgrade Access Database to SQL Server

  1. #41

    Thread Starter
    Lively Member
    Join Date
    Mar 2009
    Location
    Massachusetts
    Posts
    65

    Re: Should I upgrade Access Database to SQL Server

    Quote Originally Posted by NeedSomeAnswers View Post
    I thought i would chip in with my two pence worth.

    You have been given some good advice on this thread, but i would just re-iterate this.

    Access is a terrible database, and yes i know that with good design you can get it to work perfectly well for a fairly small user base, but that's not the point, its terrible over a network and it slows down your other network traffic as well as your system.

    Also when you can get a completely free version of SQL EXPRESS 2005 why would you use it any more ?

    You big problem DataEmpress is that if you move your back end to SQL Server & leave you front end in Access although this is perfectly do-able i think you would be disappointed in the speed improvements you would get.

    Linked tables are not great, trust me i have had the pleasure of looking after a system where they had done just this (move the back end to SQL Server while keeping the front end in Access) and it is just not a great solution, it works but it's not great.

    The best solution would obviously to have your database as SQL Server Express & then move your front end to a .Net application, however i understand that might be difficult for you to accomplish.

    It might be simpler in the short term to get your "data entry people" to use wired connections. Have you tested if there is any difference in speed if you plug one of the laptops into the network against wireless ?

    There's been much activity in this thread over the weekend, and I'd like to thank everyone. You've all given me a ton to think about. I'm willing to mark this thread resolved any time you're tired of discussing the subject, but selfishly I'm learning so much that I want to keep it going as much as I can.

    When I first started using Access I thought it was the best, most powerful database a person could imagine. I had no idea there was anything else out there. Keep in mind, my background is in literature, not computers. The database I'm talking about, I've been given from program headquarters. All our programs have the same Access database.

    While I can get away with splitting it, I don't know if I could get away with a new front end, even if I knew a .Net language. I'm getting the sense though that the .Net languages are very important to learn. There's little chance we'd be able to get our data entry staff wired due to office layouts and space constraints.

    I have no doubt a wired connection would be much better, since the database does not have any of the speed/performance issues on my computer. I split the database and found performance had improved on the network connection. However, I'm not sure what the difference is between the backend that Access uses when the database is split and a SQL Server Express backend. Is SQL server supposed to be better?

  2. #42

    Thread Starter
    Lively Member
    Join Date
    Mar 2009
    Location
    Massachusetts
    Posts
    65

    Re: Should I upgrade Access Database to SQL Server

    I went to Microsoft's website and typed VisualBasic.Net. One of the search results is Visual Basic 2008 Express Edition. Is that worth downloading to help me learn VisualBasic.Net? Thank you!

  3. #43
    VB Addict Pradeep1210's Avatar
    Join Date
    Apr 2004
    Location
    Inside the CPU...
    Posts
    6,612

    Re: Should I upgrade Access Database to SQL Server

    Yes.. and it's free.
    Pradeep, Microsoft MVP (Visual Basic)
    Please appreciate posts that have helped you by clicking icon on the left of the post.
    "A problem well stated is a problem half solved." Charles F. Kettering

    Read articles on My Blog 101 LINQ Samples JSON Validator XML Schema Validator "How Do I" videos on MSDN VB.NET and C# Comparison Good Coding Practices VBForums Reputation Saver String Enum Super Simple Tetris Game


    (2010-2013)
    NB: I do not answer coding questions via PM. If you want my help, then make a post and PM me it's link. If I can help, trust me I will...

  4. #44
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,039

    Re: Should I upgrade Access Database to SQL Server

    Quote Originally Posted by dataempress View Post
    I'm willing to mark this thread resolved any time you're tired of discussing the subject, but selfishly I'm learning so much that I want to keep it going as much as I can.
    Feel free to do that as long as you still have questions that relate to the thread topic.. but be aware that sometimes you get better responses by creating new threads (as there is less for people who haven't posted yet to read). As yet there hasn't been a reason to do that.

    I split the database and found performance had improved on the network connection. However, I'm not sure what the difference is between the backend that Access uses when the database is split and a SQL Server Express backend. Is SQL server supposed to be better?
    A standard split uses Access again - one 'database' file for the front-end, and one for the back-end. There are a variety of potential reasons this would give a speed improvement.

    SQL Server could well be better still, and it is more likely if you tend to use queries in your front-end rather than full tables.

  5. #45

    Thread Starter
    Lively Member
    Join Date
    Mar 2009
    Location
    Massachusetts
    Posts
    65

    Re: Should I upgrade Access Database to SQL Server

    Quote Originally Posted by si_the_geek View Post
    SQL Server could well be better still, and it is more likely if you tend to use queries in your front-end rather than full tables.
    Could you please explain this a little further? Is this a way of saying to base forms on queries rather than tables, or do you mean something else? I do base the forms on queries because that makes it easier for me to specify the order of records, but it seems like I'm not taking full advantage of all that I can do with this technique.

  6. #46
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,039

    Re: Should I upgrade Access Database to SQL Server

    That is basically it... but if your queries are returning all of the records then you are getting little out of them, and will only get minimal gain from switching to SQL Server.

    It would be better (no matter what database, but particularly a server-based one) to also restrict the amount of rows that are being returned.

    How you should limit the rows depends on your data and application. As a simple example, in an insurance system you are likely to want data for a specific person, so you would allow the user to type in the persons name, and then use that in the query, eg:
    Code:
    SELECT FirstName, Surname, ...
    FROM tablename
    WHERE Surname Like '*Smith*'
    ORDER By Surname
    With your wireless network this kind of thing is likely to make a massive difference to the speed, particularly if you switch to SQL Server.

  7. #47

    Thread Starter
    Lively Member
    Join Date
    Mar 2009
    Location
    Massachusetts
    Posts
    65

    Re: Should I upgrade Access Database to SQL Server

    Quote Originally Posted by si_the_geek View Post
    That is basically it... but if your queries are returning all of the records then you are getting little out of them, and will only get minimal gain from switching to SQL Server.

    It would be better (no matter what database, but particularly a server-based one) to also restrict the amount of rows that are being returned.

    How you should limit the rows depends on your data and application. As a simple example, in an insurance system you are likely to want data for a specific person, so you would allow the user to type in the persons name, and then use that in the query, eg:
    Code:
    SELECT FirstName, Surname, ...
    FROM tablename
    WHERE Surname Like '*Smith*'
    ORDER By Surname
    With your wireless network this kind of thing is likely to make a massive difference to the speed, particularly if you switch to SQL Server.
    So, if I'm understanding correctly it seems like part of my issue is database design. I did not design the database in question, but others that I have designed have been modeled after it. The reason for this is because I just assumed that it was the right way. It's only been within the past year that I've begun to understand anything about db design at all. If there's a better way to do it, I definitely want to learn.

    Let me just briefly explain the set up of the database. There are two forms: one for all who apply to the program and one for those who are accepted. All accepted students are already recorded in the apply to program form, but more in depth information is requried in the accepted form. This information is only for those who are accepted into the program, so I can see why there are two different forms.

    The apply to program form is based on a two table query and the accepted form is based on a three table one. Once each form is opened, there is a combo box at the top that lists all the participants. Currently, all records are returned. In order for only specified records to load would the combo box with the participants' names need to be outside the forms? The combo box is backed by an After Update event using the Set rs = Me.Recordset.Clone formula in VB.

    From what I've described, does this look like an optimal design, or do you see areas it could be improved?

  8. #48
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,382

    Re: Should I upgrade Access Database to SQL Server

    I don't know about anyone else, but I certanly see a lot of room for a lot of improvement. But then I'm thinking about it from a non-Access form perspective. It's been far too long since I've touched an Access form app, I'm not sure where to start, other than advocating a complete redesign. since you're in a wireless environment, I might even go so far as to suggest a web front end instead of a desktop app.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  9. #49
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,039

    Re: Should I upgrade Access Database to SQL Server

    A relatively simple improvement is to not load all of the fields so that you can fill the combo - but instead just load the name field and (if you have one) the ID/PK field. This should mean much less data being transferred (and thus a decent speed gain), depending of course on how many other fields there are.

    You can then use the recordset to load just the relevant record(s) based on the selection. To do that you would replace the line "Set rs = Me.Recordset.Clone" with something like this (with apt field/table names etc):
    Code:
    Dim strSQL as String
      strSQL = "SELECT field1, field2, ... " _
             & "FROM tablename " _
             & "WHERE IDField = " & Me.Recordset.Fields("IDField").Value
      Set rs = New ADODB.Recordset
      rs.Open strSQL, Me.Recordset.Connection

  10. #50

    Thread Starter
    Lively Member
    Join Date
    Mar 2009
    Location
    Massachusetts
    Posts
    65

    Re: Should I upgrade Access Database to SQL Server

    I thank everyone for their help. You all rock!

Page 2 of 2 FirstFirst 12

Tags for this Thread

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width