Results 1 to 10 of 10
  1. #1
    data808 is offline Noob
    Windows 10 Access 2013 32bit
    Join Date
    Aug 2012
    Posts
    727

    General Questions about queries and migration to an SQL server

    I was just wondering if there were simple ways to make queries in my frontend/backend database? We roughly have 20-30 users on it at once. I have multiple backend tables to handle certain types of data to be recorded. We been using this since 2014 and over time I noticed the queries are getting slower. It takes a while for my split forms to load. I did some compact and repairs to the backend files and also the frontend as well and that seemed to have helped. Then I also archived a few years of records and that also helped a bit too but still not as good as I would have liked. Is there any other simple things I can do to speed it up that I have missed?

    My other question has to do with the amount of users. We may be looking to increase the amount of users to be on this database simultaneously and I know Access has its limitations with this. I did some research on the subject and one suggestion was to migrate over to an SQL server. I know nothing of that stuff and was wondering if it would be difficult to do or expensive? Also, is development for designing the frontend/backend files going to be affected? Or would I just continue making design changes the same way as I have been even if the database is now in an SQL server?



    Any help is appreciated. Thank you.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    we have each PC assigned to its own frontend (on server,so they can always be updated)
    No delays in data load/save.

    the backend is connected to SQL with the same names as they were in the Access BE.
    Some have both so we can switch.
    tblCustomers-Acc
    tblCustomers-Sql
    tblCustomers

    a manager can click a button to switch from Sql to Access backend. The source (-Acc or -Sql) is copied to the target table.

  3. #3
    data808 is offline Noob
    Windows 10 Access 2013 32bit
    Join Date
    Aug 2012
    Posts
    727
    Thanks for the reply. Just trying to understand. What are the benefits of switching between Access and SQL for you folks? Why would you want to do that?

    My main reason for transferring everything to SQL is so that I can have hundreds of users logged in at once on this one database we have. That alone is enough for me to stick with SQL forever, never looking back to Access again. Lol. I would still like to develop updates in Access for the frontend though, so your setup sounds great for that.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Is there any other simple things I can do to speed it up that I have missed?
    To name a few

    1. have forms open with zero or just one or two records (i.e. don't base forms on tables, base them on queries, determine what the user wants to see before opening the form e.g. 'customers with 'Smith' in the name' and then apply as criteria, not as a filter - sounds like if you are using split forms, you are basing them on tables
    2. ensure fields regularly used for joins, sorting and filtering are indexed
    3. avoid using domain functions (dsum, etc) in queries
    4. look at using aliased tables rather than using subqueries (not always possible)
    5. Avoid using the initial * when searching for matches (it negates the use of indexes). Instead, train the users to enter it when required. Most times they know what the initial character will be, so simplistically - that enables the use of the index and means that instead of returning say 20,000 records, it will return (again simplistically) 1/26th or around 800 records - much less data to bring over the network.

    Re sql server, sql server express is free and has most of the functionality of the full version. It can comfortably handle more users than ACE or JET (the free db that comes with Access) and I believe it can hold around 10gb of data rather than the 2Gb limit of ACE or JET
    Migrating is fairly straightforward in that you just need to recreate your tables and relationships in sql server then link to them
    However migrating will not result in improved performance without a fair amount of work, it will most likely be worse. To achieve benefits you will have to undertake all of the above and perhaps use some of the sql server built in functionality that does not exist in Access (or can only be achieved by using VBA)

    For best performance you need to mimic how a website works - it doesn't list all the products for you, it give you choices based on something - type, colour, size, whatever, then fetches a reduced number of records.

    What are the benefits of switching between Access and SQL for you folks? Why would you want to do that?
    Main benefits are improved data security, larger storage, more users (although I've had 70 odd concurrent users using ACE), performance is the bottom of the list

  5. #5
    data808 is offline Noob
    Windows 10 Access 2013 32bit
    Join Date
    Aug 2012
    Posts
    727
    Thank you so much for this wonderful explanation. You got me thinking about trying to show the user what you think they want to see instead of showing them everything like I do in my split forms. Is there a way to show only the most recent 100-200 records on loading the split form? Then from there the user can enter criteria to look up what they want? If so, how would I go about doing that?

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Have a separate search form to gather information from the user to build a criteria string - such as customer name either they can type something or use a combo - for large datasets I usually don’t do anything until the user has typed 3 chars, i or wait 1/2 second after they stop typing

    Search with terms like ‘search form’, you’ll find plenty of examples for different data types and ways to do it

  7. #7
    data808 is offline Noob
    Windows 10 Access 2013 32bit
    Join Date
    Aug 2012
    Posts
    727
    I actually have another setup that does that where they enter criteria into one form and then when they click a button it takes them to a split form to show what records match the criteria they entered. I will try to develop more on that method of doing queries but is there also a way to open a split form to show only a few records then as they type into fields at the top of the split form and click the filter button, it will begin to show them what they want to see? I have multiple text boxes at the top that they can type their criteria into and as they click the filter button it will start to narrow down the results. The more criteria they provide the more minimized their search results become.

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Sounds like you are talking about FAYT - find as you type

    This is a simple bit of code I use for a combo. Gets more complicated if you want to use in a textbox to apply to a form because of losing focus.

    Code:
    Private Sub cboCustomer_Change()
    Static Txt As String
        
        With cboCustomer
            .RowSource = Replace(Replace(.RowSource, "'*" & Txt & "*'", "'*" & .Text & "*'"),"Not ","")
            .Dropdown
        End With
        
    End Sub

    two things you need to do

    1. set the combo Auto Expand property to No
    2. include in your rowsource a criteria for the field in the combo column(1) something like 'SELECT ID, Name FROM myTable WHERE Name Not Like '**' ORDER BY Name'

    This will not load any data into the combo until the user has entered a character, when they do, the replace function removes the Not

  9. #9
    data808 is offline Noob
    Windows 10 Access 2013 32bit
    Join Date
    Aug 2012
    Posts
    727
    Quote Originally Posted by CJ_London View Post
    Sounds like you are talking about FAYT - find as you type

    This is a simple bit of code I use for a combo. Gets more complicated if you want to use in a textbox to apply to a form because of losing focus.

    Code:
    Private Sub cboCustomer_Change()
    Static Txt As String
        
        With cboCustomer
            .RowSource = Replace(Replace(.RowSource, "'*" & Txt & "*'", "'*" & .Text & "*'"),"Not ","")
            .Dropdown
        End With
        
    End Sub

    two things you need to do

    1. set the combo Auto Expand property to No
    2. include in your rowsource a criteria for the field in the combo column(1) something like 'SELECT ID, Name FROM myTable WHERE Name Not Like '**' ORDER BY Name'

    This will not load any data into the combo until the user has entered a character, when they do, the replace function removes the Not

    Sorry not looking for suggestions as I type. I basically have a bunch of text boxes at the top of a split form. This split form will load all 50k records and counting because everyday dozens of records are entered. I basically want the user to be able to type in whatever fields they want to fill out such as customer name, phone number, date, etc...but not have any of those fields a requirement to do a filter. Then once they have their criteria all filled out, they click a filter button and will sort the datasheet at the bottom to show the results that match their criteria. But most of all, to speed things up, I initially want this split form to open up with only a couple hundred records showing. The most recent records would be best as that would be the most likely that a user would be wanting to look at. However, once they start typing in the criteria and clicking the filter button, it will also pull up records that were not part of the 200 records showing when the split form opened. Does that make sense?

    So basically I would just like to know how to make it so that the split form opens with 200 of the most recent records entered and then if the user wants to search by typing in criteria, it will be able to show whatever records the user wants by looking for those records that match the criteria even the records that are not part of the 200 that showed when the form was first opened.

  10. #10
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    to get the latest 200 records, order by date desc and select top 200

    Ensure the date field is indexed

    sql would be something like

    select top 200 * from mytable order by mydate desc

    Could be wrong but not sure this would be any faster than loading the whole table since it has to parse the whole table to determine the top 200 - try it and see

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

Similar Threads

  1. Migration of sharepoint to SQL server
    By meena16 in forum SQL Server
    Replies: 0
    Last Post: 01-14-2020, 01:51 AM
  2. General Questions
    By Dave14867 in forum Access
    Replies: 14
    Last Post: 10-22-2018, 07:06 PM
  3. Access To SQL Server Migration
    By Rxp in forum Access
    Replies: 6
    Last Post: 05-07-2012, 05:50 PM
  4. A few general questions...
    By Daryl2106 in forum Access
    Replies: 3
    Last Post: 02-29-2012, 09:57 PM
  5. Querry general questions
    By newtoAccess in forum Access
    Replies: 2
    Last Post: 04-04-2011, 06:56 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
  •  
Other Forums: Microsoft Office Forums