Looking to convert an Access query to MS SQL view.

After reading your other thread, this is not a simple problem, cause it tackles overall architectural questions..

Also I have to admit, that I don't understand your problem. The query contains a history table of some sort. These must are immutable. Thus the view must be immutable.

Note- tables are not in most normalized state, however structure cannot be changed, as data is being fed from another

Again, I don't get it. The ETL process should normalize it. and when I read your mentioned thread, you should really invest some time in the data model. Cause under normal circumstances you need to modify it anyway to get the best of using SQL Server as backend. Especially as this is necessary to get the top performance from it.

Besides that, use always table alias names and prefix all column names with it. Currently it's unclear for the important columns in the formulas where they belong to. Which influences a possible solution.

Especially as it's currently not clear, whether you need an INSTEAD OF INSERT trigger to make this view updateable in Access. Here we need to know your data model around those tables and the desired workflow in Access.

btw, using Access and SQL Server requires imho that you make use of the (read-only) list and (updateable) edit form approach. Thus having different views for lists and the edit form.

You don't need a trigger to make the view updateable by Access.  You need to include the PK's of ALL tables so that when you link the view, you can create a pseudo index.  BTW, every time you refresh the links or swap from the test server to the production server, you will need to recreate the pseudo index so use DDL and run that query as part of your BE swapping process.

Warning, if you are sloppy with the pseudo index and it is not in fact unique, an update from Access could corrupt the SQL Server table.

@Pat: It's about the SQL Server side. SQL Server views can only update one base table. Thus depending on the use-case you need INSTEAD OF triggers on the view to route the updates to the correct table. This is not about the primary key definition in the linked table.

The Access app should only be updating one table.  Joining the others provides "lookup" data for the bound form.  In fact, I lock all "lookup" controls to prevent accidents.  I don't want someone changing an address on an order form thinking they are changing it only for that order and have it update the default shipping address.

I've never had a problem updating a view and never created a trigger.  Just sayin'

Ben,

In your Access environment (not using SQL Server) is that query updateable ?

Hi Experts,

@Pat, @ste5an,
My experience with Access linked to SQL is the same as Pat, that as long you specify which is the PK, and the view is updatable in SQL, it will also be updatable in Access, without any use of triggers.

@Mark,
Yes it is.


I was originally looking for just a single view that will contain all this, the same I have in Access, however after reading Bitsqueezer's suggestion, I guess it would be better having divided into 3 pieces.

1- defining those computed columns within the original table itself.
2- a view for what the SNVNotesQry does
3- then have a final view containing all these info together.

P.S would highly appreciate if someone can post here what those SQL statements should be.

Thanks,
Ben


Thanks,
Ben

OK, so if the Query was updateable in Access, and all we are really doing is housing the Data on SQL Server - but still using Access (and Access syntax) to form the query, display, updates etc,

Then, the question becomes,  what errors are you getting if SQL Server is the backend ?

And quite disappointed in seeing that "NONCLUSTERED" keyword in those scripts. Thought I had drilled home the importance of CLUSTERED indexes.... Ah well....

All you had to do was add the keyword PRIMARY KEY after IDENTITY as per the scripts in the other thread - to wit :

[ID] [int] IDENTITY  PRIMARY KEY, 

Select allOpen in new window

You can fix, but will have to drop the existing constraint first (yep, a primary key isnt just an index, it is a constraint) :

alter table Patient_Progress_Notes drop constraint PK_Patient_Progress_Notes;
GO

ALTER TABLE Patient_Progress_Notes ADD CONSTRAINT PK_Patient_Progress_Notes PRIMARY KEY CLUSTERED (id);
GO

Select allOpen in new window

Do you actually have PRIMARY KEYS ? In SQL Server SSMS run :

select * from information_schema.KEY_COLUMN_USAGE where TABLE_NAME in ('Patient_Progress_Notes','Skilled_Nursing_Visit_Note','SNV_Printed_History')

Select allOpen in new window

We cannot create that view in SQL Server - not unless we convert the syntax to SQL Server.

And as far as syntax is concerned SQL Server uses the same basic approach as Access :

CREATE VIEW <view name> AS
select <column list>
from <tables>

Select allOpen in new window

See : https://docs.microsoft.com/en-us/sql/t-sql/statements/create-view-transact-sql?view=sql-server-2017 and scroll down to "Updatable Views". Then compare to : https://docs.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/create-view-statement-microsoft-access-sql along with the same issues with an updateable view in Access as there is in SQL Server.

I am not really understanding the predisposition with needing to use VIEWS - in Access, if the Query is updateable, then you probably arent using views on top of that (or are you) ?

Are you now wanting us to convert the Access code to SQL Server (T-SQL) ?

Hi Mark,

Not sure what happened to the indexes, remember doing whatever you posted in the other thread, however will apply now the drop/create you're posting.

But as mentioned, I'm really focusing here on getting the SQL above converted to T-SQL in an updatable version.
Preferable following Bitsqueezer guidance as outlined above.

Thanks,
Ben

@Bitsqueezer,

Starting with the "SNVNotesQry": This should be converted into a SQL Server view. The main thing it does is adding all those non-normalized fields ending with "_x". So to makes this perform better you should create a persisted calculated column from it. Just go to the table designer and add the formula you used in the "SUM" function as a new column in the formula property. The datatype will automatically be set by SQL Server from the result of this formula so add a CONVERT function call if you want to have a specific datatype out of the result. Then set the formula to "Persisted", this will save the result value as a real table value into the table in this new column so it will be changed whenever one of the values of the source columns will be changed.

Just to clarify, are you suggesting I first create a view and then a computed field, or the computed field approach is enough?

Just go to the table designer and add the formula you used in the "SUM" function as a new column in the formula property. The datatype will automatically be set by SQL Server from the result of this formula

This formula field is something specific to SQL, guess would need to know more about it...

Thanks,
Ben

Hi Ben,

in SQL Server you can create computed columns as part of the table design. So the answer is of course that you would need to create the computed column first in the table design using SSMS. After saving the changed table you will have a new column with whatever name you choose and then you can create a view which uses this column name in the SUM function.


It's nothing special, it's only an expression like you have built in the query.

In Access Microsoft added this feature using Macros beginning with A2010 (for Access tables) which is a similar functionality.

But what ste5an said above is also an important hint: Access itself can (depending on the query) update data in JOINed tables (in case of Access tables) where the same is not possible using SQL Server. In SQL Server you can update only exactly one table at the same time, so if you join more than one table and update fields from one table only - no problem. If you update fields from the other table - also no problem. If you try to edit the record and update fields from both tables it will not work.
So in general you should try to avoid to handle (edit) fields from more than one table in one form. If there is really no other way (and normally there are ways using a better frontend design) you will indeed need to use a trigger on the view, but this introduces also more problems: In that case you need to bind the view to the schema and that means that the table can no longer be changed in design as long as this view is bound to this (these) table(s). An instead-of-trigger for views is not easy to create especially for beginners in SQL Server so better avoid them (also because of the restrictions with the table design).
If you use the fields of the second table for display purposes only then I would also recommend what Pat said above: Just set all the other fields to read-only in the frontend which should be displayed but not edited.

Cheers,

Christian

Hi Bit,
Yes I was looking into that formula section, but seems to be grayed out.
Could that be due to old db version?
DB Compatibility level is SQL 2008 (100)
Thanks,
Ben

Hi,
Actually I was able to create it using this command.

ALTER TABLE dbo.Patient_Progress_Notes
ADD CountOfNotes as (CASE 
         WHEN isnull(Patient_Progress_Notes_1,'') ='' THEN 0
         ELSE 1         
      END)

Select allOpen in new window

Now I changed the formula to the following

(case  when [Status]='draft' then (0) else  case when isnull([Patient_Progress_Notes_1],'')='' then (0) else (1) end + case when isnull([Patient_Progress_Notes_2],'')='' then (0) else (1) end + case when isnull([Patient_Progress_Notes_3],'')='' then (0) else (1) end +case when isnull([Patient_Progress_Notes_4],'')='' then (0) else (1) end + case when isnull([Patient_Progress_Notes_5],'')='' then (0) else (1) end + case when isnull([Patient_Progress_Notes_6],'')='' then (0) else (1) end + case when isnull([Patient_Progress_Notes_7],'')='' then (0) else (1) end + case when isnull([Patient_Progress_Notes_8],'')='' then (0) else (1) end + case when isnull([Patient_Progress_Notes_9],'')='' then (0) else (1) end + case when isnull([Patient_Progress_Notes_10],'')='' then (0) else (1) end + case when isnull([Patient_Progress_Notes_11],'')='' then (0) else (1) end  end)     

Select allOpen in new window

So this should take care of this column.
Now how do I proceed?

Thanks,
Ben

Hi experts,

After analyzing it seems like these are the portions still needed for conversion.

 CInt(IIf([duration]=0,1440,[duration])/60) AS DurationHr,
    Int(Replace([Skilled_Nursing_Visit_Note].[SNV_ID],"snv","")) AS SNVNum, SNV_Printed_History.NoPrint,
     TimeValue(CDate(TimeSerial(Nz([Shift_To_Hour]),Nz([Shift_To_Minute]),0)-TimeSerial(Nz([Shift_From_Hour]),Nz([Shift_From_Minute]),0)+1))*24*60 AS Duration,
      (Abs(DateDiff("n",TimeSerial(Nz([Shift_From_Hour]),Nz([Shift_From_Minute]),0),TimeSerial(Nz([Shift_To_Hour]),Nz([Shift_To_Minute]),0)))/120)-Nz(DSum("CountOfNotes","SNVNotesQry","SNV_ID = '" & [Skilled_Nursing_Visit_Note].[SNV_ID] & "'"),0) AS MissingNotes, CInt(IIf([duration]=0,1440,[duration])/60) AS Duration2

Select allOpen in new window

Except for the DSUM which will be part of the table.

Thanks,
Ben

Hi Mark,

I see we are very close to get this done,

But if you want, we can complete the conversion to SQL Server T-SQL

 Note in SQL2012 (and more recent) we do have IIF() and TIMEFROMPARTS()  so that would take care of those few commands you highlighted above....

The problem with Access/SQL is that if you are still using any of the Access specific functions, it will force Access to load the entire recordset and be the one executing the query, which then nullifies the entire purpose of upgrading it to SQL.

Let me know if there is no way around, I will have to request management to upgrade to SQL 2012, but that may delay our process.

Also if we upgrade the servers SQL version to 2012, is mine personal SSMS 2008 pro edition still going to be able to connect to the DB of later version (if I download the free version I lose the profiler which is a very important tool)?

Thanks,
Ben

>> which then nullifies the entire purpose of upgrading it to SQL.

Not entirely....

True about the recordset, so in very large recordsets it will be just as "ho-hum" via SQL as it would be in Access.

But if you can filter the query so that the resultsets are manageable, then it will work OK.

The problem is having the hybrid approach of Access using Access syntax to a SQL Server backend.

But Access does haave updatable queries (and views) and while SQL Server does too (updateable Views), you can only update a table source at a time, whereas Access will update the respective tables (in a joined environment). So, they work quite differently.

And I have a sneaking suspiscion that there will be quite a few examples with bound forms....

That's when you need to consider using Stored Procedures (or similar) to do the updates, inserts, and deletes...

However, it is a journey, and ultimately, it would make it easier for you to upgrade to 2012 because functions like IIF() and FORMAT() are basically the same, and functions like TimeSerial() do have an equivalent in SQL Server TimeFromParts()

You also get the TIME datatype which would also be helpful.

Otherwise, we end up teaching you versions of T-SQL which has been superseded and will be a lot more effort which you then need to unlearn....

Try your Access query using the LEFT JOIN instead of the dlookup (and get rid of the duplicate columns) and see if that is an updatable query.  I think MS Access recognises the different tables and updates each table as a seperate transaction.

SQL Server (as a view) will error if you concurrently update columns from different tables - that will be a problem for you. Sure, there are tricks we can employ to accommodate (like the INSTEAD OF trigger) but that adds to the workload....

Profiler is still available in SQL2012, though, Extended Events were introduced in 2008 as an extra aid.

Your call...

Hi,

You could create a VIEW in MS Access as well..

In Access you can only create DAO queries, you cannot create views, the name "view" exists in SQL Server only.

Note in SQL2012 (and more recent) we do have IIF() and TIMEFROMPARTS()

If the database runs in compatibility mode for SQL Server 2008 then you have only the possibilities available which SQL Server 2008 supports.

You CAN update a view with JOINED tables in SQL Server

I already said that above: Yes, you can, but only with fields of one table from the two joined tables at the same time.

@Ben: I would not run the database in 2008 mode if you have the possibility to use a newer one only for the purpose of the SQL Server profiler. I don't think that it should be a big problem for your employer to buy you a licence. Please note that SQL Server has a special developer licence which has all the possibilities of the big enterprise versions which can be used for development only and which is very cheap compared to the real enterprise version.

Just for the purpose of being able to edit a joined query I personally would not switch to an Access query compared to a view created on SQL Server. A view will be compiled and also cached, it saves usage statistics and will be optimized in the background automatically by SQL Server independent of any frontend. If more than one user executes the same view it can i.e. cache the result table and perform very much better to return the recordset to the desired frontend - and so on. Nothing of these advantages can be done using an Access query.

I think you tried to edit the formular at the wrong place: Unfortunately the SSMS frontend wants you to "open" the "formula" property using the "+" symbol and then enter the formula at the second line, then it appears in the first line (with the "+"). You can't edit the first line for whatever reason.
And also you need to use a new field name in the field list. You can't add a formula to an existing column (you can't have normal data in the column and also have a formula there).
If you open the table designer after you now have successfully created the formula column with SQL you should see this column name and the formula.

As I said above you can now query the "CountOfNotes" column in the subquery in the SUM function. If you have set the formula to persisted your subquery should perform better. So if you exchange the DLookup in your original main query (which should now be a view in SQL Server instead) and use the subquery I wrote in my first comment then you are done with converting the DLookup.

The part with the TimeSerial things can be converted to similar functions existing in SQL Server, that's as easy as looking into the SQL Server Online Help using F1 or some Internet search. As I don't know about the contents of the fields and the expected results I really have not the time to convert all that for you, but I think that with all your experience about SQL Server from the past you should be able to do that quickly by yourself. I think the performance aspect is more interesting here.

Cheers,

Christian

Also if we upgrade the servers SQL version to 2012, is mine personal SSMS 2008 pro edition still going to be able to connect to the DB of later version (if I download the free version I lose the profiler which is a very important tool)?

If you can upgrade, go for a more actual version. E.g. JSON support in SQL Server 2016+, STRING_SPLIT().

SSMS is since some time free and can be downloaded separately: Download SQL Server Management Studio (SSMS).

Imho you should use the actual SSMS under all circumstances. Cause it allows you to connect to all different SQL Server versions including Azure instances.

Let me know if there is no way around, I will have to request management to upgrade to SQL 2012, but that may delay our process

Reason: Product life time.. End of Mainstream support for SQL Server 2008 and SQL Server 2008 R2.

Cause you only have 10 months before it becomes a technical debt, where security risks must be assessed and reported. So point this out and I think you'll get your actual SQL Server instances faster than you  may have thought. Especially as you not necessarily need a new machine. For development and testing existing hardware should do it (side-by-side installation). So you have a more relaxed timeline to production.

@BitSqueezer,

Maybe we are splitting hairs in nomenclature, but I can certainly create a VIEW in Access

Maybe not as a "normal" create query, but can certainly be created via Access desktop (via Visual Basic)

If I create a table (Access SQL)

create table [EE Test Table]
( ID AutoIncrement PRIMARY KEY,
  int_Field1 Integer,
  str_Field2 varchar(20)
);

Select allOpen in new window

Then go into  Database Tools -> Visual Basic -> Immediate  and type in :

currentproject.Connection.Execute "create view [vw EE Test Table] (F1,F2,F3) as SELECT ID, int_Field1, str_Field2 from [EE Test Table] AS EE"

Select allOpen in new window

Then close that window, and use the navigation pane to search for vw, It will find/ locate the query [vw EE Test Table]. Sure, it doesnt have the CREATE VIEW syntax, it has become a named / stored Query :

SELECT EE.ID AS F1, EE.int_Field1 AS F2, EE.str_Field2 AS F3
FROM [EE Test Table] AS EE;

Select allOpen in new window

Which is all a VIEW is anyway - a stored / named query. In that regard, Access has always had VIEWS - being a named query. So, it is the Query that needs to be updatable as far as Access is concerned.

But I do digress...

The task at hand is How To transition to a SQL Server backend, while using Access Syntax for Queries / Forms etc.

Because SQL Server wont support an updatable view the same way Access does for a (named) query when there are multiple tables....

So, fix the Access Query (using the left join, removing duplicate columns, keeping Access syntax) and see if that becomes updatable. If not, then we MUST take a different direction.

And, as asked before, if your Access Query (using Access backend) is updatable, then what errors are you getting when using the exact same query but having SQL Server as the datasource - is it a SQL Server error, or Access error, or something else ?

Try that left join variant as well....

And what table / columns does your form update ?

Hi Experts,

I'm not in the office today, but I guess will end up closing this thread and opening a new one for the remaining unconverted SQL statements as it was not stated in original question that requires 2008 solution, and from what I see this is cause of the overhead here...


@Mark,

The task at hand is How To transition to a SQL Server backend, while using Access Syntax for Queries / Forms etc.

Because SQL Server wont support an updatable view/query the same way Access does when there are multiple tables....

Just want correct this, my goal is to fully convert to SQL and not have Access syntax here at all, while for the updatable option, I see conflicting comments, some suggest a sub query while others here mentioning that only a left join will make it possible (or perhaps none of them...), will leave for you experts to figure that out, whoever comes up with an updatable SQL view and proves it wins...-:)

P.S just to clarify, the need is to only update one table, which is the SNV_Printed_History table.

Thanks,
Ben

Thanks to all Participants!!

>> will leave for you experts to figure that out, whoever comes up with an updatable SQL view and proves it wins...-:)

So, a moral victory ?

Some final thoughts - Since you can't convert completely to T-SQL syntax now and still be able to use bound forms, then you may need to make adjustments.

SQL Server doesn't support VBA so you obviously cannot use VBA or UDF functions in pass-through queries or in views.  You are however allowed to make views and then reference those views in an Access query and use VBA and UDF functions there.  SQL Server will prepare the data requested by the view and Access will apply the rest of the logic locally.  Also, you need to consider your final platform since you don't want to have to rewrite again if you can avoid it so you need to understand what is possible in your target platform.  Access is built to integrate with data from any ODBC compliant RDBMS and as long as you stick with the Access variant of SQL which is rudimentary at best but does allow the use of VBA and UDF functions,, you can swap BE databases at will.  This is amazingly powerful but you are giving it up so you might need to start now breaking up the Access app into what can be supported in the target solution.  SQL Server does allow you to create UDF using its internal language.  You can create those now and use them in views.  I don't believe that will alter the updateablility of the view.