Results 1 to 8 of 8
  1. #1
    SteveApa is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2015
    Posts
    52

    Moving BE to SQL Server overview help


    Hi all.

    I have been using access for 20 yrs and skilled in vba and dao. I am moving my backend to sql server from Sharepoint lists - I work for a large corporation and IT hates me. I am an engineer that learned to do what I want without IT. I used sharepoint lists for an app I did because the use is global - that was the only way I new how to do it at the time. The db is all form driven. Finally, I got IT to give me a database on a shared sql server. I have a colleague that has some sql server experience so she will be the DBA for all of my stuff. So, to convert my access db to use sql server tables, I have a couple questions...

    From what I read, I need to move from dao to ado. I did a quick test... exported my sharepoint lists (12 tables, approx 2000 lines each with 25 columns) to the server, named the tables the same and linked them. Ran the db. The dao rs.findfirst is very slow - this is why I assume I need to move to ado. My question... My standard designing mode was to create an access query (not vba, not sql) to query the sharepoint list. Then I dao the access query in vb to do my recordset manipulation. When converting to ado for the sql server, do I still do the same thing or do I connect the recordset directly to the sql server table and not go thru the access query? which method is faster? Which method is easier to develop? I assume that I can still have my vb ado recordset point to an access query that uses the sql server table.

    Any other advice would be helpful

    Thanks.
    Steve

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    8,279
    you dont need dao ,nor ado. Everything can be connected inside access.
    Link your tables from SQL server, then just run queries as if it is a local table.

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,284
    I use SQL Server most of the time and still use DAO for recordsets and such. I think ADO is theoretically better optimized, but I haven't felt a need to switch.

    I don't use FindFirst much, I open the recordset on the desired record(s). As a general rule I start with linked tables and Access queries, then move to pass through queries or other SQL Server based solutions when performance isn't up to snuff.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    RayMilhon is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,040
    I've moved a few Access Tables to SQL Server. Here's a couple of recommendations. If your queries don't have any criteria that need to be changed or entered at runtime move them to SQL Server Views. When running queries in Access on SQL Server tables the query is processed in 2 steps. The first retrieves all of the data from sql server the second is the application of the criteria so if your Dataset without the criteria returns a lot of data Access will bring all of that data across the network to your local system and then apply the criteria. It's better to run that as a view so only the data you need comes across the network. For example you have 5 years worth of data in you database you run a monthly report. With the query in Access you will have all 5 years worth of data cross the network and then Access will apply the criteria for last month. It's faster if you only bring last months data across the network. What I've done is create a function called startdate with an integer parameter and a function called enddate with an integer parameter. Then create a view in SQL Server Select somefields from sometables where adatefield >= startdate(1) and adatefield <= enddate(1)

    The startdate would come back as May 1, 2020 and the enddate would come back as May 31, 2020 changing the number from 1 to 12 for the startdate would return June 1, 2019 through May 31, 2020

    All of the processing is done on the server and you only get the few records you need.

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,284
    Quote Originally Posted by RayMilhon View Post
    When running queries in Access on SQL Server tables the query is processed in 2 steps. The first retrieves all of the data from sql server the second is the application of the criteria so if your Dataset without the criteria returns a lot of data Access will bring all of that data across the network to your local system and then apply the criteria.
    I don't believe this is accurate, and certainly doesn't seem to be in my experience. I can run an Access query with parameters against a linked table with 5-10 million records and get the result instantaneously. My understanding is that Access (or perhaps more accurately the ODBC driver) is pretty good at passing the SQL back to the server to process when it can. If you have something in the criteria that SQL Server doesn't understand, then it would behave as you describe.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    RayMilhon is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,040
    Paul Maybe it hasn't been your experience but it has been mine. I've run queries in Access with a link to SQL Server the queries took hours to process. Copied the SQL from Access to SQL Server Management Studio made some syntax changes the query took under 5 minutes. Maybe it's caused by the Network, maybe by the complexity of the commercial Database I'm querying from. I don't know but that's been my experience.

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,284
    I suspect you ran into the situation I described, where Access couldn't translate something in the query (criteria, join, etc). One or more of the syntax changes you made in SSMS couldn't be made by Access. Leigh Purvis (highly regarded in the Access community) has a FAQ available here

    http://www.utteraccess.com/forum/Beg...-t1732935.html

    where he says in part:

    The misconception that they always pull over an entire server table's content and then filter on the client is completely false. Jet, in its SQL request wrapping role, is much more intelligent than that.


    He follows with some discussion of how/why it happens. It appears that I was wrong about the ODBC driver doing the work, it's JET/ACE.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    NoellaG's Avatar
    NoellaG is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    655
    If you creater the query as a pass-through query it is completely processed and executed on the server. It also enables you to take advantage of T-SQL which has a lot more posibilities than Access SQL.
    In the situation where you have several years of data and, in most queries, you need only the data of the past year, you can create a view on SQL server filtering and joining the needede data up front on SQL server. Than you can link this view to access just as you do with a table.

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

Similar Threads

  1. Moving backend to SQL Server
    By Jayshe9999 in forum SQL Server
    Replies: 7
    Last Post: 02-27-2019, 09:48 AM
  2. Replies: 2
    Last Post: 01-29-2019, 07:48 AM
  3. Moving queries from Access to SQL Server
    By jaryszek in forum SQL Server
    Replies: 10
    Last Post: 03-29-2018, 11:25 AM
  4. Replies: 2
    Last Post: 07-10-2015, 07:10 AM
  5. Replies: 7
    Last Post: 01-09-2015, 12:15 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 - Senior Forums