Migrated backend to SQL, now Im crushed! (2 Viewers)

[gerrythefish]

My access 2013 database is tiny - front end 16MB, Backend 30MB and will probably grow by about 30MB per year. 140 users, some remote, but typically about 10 concurrently. 120 tables (should be well normalized)

Front end is on users Desktop, which allows me to do a lot of data manipulation on the fly (via queries) that I wouldn't dare do over a network.

Primary reason to move to SQL now is access split database doesn't work well (at all!) over VPN.

After migration (SQL 2012) linked tables open very fast. Access queries with parameters are very slow.

I know that I can move queries to Stored Procedures.

I shudder at the thought of disconnecting the data - ie read from SQL, make changes, write back to SQL in a multiuser environment, and somehow deal with conflicting updates from different users - isn't that what SQL is supposed to do! :banghead:

Users edit records in datasheet view and forms, which can have multiple linked subforms.

I need help planning on what moves to SQL stored procedures/views and what stays in access. And how to read these (vba or access queries)

 

[GinaWhipp]

I have never had to switch to Stored Procedures and I have several databases set up using your scenario. So, let's see if we can figure out what's going on.

1. I never put the Frontend on the Desktop, presents problems all in it's own. So, step one is move it to a Folder and put the Shortcut on the Desktop.
2. Make sure Compact on Close and Track name Autocorrect are both unchecked
3. Make sure all BIT fields have a Default Value
4. Check this list for other gotchas (http://regina-whipp.com/blog/?p=371)

Did that help? If not we're here to see what else could be the problem.

 

[Rx_]

The VPN is not a great place for data connections.
Was SQL Server Native Client 11.0 used for the ODBC connection for DSN-Less connections? Search this site for reasons why this woudl be prefered.
It is free, would have to be installed on each client PC.

With the number of users - and concurrent connections - the smal office Citrix should be considered. The Access application gets installed on one server, client PC would not require MS Access installed. They also wouldn't need to keep up on the Service Packs or other maintenance. It wouldn't matter if clients had the same version of Windows, Mac, or other computers.
The network band width would drop hugely. The network security would go up many times. The overall Technical desktop and network maintenance in hours would drop 90% once it was set up.
Users would see no deleay since Citrix would reside very close to the SQL Server.

Search this site for the Bat file that allows each user loggin in to copy the front-end into a folder and launch. Each user needs to have thier own front-end - linked to a common SQL Server Back end.

 

[gerrythefish]

Lots of great info from your blog - thank you. See below.

I saw no noticeable improvement from these. Some improvement from using native sql server 11 connection - but still way too slow. From reading your blog, my forms have several bound comboboxes that are basically lookups. Im guessing that's impacting the form loading time. How to deal with those efficiently? (deleting the combobox is not an option)

I have never had to switch to Stored Procedures and I have several databases set up using your scenario. So, let's see if we can figure out what's going on.

1. I never put the Frontend on the Desktop, presents problems all in it's own. So, step one is move it to a Folder and put the Shortcut on the Desktop. (I moved it with no change, what are the problems?)
2. Make sure Compact on Close and Track name Autocorrect are both unchecked (I always compact on close when the database is on the users machine and not over a network, I unchecked name autocorrect.)
3. Make sure all BIT fields have a Default Value (Yes)
4. Check this list for other gotchas (http://regina-whipp.com/blog/?p=371)

Did that help? If not we're here to see what else could be the problem.

 

[gerrythefish]

Thanks for the response. I saw a little improvement with SQL Serer Native Client but still slow.

I cant move to Citrix or any other solution. Zero budget (of course). I have Office 2013 and SQL 2012 to work with only.

The VPN is not a great place for data connections.
Was SQL Server Native Client 11.0 used for the ODBC connection for DSN-Less connections? Search this site for reasons why this woudl be prefered.
It is free, would have to be installed on each client PC.

With the number of users - and concurrent connections - the smal office Citrix should be considered. The Access application gets installed on one server, client PC would not require MS Access installed. They also wouldn't need to keep up on the Service Packs or other maintenance. It wouldn't matter if clients had the same version of Windows, Mac, or other computers.
The network band width would drop hugely. The network security would go up many times. The overall Technical desktop and network maintenance in hours would drop 90% once it was set up.
Users would see no deleay since Citrix would reside very close to the SQL Server.

Search this site for the Bat file that allows each user loggin in to copy the front-end into a folder and launch. Each user needs to have thier own front-end - linked to a common SQL Server Back end.

 

[GinaWhipp]

Hmm, no I would not suggest deleting permanently BUT on a COPY of the database I would open a Form and delete it... If you see an improvement then we know where to look but let's first see if there is an improvement.

 

[GinaWhipp]

Hmm, might also help to see one of those parameterized queries.

 

[gerrythefish]

Yup - this made me look closer at these. Some indexes and relationships were missing in access or didn't come over in the migration and some indexes were duplicated in SQL in the migration. So Im going back thru all the tables in SQL and reviewing those.

As a separate issue - would you have any resource on doing OOP in Access. Ive been trying to get into OOP - but have not found a good resource that takes you thru an access table, queries and forms. Please don't say search these forums - Ive done this many times.

Hmm, might also help to see one of those parameterized queries.

 

[GinaWhipp]

Before we get into programming.. stop! Did you migrate the relationships using DRI's or Triggers? Did you create relationships on the SQL Server? This is important and can affect speed.

 

[gerrythefish]

Before we get into programming.. stop! Did you migrate the relationships using DRI's or Triggers? Did you create relationships on the SQL Server? This is important and can affect speed.

I created most of them in Access, with referential integrity, cascade updates and cascade deletes turned on except for a few exceptions. These were then added to SQL by SSMA (version 6) in the migration. We had lots of problems with data that no longer fit the rules in access (for example a new field that was now required, caused errors in the migration as the old records had null in this field.) So we had to work thru all of those, adding data to the field, changing the field setting to allow nulls or changing the index in SQL to ignore nulls. Where relationships didn't come over Ive started creating them in SQL dragging the Keys between tables with the default enforce for replication and enforce foreign key constraint = Yes.

 

[CJ_London]

Just a quick one from me - I presume your recordsources for forms and reports are all designed to bring the minimum data across, both in terms of width and depth. A form with a recordsource of a table name and relying on filtering will be slower to respond than one which only brings through the fields required and a limited number of records based on a criteria

 

[Minty]

Also - if your combo boxes are pretty fixed datasets = eg country codes, lists of statuses etc that only get changed occasionally then consider loading them once at database startup into a local copy of the table - this will dramatically reduce loading time.

Also consider putting subforms onto tabs and not loading them until the sub form tab is selected. If you don't need to see it don't get the data...

 

[GinaWhipp]

Okay, (might only be me) by I don't use any Relationships via the SQL Server, because of the way SQL handles new records, as you just found out. I also noticed a slight difference in performance though minor I still decided it wasn't worth it. I handle validation via code/From data entry. I would remove them but up to you... as long as you selected DRI's and not Triggers you will probably be okay.

I also noticed that when possible to trim down that query on only what is needed. More than one table included in a Recordsource does cause recordsets to load slower.

So, we will wait till you finish and report back.

 

[gerrythefish]

Just a quick one from me - I presume your recordsources for forms and reports are all designed to bring the minimum data across, both in terms of width and depth. A form with a recordsource of a table name and relying on filtering will be slower to respond than one which only brings through the fields required and a limited number of records based on a criteria

This is an excellent point and one that I need help on. I have about 10 main tables with related child tables for each. For the most part I display a long datasheet list of records for the parent table. When the user clicks a link on a record in the datasheet I open a record in a single main form with multiple subforms for the children. I only load the subform that is selected (tab control) to reduce bandwidth.

Currently the datasheet and single main form are linked to a query on the table with all records. When the single form view opens I do a recordset.findfirst to move to the selected record. (I know I know but it worked great in access with delays of about 1 second to open or move to a new record)

I welcome any help.

 

[CJ_London]

For the most part I display a long datasheet list of records for the parent table.

1. consider adding a search facility before displaying any records - even 'customer starting with A' as a criteria will reduce the number of records - I use a search field which searches as it types (using the the change event) but has a half second delay from when the last character was typed before applying a criteria and refreshing the form - so if the user types a second character within that half second it gets added to the criteria. ergo the user can type 'Allied Horses' before a search commences.
2. if already using a search facility, avoid Like '%... in your criteria - putting a % at the front means that indexing is not used - instead it does a sequential search.
3. limit the width to those fields that users search on (plus the ID to enable the selected record to be selected)
4. 'I open a record in a single main form' so set the recordsource for this form to have a criteria to only bring through a single record - not sure why you need .movefirst
5.'with multiple subforms for the children' rather than relying on the linkchild/master properties to filter to the correct record, modify the recordsource with the appropriate criteria to only bring through the related records
6. 'I only load the subform that is selected' not quite sure what you mean by this - ideally the subform control will have a subform with a blank recordsource which is then populated to fetch the data

 

[jdorste]

I have almost the same setup as you. Migrated the Back end to SQL Server, and use a front end with linked tables. 150 users.

We use DSN-Less connections, and it is always blazing fast. So, you could always give that a shot. If you do decide to try it, your connection has to be inside an IF statement to work for some reason...

We used to have reports that would take hours to run, now only minutes.

 

[gerrythefish]

We use DSN-Less connections

Please explain!

 

[AccessBlaster]

Here is a link on the subject. http://www.accessmvp.com/DJSteele/DSNLessLinks.html

 

[Rx_]

Edit - update - Found this great link with code examples by HiTechCoach who makes the rounds on this forum among others. worth looking at:
http://hitechcoach.com/index.php?op...ge-your-linked-tables&catid=65:split-database


This site's search for DSN-Less doesn't work - use google instead.
Here is one of my post:
http://www.access-programmers.co.uk/forums/showthread.php?t=247756

Basically, each workstation need the SQL Server Native Client 11.0 (free download)
This is the ODBC driver used for AZURE Cloud to SQL Server. It is here to stay and a very efficient ODBC driver.

On my front-end, there is a Local Table with the name of the Tables in SQL Server to connect. The code shown at this link is used in a loop while each table name on SQL server is copied from the Local Table on Access.
First I destroy all links. Then rebild about 300 tables to SQL Server as Linked Tables.
A routine in my interface lets me switch from SQL Server Production to SQL Server Test within seconds. A function retries if my current links are connected to Production or in Test then links to the other one.

A local table SQL_Linked holds the SQL Server table names to link, with some yes / no columns and a column for comments.

A one command Relink takes seconds to reconnect to all the tables in SQL Server.

This is the short version:

Private Const vbQuote As String = """"
Private fTest As Boolean
' this short version probably won't compile with option explicit left out dims
Public Sub SQL_Linked_Process() ' Access 2010 SQL 2008 R2
      ' A local Table SQL_Linked with fields TableName, linked, relink (the last two are yes/no check boxes)
      ' A procedure populates SQL_Linked with all the Access Linked table names.
      ' Placing a check (yes) in Relink will delete the Access Linked Table, and append a new Linked Table with DSNLess connection
      ' -------------------- check below for connected to production or connect to test - -- run this public sub.
      
      Dim rsSQLLinked As Recordset
      Dim RecordsCount As Integer
      Dim Counter As Integer
      Dim td As TableDef  ' for table SQL_Linked
      Dim tdLinked As TableDef ' for new linked table
      
10    On Error Resume Next
20    Set rsSQLLinked = CurrentDb.OpenRecordset("SELECT * FROM SQL_Linked ORDER BY TableName", dbOpenDynaset, dbSeeChanges)
30    rsSQLLinked.MoveLast
40    RecordsCount = rsSQLLinked.RecordCount
50    rsSQLLinked.MoveFirst
60    Debug.Print "Number of Linked Tables " & RecordsCount
    
      '   Delete the linked tables that have a check in the Relink Column
70    If RecordsCount <> 0 Then
80        For Counter = 1 To RecordsCount
              'Debug.Print Counter & "/" & RecordsCount & " Field value " & rsSQLLinked.Fields(0).Value & "  " & rsSQLLinked.Fields(2).Value
90                    If rsSQLLinked.Fields(2).Value Then                 ' if Relink checkbox is true then
100                         For Each td In CurrentDb.TableDefs
110                               If td.Name = rsSQLLinked.Fields(0).Value Then
120                                     CurrentDb.TableDefs.Delete rsSQLLinked.Fields(0).Value
                'Debug.Print "Error Deleting old links " & Err.Description & Err.Number & " " & rsSQLLinked.Fields(0).Value
                
130                                     Err.Clear
140                               End If
150                         Next
160                   End If
170       rsSQLLinked.MoveNext
180       Next Counter
190   CurrentDb.TableDefs.Refresh
200   Else
210       MsgBox "There are no records in the table", vbOKOnly, "SQL_Linked_Process"
220       Exit Sub
230   End If
      ' ////////////// Relink to SQL Server ///////////
240   If RecordsCount <> 0 Then
250       rsSQLLinked.MoveFirst
260       For Counter = 1 To RecordsCount
              'Debug.Print Counter & "/" & RecordsCount & " Field value " & rsSQLLinked.Fields(0).Value & "  " & rsSQLLinked.Fields(2).Value
270                   If rsSQLLinked.Fields(2).Value Then                 ' if Relink checkbox is true then
                      ' Add new linked table here
'280                           Set tdLinked = CurrentDb.CreateTableDef(rsSQLLinked.Fields(0).Value)
280                            Set tdLinked = CurrentDb.CreateTableDef(rsSQLLinked.Fields(0).Value, dbAttachSavePWD) ' password persist now
                                
290                           tdLinked.Connect = ModifiedRefreshDNSLess2(rsSQLLinked.Fields(0).Value)
300                                   tdLinked.SourceTableName = "dbo." & rsSQLLinked.Fields(0).Value
310                                   CurrentDb.TableDefs.Append tdLinked
320                                   CurrentDb.TableDefs(rsSQLLinked.Fields(0).Value).RefreshLink
330                                   CurrentDb.Containers("Tables").Documents.Refresh ' doesn't refresh table icon
340                   End If
        'These should be at the end of the loop
'''350       rsSQLLinked.MoveNext
'''
'''            If rsSQLLinked.EOF = True Then GoTo ProcedureExit           
            'Debug.Print "Error " & Err.Description & Err.Number & " " & rsSQLLinked.Fields(0).Value           
            Debug.Print rsSQLLinked.Fields(0).Value;           
            If Err.Number = 0 Then
                Debug.Print
            Else
                Debug.Print " - Error #" & Err.Number & " : " & Err.Description
360             Err.Clear
            End If         
            DoEvents            
            rsSQLLinked.MoveNext            
            If rsSQLLinked.EOF = True Then GoTo ProcedureExit
            
370       Next Counter
ProcedureExit:
380   CurrentDb.TableDefs.Refresh
390   RerefreshLinkedTables
400   Else
410       MsgBox "There are no records in the table", vbOKOnly, "SQL_Linked_Process"
420       Exit Sub
430   End If
440   Set rsSQLLinked = Nothing
450   Set tdLinked = Nothing
460   Set td = Nothing
        
        Application.RefreshDatabaseWindow
        
470   Exit Sub
End Sub

Function ModifiedRefreshDNSLess2(TableName As String) As String ' temp test database
    Dim ConnectString As String
    Dim strConnectionString As String
    Dim scn As String
    Dim sLocalName As String
    Dim UID As String
    Dim pwd As String
    Dim ServerString As String
    ' "SERVER=SQLInstance\SQLDBName;DATABASE=" & DataBaseName & ";" & _
    ' 'DBMSSOCN  - showed up in SQL Server connect
        ServerString = "SQLInstance\SQLDBName"
    UID = "MyUserID"     ' temp SQL name change later  ' Same for RegulatoryDBT
    pwd = "MyPassword"
    sLocalName = TableName  ' "Const_Seed" ' <<<---manually add a table name here for one single table
    Dim DatabaseName As String
    
    If fTest = False Then
        DatabaseName = "MyDBNameDB"
    Else
        DatabaseName = "MyDBNameDBT"    ' <<<<<<<<<<------------  DBT or DB  ////////Uncomment for Test DB Server name next Production or test
    End If
    
    'DatabaseName = "MyDBNameDev"
    
    strConnectionString = "ODBC;DRIVER=SQL Server Native Client 11.0;" & _
        "SERVER=SQLInstance\SQLDBName;DATABASE=" & DatabaseName & ";" & _
        "UID=" & UID & ";" & _
        "PWD=" & pwd & ";" & _
        "Table=DBO." & sLocalName & ";Option=3;"
    strConnectionString = "ODBC;DRIVER=SQL Server Native Client 11.0;" & _
        "SERVER=SQLInstance\SQLDBName;DATABASE=" & DatabaseName & ";" & _
        "UID=" & UID & ";" & _
        "PWD=" & pwd & ";" & _
        "Table=DBO." & sLocalName & ";Option=3;"
    
    
    ModifiedRefreshDNSLess2 = strConnectionString
    'Debug.Print strConnectionString
End Function

Sub RerefreshLinkedTables() ' refreshes Linked tables doesn't refresh icon
          Dim dbs As dao.Database
          Dim tdf As TableDef
10        Set dbs = CurrentDb
20        For Each tdf In dbs.TableDefs
30            If Len(tdf.Connect) > 0 Then
40                    tdf.RefreshLink
                      'Debug.Print tdf.Name
50            End If
60        Next tdf
70        Set dbs = Nothing
End Sub

Public Function DatabaseName() As String
' Determin if in production DB or Test DB
10        On Error GoTo ErrorHandler
          Dim db As dao.Database
          Dim tdef As dao.TableDef
          Dim strConnection As String
20        Set db = CurrentDb
30        Set tdef = db.TableDefs("Pick a linked Table that exist")
40        strConnection = tdef.Connect
50        If InStr(strConnection, ";DATABASE=MyDBT;") <> 0 Then
60            DatabaseName = "SQL Server - MyDBT (TEST)"
70        ElseIf InStr(strConnection, ";DATABASE=MyDB;") <> 0 Then
80            DatabaseName = "SQL Server - MyDB (Production)"
90        Else
100           DatabaseName = "*** Unknown ***"
110       End If
ExitRoutine:
120       Set tdef = Nothing
130       Set db = Nothing
140       Exit Function
ErrorHandler:
150       MsgBox "Error #" & Err.Number & vbNewLine & Err.Description
160       DatabaseName = "*** Unknown ***"
170       Resume Ex