Adapting Access frontend to new Sql backend (1 Viewer)

selvsagt · Mar 6, 2015

Hi.

I have moved my data from Access to SQL Express, and linked the tables to the Access frontend.
I have made views for the "biggest" queries that has a lot of calculations.

Before I upsized, I restricted the amount of records calculated by using criteria from the customer form (frmKundekort) with the primary key being accountID.

So all other relevant queries for reports and a bunch of other things, has forms!frmKundekort.accountID as a criteria in the underlying queries.

This does not work with the sql view.

So my question is, what is "best practice" for narrowing down the amount of records transfered from sql to the frontend? How to put in a WHERE clause in the sql view, so it only returns records related to the relevant accountID in the form frmKundekort?

Comments or examples are greatly appreciated...

Rx_ · Mar 6, 2015

My installation is also Citrix with full SQL Server and Access front-end.
Before answering the query question, does your Citrix have a process (bat file) that makes a copy of the front-end for each client logon? It is a worthy question as many of us are amazed how often a single front-end is used for a networked application.

The front-end has linked tables to two different objects in SQL Server - directly to Tables and directly to Views.
Just for background, what driver are you using (e.g. ODBC from Windows or SQL Server Client 11.0). Is the front-end using DSN-Less connections?

We just converted another application over to SQL Server yesterday. It did use multiple linked tables to sQL with the join and filter criteria on the front-end.
It was converted to a basic single linked SQL Server View - with the criteria filter on the front-end. In theory, ODBC should be intelligent and convert the request to an equal T-SQL to run on the SQL Server. This query had many outer joins.
In this case, the query after the conversion ran over ten times faster.

If a select * is used, all of the records come across the wire (back-end to the front-end). If a good SQL statement with a filter criteria is requested, all of the processing takes place on SQL Server and only the results come across the wire.
If the front-end query uses a custom function or Access function that is not part of SQL Server's T-SQL, then the translation via ODBC can get really messy.

In the case just described, the SQL View was created, then the front-end selected against that. The front-end made use of several custom functions. This allowed SQL server to do what it does best and the front end to not ask ODBC to deal with an Access custom function that it can't translate.

A stored procedure allows the front-end to send an exact T-SQL statement to SQL Server with the where clause. In general.... case-by-case ... this is not necessary for 90% of the queries.

Basically, create a view without the Where clause on SQL Server. Treat this as a linked table.
Then, create a query against that linked table (actually a linked view). In the query use the where clause. ODBC should translate that where clause for you into TSQL and process the query on SQL server, returning over the wire only the records requested.

One more thing, monitor the resources on SQL Server. When my IT department set up SQL Server for over 50 users, they only assigned 1 GB of RAM since that was the Microsoft Sales Recommendation. If your SQL Server is on a Virtual Machine, nobody has any idea how much real/actual physical memory is actually available. The virtual SQL server RAM could be cached to hard drive by the virtual machine manager.

That is a lot to take in for one post. Ask more. This site has some really excellent members for this subject. It is a Friday so some of them in England and Australia may already be starting the weekend. It is a Friday, I missed using spell check on my Power Point attachment.

CSL · Mar 6, 2015

Can you use a table function - basically a view but accepting a parameter.

selvsagt · Mar 9, 2015

Thank you RX! That was just the answer I wanted. Even if it were friday (now its monday

) You gave me grounds to be very productive during the weekend.

First some answers to your questions;

DSN;
To be honest, I have seen the name DSN, but it stops there. Is this something I should read up on?
I have connected the backEnd with an ODBC connection.
The backend is on its on server with SQL Server Express 2012.

FronEnd;
All users gets a fresh frontEnd when they log on on their personal space. I love this function. So easy to update the frontend.

Your solution;
I have during the weekend testet out a lot of your ideas, and they work.
I've spent a lot of time getting the nested queries to "view" (rather than "views").

So far the result is a faster DB. Much faster in most aspects.

One issue now is that the users can no longer delete records because the view has more than one table.
(error: ... is not updatable because the modification affects multiple tables (#4405)) This I have to figure out.

So the structure in the db now, is mostly views. This was flexible.
But, when I need to do changes in to records in the recordset, must I then do this in VBA, or may I use delete query?
A delete query ends up in the original question: How to identify the record from the form that I need to delete?

Example;
An Account [tblAccount], has a portfolio [tblPortfoli]) that includes a lot of holdings [tblHoldings] that consist of a product [tblProd]. Each product has a changing price (daily, weekly or monthly, or never) that it gets from [tblKurs].
The user may add a holding there even if we do not have a price. This could be a personal company or real estate. In the view I used a outer left join to include all holdings, so that the user sees everything. The same data is later used in other calculations.

Now the user may not delete this holding because the recordset is not updatable. Before I used the dlookup to get the price, because that would keep the recordset updatable. But if I use dlookup now it "kills performance".

The only thing that needs to be deleted is the entry in the table tblHoldings.

Code:

SELECT        dbo.tblHoldings.holdingsID, dbo.tblHoldings.portfolioID, dbo.tblHoldings.prodID, dbo.tblHoldings.kostpris, dbo.tblHoldings.antallAndeler, dbo.tblHoldings.dato, dbo.tblHoldings.DepotID, dbo.tblHoldings.RG, 
                         dbo.tblHoldings.kommentar, dbo.tblHoldings.Rapport, dbo.tblHoldings.antallAndeler * dbo.tblHoldings.kostpris AS Kjopspris, dbo.tblHoldings.innlosPro, dbo.view_produkt_sistekurs.Sistekurs, 
                         dbo.view_produkt_sistekurs.Kursdato, dbo.tblHoldings.Depot, dbo.tblHoldings.antallAndeler * dbo.view_produkt_sistekurs.Sistekurs AS DagensVerdi, 
                         dbo.tblHoldings.antallAndeler * dbo.view_produkt_sistekurs.Sistekurs - dbo.tblHoldings.antallAndeler * dbo.tblHoldings.kostpris AS gevTap
FROM            dbo.tblHoldings LEFT OUTER JOIN
                         dbo.view_produkt_sistekurs ON dbo.tblHoldings.prodID = dbo.view_produkt_sistekurs.prodID

Rx_ · Mar 9, 2015

One issue now is that the users can no longer delete records because the view has more than one table.
(error: ... is not updatable because the modification affects multiple tables (#4405)) This I have to figure out.

This gets into the DB design. There typically needs to be a clear relationship between the primary key and the data being deleted.

While every case is different, there is good reason to consider VBA in the delete.

This is an example where the record is marked deleted (status changed from "A" Active to "I" Inactive). The list box only displays a record with "A".
Instead of actually deleting a record, it is marked as "inactive" and filtered (hidden) from the user. The execute could just as easily have deleted it too.
The VEDWells is a View for Environmental Database Wells.
Notice how a PK (field 0) was selected from the list box, then included into the SQL Statement. The SQL Statement was executed, then the list box refreshed.

Code:

Private Sub cmdRemoveEM_Click()
' Simply picks the WellID (primary key) from the listbox, runs update query to change value from Inactive to Active ("I" or "A")
' then A SQL Server View 'VEDWELLS' uses this value from the Wells table
' A query 'vEDWells_ListBox' is the row source for the list box.
 Dim ID_WellSelected As Long
 Dim SQLString  As String
 On Error Resume Next
  If Not IsNull(Me.lst_id_wells.Column(0)) Then
      ID_WellSelected = Me.lst_id_wells.Column(0) ' ID_Well selected in listbox
      'AddWellsMode = False (a flag in used at the form level)
         SQLString = "UPDATE Wells SET Wells.ED_EM_Activity = 'I' WHERE (((Wells.ID_Wells)=" & ID_WellSelected & ")); "
         CurrentDb.Execute SQLString, dbSeeChanges
         Me.lst_id_wells.RowSource = "vEDWells_ListBox" ' this works to refresh
         Me.lst_id_wells.Requery ' The list box is blank  now.
      Else
           MsgBox "Please select a Well from the list. This button will change the EM Active for the selected Well to an 'I'", vbOKOnly, "Well Selection Required"
      End If
End Sub

Of course, there are many variations for this theme.

Just to get started
http://www.access-programmers.co.uk/forums/showthread.php?t=224121&highlight=native
Microsoft AZURE uses SQL Server Native Client 11.0. It is a free download (search for other posts I have made). With Citrix, it can be downloaded on the server where Access is run (the users copy of Access). SQL Server Native Client is an ODBC client, very likely updated over the Windows ODBC.

Basically, my distributed front-end has a local table (SQL-Link) with the names of each linked Table (or view). A Subroutine first deletes all Linked Tables in the collection. Then, a loop links each table (or view).

My SQL Database are named MyDB and MyDBT - the T is the Test DB.
The relining procedure allows me to relink to MyDB or MyDBT.
Every few days, a copy of MyDB (production) is copied over MyDBT.
By running the relinking subroutine, it provides me with somewhat real data to develop with.

On Citrix, the users also have a Production DB and a Test DB. This makes for good testing and training.

Rx_ · Mar 9, 2015

One issue now is that the users can no longer delete records because the view has more than one table.
(error: ... is not updatable because the modification affects multiple tables (#4405)) This I have to figure out.

This gets into the DB design. There typically needs to be a clear relationship between the primary key and the data being deleted.

While every case is different, there is good reason to consider VBA in the delete.

This is an example where the record is marked deleted (status changed from "A" Active to "I" Inactive). The list box only displays a record with "A".
Instead of actually deleting a record, it is marked as "inactive" and filtered (hidden) from the user. The execute could just as easily have deleted it too.
The VEDWells is a View for Environmental Database Wells.
Notice how a PK (field 0) was selected from the list box, then included into the SQL Statement. The SQL Statement was executed, then the list box refreshed.

Code:

Private Sub cmdRemoveEM_Click()
' Simply picks the WellID (primary key) from the listbox, runs update query to change value from Inactive to Active ("I" or "A")
' then A SQL Server View 'VEDWELLS' uses this value from the Wells table
' A query 'vEDWells_ListBox' is the row source for the list box.
 Dim ID_WellSelected As Long
 Dim SQLString  As String
 On Error Resume Next
  If Not IsNull(Me.lst_id_wells.Column(0)) Then
      ID_WellSelected = Me.lst_id_wells.Column(0) ' ID_Well selected in listbox
      'AddWellsMode = False (a flag in used at the form level)
         SQLString = "UPDATE Wells SET Wells.ED_EM_Activity = 'I' WHERE (((Wells.ID_Wells)=" & ID_WellSelected & ")); "
         CurrentDb.Execute SQLString, dbSeeChanges
         Me.lst_id_wells.RowSource = "vEDWells_ListBox" ' this works to refresh
         Me.lst_id_wells.Requery ' The list box is blank  now.
      Else
           MsgBox "Please select a Well from the list. This button will change the EM Active for the selected Well to an 'I'", vbOKOnly, "Well Selection Required"
      End If
End Sub

Of course, there are many variations for this theme.

Just to get started
http://www.access-programmers.co.uk/forums/showthread.php?t=224121&highlight=native
Microsoft AZURE uses SQL Server Native Client 11.0. It is a free download (search for other posts I have made). With Citrix, it can be downloaded on the server where Access is run (the users copy of Access). SQL Server Native Client is an ODBC client, very likely updated over the Windows ODBC.

Basically, my distributed front-end has a local table (SQL-Link) with the names of each linked Table (or view). A Subroutine first deletes all Linked Tables in the collection. Then, a loop links each table (or view).

My SQL Database are named MyDB and MyDBT - the T is the Test DB.
The relining procedure allows me to relink to MyDB or MyDBT.
Every few days, a copy of MyDB (production) is copied over MyDBT.
By running the relinking subroutine, it provides me with somewhat real data to develop with.

On Citrix, the users also have a Production DB and a Test DB. This makes for good testing and training.

Adapting Access frontend to new Sql backend (1 Viewer)

selvsagt

Registered User.

Rx_

Nothing In Moderation

Attachments

CSL

Registered User.

selvsagt

Registered User.

Rx_

Nothing In Moderation

Rx_

Nothing In Moderation

Similar threads

Users who are viewing this thread