Results 1 to 10 of 10
  1. #1
    crowegreg is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Posts
    398

    Convert an Access 2010 DB to SQL Server DB

    I have an Access 2010 DB that has a front end and a back end. I've converted the back end to SQL Server. I've created an ODBC connection to link to the SQL tables. On my front end DB, I have extensive code within modules and behind the forms.


    If I try to use the existing code in the front end DB, the speed is terrible. I'm assuming it is because of the network traffic.

    On the SQL Server, do I need to create stored procedures similar to my code behind the form and the code within modules?

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I suspect the answer is "it depends". Generally speaking I'd expect speed to be about the same. If yours slowed down, it does sound like it's now having to move a lot of data over the network that it didn't before. There are a number of tools available to speed things up, but the choice probably depends on the specific issue. Options include stored procedures, pass-through queries, views and functions. Basically you want to either force the processing to the server and/or minimize the data that has to come over the wire.

    If you want to post an example of a process that slowed down, we can look at how to fix it.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    crowegreg is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Posts
    398
    Thanks for replying.

    Here is my code that appends data from a local table to a table on the SQL Server.
    Code:
            strSQL = "INSERT INTO dbo_PM_TEMP ( THRU_DATE, Process_Level_ID, Dept_ID, Position_ID, Position_Title, New_Position_Status, Job_Salary_Grade, Job_Code, Location_ID, FLSA_Status,"
            strSQL = strSQL & " Expense_Account_Unit, Shift, Manager_Supervisor_Number, Manager_ID, Empl_ID, Last_Name, First_Name, Middle_Initial, Vacant_Position_Incumbent_ID,"
            strSQL = strSQL & " Vacant_Position_Incumbent_Name, Adjusted_Hire_Date, Hourly_Rate, Assigned_FTE )"
            strSQL = strSQL & " SELECT " & Chr(34) & dteNewThruDate & Chr(34) & " AS [Date], Left([Company],5) AS Process_Level_ID, Mid([Cost Center],6,4) AS Dept_ID, Sheet1.[Position ID],"
            strSQL = strSQL & " IIf(IsNull([Job Profile]),"""",Right([Job Profile],Len([Job Profile])-6)) AS Position_Title, Sheet1.[Position Status],"
            strSQL = strSQL & " IIf(IsNull([Default Compensation Grade]),"""",Mid([Default Compensation Grade],InStr([Default Compensation Grade],""-"")+1,2)) AS Job_Salary_Grade,"
            strSQL = strSQL & " IIf(IsNull([Job Profile]),"""",Left([Job Profile],5)) AS Job_Code, Sheet1.[Location ID], IIf([Exempt?]=""yes"",""Exempt"",""Non-Exempt"") AS FLSA_Status,"
            strSQL = strSQL & " Mid([Cost Center],6,4) & Mid([Cost Center],3,3) AS Expense_Account_Unit, IIf(IsNull([Work Shift]),0,Mid([Work Shift],7,1)) AS Shift,"
            strSQL = strSQL & " Left([Position],10) AS Manager_Supervisor_Number,"
            strSQL = strSQL & " IIf([Manager] Like ""*(On Leave)*"",Mid([Manager],InStr(InStr([Manager],""("")+1,[Manager],""("")+1,InStr(InStr(InStr([Manager],""("")+1,"
            strSQL = strSQL & "[Manager],""(""),[Manager],"")"")-InStr(InStr([Manager],""("")+1,[Manager],""("")-1),Mid([Manager],InStr([Manager],""("")+1,InStr([Manager],"")"")"
            strSQL = strSQL & "-InStr([Manager],""("")-1)) AS Manager_ID, Sheet1.[Employee ID], Sheet1.[Last Name], Sheet1.[First Name], Sheet1.[Middle Name], Sheet1.[Vacated Incumbent ID],"
            strSQL = strSQL & " IIf(IsNull([Vacated Incumbent Name]),"""",Left([Vacated Incumbent Name],InStr([Vacated Incumbent Name],""("")-2)) AS Vacant_Position_Incumbent_Name, Sheet1.[Hire Date],"
            strSQL = strSQL & " Sheet1.[Hourly Rate], Sheet1.FTE"
            strSQL = strSQL & " FROM Sheet1"
            CurrentDb.Execute strSQL

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    How many records would it normally append, and how long does it take? If you create a regular query with the SELECT portion, how long does it take to run (and return all records, not just show the first page). There are a lot of little functions in there that might slow it down, but the only difference with SQL Server would be moving them across the network, so this may not be the best place to gain speed.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    crowegreg is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Posts
    398
    There are 1674 records at this time. It's taking about 3 minutes.

    Before I import these records, I'm deleting the existing records. When I run a delete query, it takes about 1.5 minutes.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Wow. The delete query should be virtually instantaneous, because nothing should happen locally (I'm assuming you're sending "DELETE * FROM TableName"). Do you have access to the server? If you run the delete from SSMS, does it take as long?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    crowegreg is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Posts
    398
    You are correct on the syntax for the delete query. If I run the query in SSMS, it is instantaneous.

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You can try making that a pass-through query, but it really should work as-is. Simple SQL like that Access should not try to process locally, it should just send the SQL to the server to execute. Even the append should be much faster. I've got processes that copy records from the server to local tables, and it will do 20-30k records in a few seconds (I'm populating local tables with lookup data so it doesn't have to query the server as much for an app that runs at an off-site location over a VPN).

    Is the network okay, or do you feel the problem might be there?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    crowegreg is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Posts
    398
    Looks like I need to check with the IT DBA. I just performed a make table query, taking data from a linked table to a new table in the Access Front end DB, and it was instantaneous.

    I tried appending different quantities of data, and once it reaches 500 records, the amount of time jumps drastically.

    Thanks for your assistance!!

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Odd that 500 records would cause that. Let me know what you find out; now I'm curious.

    Feel to post other issues that might be more amenable to speeding up.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Convert linked access 2003 file to access 2010
    By abhijeetkadam in forum Access
    Replies: 1
    Last Post: 11-13-2013, 12:44 PM
  2. convert old db to Access 2010
    By Jean S in forum Access
    Replies: 1
    Last Post: 04-03-2013, 06:55 AM
  3. Replies: 9
    Last Post: 05-30-2012, 01:23 AM
  4. Convert Access 07 ADO code to SQL Server Express 2008
    By jrdnoland in forum Programming
    Replies: 8
    Last Post: 04-14-2010, 05:57 PM
  5. Convert query sql server for Access
    By webtony in forum Queries
    Replies: 0
    Last Post: 06-23-2009, 02:46 AM

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