Help needed converting Access DB to MS SQL Server

Ben, for something like this, it will help the Experts to know the table layout of each of the joined tables, the field type you have assigned to it any indexing you have done along with some sample data.  Include the number of rows in each table.  (is it 100's, 1,000's or millions)

Hi,
Is there a way I can extract this info from SQL w/o having to upload the tables itself?
btw, those tables in q each of them contain 22K records, and same when joined.
Thanks,
Ben

Probably the easiest would be to go to Manager Studio, select the database, then table, right click on the table and select Script Table As, then Create To, then New Query window.  Then copy the CREATE TABLE portion.

You would end up with something like this

CREATE TABLE [dbo].[BC_Card_Bar_Code_Prefix](
	[Bar_Code_Prefix_ID] [int] IDENTITY(1,1) NOT NULL,
	[Card_Bin_ID] [int] NOT NULL,
	[Bar_Code_Prefix] [varchar](20) NOT NULL,
	[Card_Prefix] [varchar](20) NOT NULL,
 CONSTRAINT [PK_Bar_Code_Prefix_ID] PRIMARY KEY CLUSTERED 
(
	[Bar_Code_Prefix_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Select allOpen in new window

Do that for both tables and if you can include a few rows of sample data, that will help as well.

@Scott,
Attached those Create table script.

Note- I cannot change much of the tables structure as its being used already by other applications. (btw, tables were not designed by me...)

Thanks,
Ben
1.txt

Maybe I have it wrong on what you are trying to do.   Are you converting what you have in MS Access to MS Sql Server?

@Pat,

NEVER use domain functions in queries...

I needed for the ability of editing records.

Once you move the BE to a RDBMS, this is the first (and possibly ONLY) thing you need to change to get better performance.

So this is the Magic here..?

Rather than fetching huge recordsets and filtering locally, use criteria in the RecordSource query and only retrieve the records the user selects.

Good point, however will need that SQL converted first in order to apply that, as currently form is bound to an Access query based on that SQL op.
Thanks,
Ben

Are you converting what you have in MS Access to MS Sql Server?

Yes, why?
Thanks,
Ben

@Scott,
I created index on both joined fields, and did help a little, not much...
Thanks,
Ben

I needed for the ability of editing records.

Then put them in the ControlSource of form controls.  You really cannot work with unfiltered recordsets the way you are doing it.  The whole point of using an RDBMS is to let the server do the filtering.  You do NOT download entire tables and let the user filter locally.

Good point, however will need that SQL converted first in order to apply that, as currently form is bound to an Access query based on that SQL op.

Ben, We've been over this a dozen times.  I tell you how to do it.  You think I don't know what I'm talking about and accept some other answer because my suggestions don't jibe with some preconceived idea you have that Access is bad and it is all Access' fault.  Converting the SQL to T-SQL and unbound forms is NOT the first step.  In fact, it will probably NEVER be required.  You simply need to understand how to use bound forms efficiently in a client/server environment.  I'm not saying that you might never have to resort to unbound forms.  I am telling you that in 25 years of converting Access apps  to SQL Server, Oracle, DB2, etc.  I have NEVER had to convert a bound update form to an unbound form.   And I have numerous applications where the main tables contain MILLIONS of rows.  I have had to use stored procedures to create very complicated reports but that is a different issue.  We are talking here about updateable bound forms.  If your recordset is returning THOUSANDS of rows because you have no selection criteria, you CANNOT be using domain functions unless your users like to watch the pixels paint.  However, if you use selection criteria so you are returning a very small set of records (preferably 1 per form unless the form is a continuous subform), you can get away with using the domain functions but I still don't do it.  If I need summary information, I either use a subform bound to a totals query or domain functions bound to the controls.

I'm not going to create a timing app for you but perhaps someone can post one they have already built.  You need to understand that it costs more to retrieve thousands of records the user will never view than to have the user specify what he is looking for up front and return dozens of records one at a time so he can update them.

You can also experiment with using views.  Views are updateable provided you include the PK's of each table and they will sometimes make queries with joins perform better since the server doesn't always have to calculate an execution plan on the fly.  The view's execution plan is stored when it is saved (or maybe when it runs the first time - that is how Access does it).

Well, there doesnt seem to be any PK's or any other indexing at all on those two tables in that attached script....

I dont see a need for views at this stage, and while it would be great to restructure the tables (and that is where views can help make a table look like the original) I believe we do need to get some indexing happening.

When you created the script, is that everything that was generated ?

Can you share some sample data so I can do some analysis on the query ?

Initial thought is we can certainly make a difference to the speed of that query.

@Pat,

Converting the SQL to T-SQL and unbound forms is NOT the first step.  In fact, it will probably NEVER be required...

There is clearly a misunderstanding here...
Where in my comments did you see I'm looking for an unbound forms solution?!
The only thing I said is that in order to follow your suggestion on having the forms control source change instead of applying the filter, I first have to get a constructed a valid SQL query.

In order to clarify what I meant, here is the code I use to filter the form.
Let me know how will you modify that in order to accomplish your solution.

    Dim s As String
    If Nz(Me.TextClient_First_Name, "") <> "" Then s = s & " And Client_First_Name like '" & Me.TextClient_First_Name & "*'"
    If Nz(Me.TextClient_Last_Name, "") <> "" Then s = s & " And Client_Last_Name like '" & Me.TextClient_Last_Name & "*'"
    If Nz(Me.TextDate_SignedFrom, "") <> "" Then s = s & " And Date_Signed >= #" & Me.TextDate_SignedFrom & "#"
    If Nz(Me.TextDate_SignedTo, "") <> "" Then s = s & " And Date_Signed <= #" & Me.TextDate_SignedTo & "# +1"
    If Nz(Me.TextNurse_Name_Stamp_SNV, "") <> "" Then s = s & " And Nurse_Name_Stamp_SNV like '" & Me.TextNurse_Name_Stamp_SNV & "*'"
    If Nz(Me.TextNurse_Signature_First_Name, "") <> "" Then s = s & " And Nurse_Signature_First_Name like '" & Me.TextNurse_Signature_First_Name & "*'"
    If Nz(Me.TextNurse_Signature_Last_Name, "") <> "" Then s = s & " And Nurse_Signature_Last_Name like '" & Me.TextNurse_Signature_Last_Name & "*'"
    If Nz(Me.TextNurse_User_ID_num_SNV, "") <> "" Then s = s & " And Nurse_User_ID_num_SNV like '" & Me.TextNurse_User_ID_num_SNV & "*'"
    If Nz(Me.TextProgress_Note_ID, "") <> "" Then s = s & " And Progress_Note_ID like '" & Me.TextProgress_Note_ID & "*'"
    If Nz(Me.TextReviewed_By, "") <> "" Then s = s & " And Reviewed_By like '" & Me.TextReviewed_By & "*'"
    If Nz(Me.TextReviewedDateFrom, "") <> "" Then s = s & " And ReviewedDate >= #" & Me.TextReviewedDateFrom & "#"
    If Nz(Me.TextReviewedDateTo, "") <> "" Then s = s & " And ReviewedDate <= #" & Me.TextReviewedDateTo & "#"
    If Nz(Me.TextReviewed_Status, "") <> "" Then
        If Me.TextReviewed_Status = "Yes" Then
            s = s & " And ReviewedDate is not null"
        Else
            s = s & " And ReviewedDate is null"
        End If
    End If
    If Nz(Me.TextShift_From, "") <> "" Then s = s & " And Shift_From_Hour = " & Me.TextShift_From
    If Nz(Me.TextShift_To, "") <> "" Then s = s & " And Shift_To_Hour = " & Me.TextShift_To
    If Nz(Me.TextSNV_ID, "") <> "" Then s = s & " And SNV_ID like '" & Me.TextSNV_ID & "*'"
    If Nz(Me.TextVisit_DateFrom, "") <> "" Then s = s & " And Visit_Date >= #" & Me.TextVisit_DateFrom & "#"
    If Nz(Me.TextVisit_DateTo, "") <> "" Then s = s & " And Visit_Date <= #" & Me.TextVisit_DateTo & "#"
    If Me.CheckOnlyNotPrinted = True Then s = s & " And PrintedDate is null"
    If CheckOnlyMissingNotes = True Then s = s & " And MissingNotes > 0"
    If Nz(Me.ComboVendor, "") <> "" Then s = s & " And VendorsID = " & ComboVendor
    
    If Nz(Me.ComboNoPrint, "") <> "" Then
        If Me.ComboNoPrint = "Yes" Then
            s = s & " And NoPrint =  -1"
        Else
            s = s & " And (NoPrint is null or NoPrint = 0)"
        End If
    End If
    
    
    s = s & " And (Status is null or Status <> 'Draft')"
 Application.Echo False
 DoCmd.Hourglass True
 If Len(s) > 0 Then
        Me.Filter = Mid(s, 5)
        Me.FilterOn = True
    Else
        Me.Filter = ""
        Me.FilterOn = False
    End If
 Application.Echo True
 DoCmd.Hourglass False

Select allOpen in new window

Thanks,
Ben

@Mark,

I dont see a need for views at this stage

Dont get that, please explain why?

Those indexes does not get displayed in the create table script
Here they are

USE [SkilledNursingVisitNotes]
GO

/****** Object:  Index [IX_Skilled_Nursing_Visit_Note_SNVID]    Script Date: 11/15/2018 20:43:38 ******/
CREATE UNIQUE NONCLUSTERED INDEX [IX_Skilled_Nursing_Visit_Note_SNVID] ON [dbo].[Skilled_Nursing_Visit_Note] 
(
	[SNV_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

USE [SkilledNursingVisitNotes]
GO

/****** Object:  Index [IX_SNV_Printed_History_SNVID]    Script Date: 11/15/2018 20:46:33 ******/
CREATE NONCLUSTERED INDEX [IX_SNV_Printed_History_SNVID] ON [dbo].[SNV_Printed_History] 
(
	[SNV_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

Select allOpen in new window

Thanks,
Ben

I do not wish to cut across the experienced advice you are receiving from Pat, which appears to be great advice.

"Domain Functions" are these:

DAvg Function
DCount Function
DFirst, DLast Functions
DLookup Function
DMin, DMax Functions
DStDev, DStDevP Functions
DSum Function
DVar, DVarP Functions

And Pat is advising you to AVOID these.
================================

In the question your query has an easy part which requires no "conversion":

SELECT
    Skilled_Nursing_Visit_Note.ID
  , SNV_Printed_History.VendorsID
  , SNV_Printed_History.SNV_ID
  , Skilled_Nursing_Visit_Note.Client_Last_Name
  , Skilled_Nursing_Visit_Note.Nurse_Signature_Last_Name
  , Skilled_Nursing_Visit_Note.Visit_Date
  , Skilled_Nursing_Visit_Note.Shift_From_Hour
  , Skilled_Nursing_Visit_Note.Shift_To_Hour
  , Skilled_Nursing_Visit_Note.Date_Signed
  , Skilled_Nursing_Visit_Note.Nurse_Name_Stamp_SNV
  , Skilled_Nursing_Visit_Note.Nurse_User_ID_num_SNV
  , SNV_Printed_History.ReviewedBy
  , SNV_Printed_History.ReviewedDate
  , SNV_Printed_History.PrintedDate
  , SNV_Printed_History.PrintedBy
  , SNV_Printed_History.PrintedDate
  , Skilled_Nursing_Visit_Note.Client_First_Name
  , Skilled_Nursing_Visit_Note.Nurse_Signature_First_Name
  , Skilled_Nursing_Visit_Note.Date_Of_Birth
  , Skilled_Nursing_Visit_Note.Shift_From_Minute
  , Skilled_Nursing_Visit_Note.Shift_To_Minute
  , SNV_Printed_History.ReviewedDate
  , Skilled_Nursing_Visit_Note.Treatments_Administered
  , SNV_Printed_History.NoPrint
  , Skilled_Nursing_Visit_Note.Status
  , Skilled_Nursing_Visit_Note.Client_Last_Name_Init
  , Skilled_Nursing_Visit_Note.Visit_Date_Init
  , Skilled_Nursing_Visit_Note.Client_First_Name_Init
  , Skilled_Nursing_Visit_Note.Shift_From_Init
  , Skilled_Nursing_Visit_Note.Shift_To_Init
  , SNV_Printed_History.NoPrint
FROM SNV_Printed_History
INNER JOIN Skilled_Nursing_Visit_Note ON SNV_Printed_History.SNV_ID = Skilled_Nursing_Visit_Note.SNV_ID

Select allOpen in new window

The "hard part" are these:

  , CInt( IIf( [duration] = 0, 1440, [duration] ) / 60 )                                                                                                                                                                                                                              AS DurationHr
  , DLookUp( "CountOfPN", "SNVNotesQry", "SNV_ID = '" & [Skilled_Nursing_Visit_Note].[SNV_ID] & "'" )                                                                                                                                                                                 AS CountOfPN
  , DLookUp( "CountOfNotes", "SNVNotesQry", "SNV_ID = '" & [Skilled_Nursing_Visit_Note].[SNV_ID] & "'" )                                                                                                                                                                              AS CountOfNotes
  , Int( REPLACE( [Skilled_Nursing_Visit_Note].[SNV_ID], "snv", "" ) )                                                                                                                                                                                                                AS SNVNum
  , 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

ORDER BY
    Int( REPLACE( [Skilled_Nursing_Visit_Note].[SNV_ID], "snv", "" ) );

Select allOpen in new window

You will need to learn how to deal with things like "TimeValue( CDate( TimeSerial( Nz( ..."

You need to strip these harder rows down to the Access specific functions being used, and then learn the T-SQL (SQL Server) equivalent e.g.

Cint() ====>>>   CAST(.... as integer)  or CONVERT(integer, ... )
Nz() ====>>>> ISNULL() or COALESCE()

i.e. once you know what the Access function does, the category of SQL Server's equivalent should be apparent.

Alpha list of Access functions
SQL Server function by category

So, my suggestion would be to cease asking for conversion of whole queries, and instead ask for advice on conversion of functions IF you are unable to find a way.

Which leads back to the "Domain Functions".
Pat's advice is to AVOID these, so you need to learn how.

That's it for me. I have never had to convert an access application to use a SQL server back-end, so I would listen to those who have.
Good luck.

@PortletPaul,
I did not disagreed with Pat on the disadvantage of using domain functions in a query, I just explained why I'm forced to use it in Access...
and here is some info on that point.
https://www.experts-exchange.com/questions/29122420/Continuous-form-performing-very-slow-in-Access-2016.html?anchorAnswerId=42716890#a42716890
Thanks,
Ben

Two things right off:

a. add a Timestamp column to every table in SQL that Access will touch, then re-link the tables.
b. ensure that you have proper indexing in place.

 Then for some other tips and tricks you can do fairly quickly, see "Best of both Worlds" here:

https://www.jstreettech.com/downloads.aspx

Beyond that, conversion to SQL is a journey.  While most will see decent performance with just a straight conversion and no additional work, that's really only the start.   You won't see the benefit of using SQL until you start:

a. using views
b. using pass-through queries.
c. using stored procedures.
d. using triggers.

With SQL, you want to push as much of the processing server side as possible.   As has been mentioned, in Access queries, any of the following will cause the query to get processed locally by JET/ACE:

a. Joins to local tables.
b. JET specific SQL
c. Use of VBA/Access expressions.

and yes, domain functions in a query are an absolute no-no.   There are very few times you want to break that rule.   Editability is one such, but I'd re-design the interface before I did that.   In fact in all my years of programming with Access, I've only done that once.

HTH,
Jim.

Looks like you're receiving some rock-star feedback on your question from the above experts, so instead of commenting directly you can check out the article Migrating your Access Queries to SQL Server Transact-SQL for more help with this road you're going down.

If fact the above feedback looks so good I may have to update my article with these points, or ask anyone if they want to write a Part 2.

Hi Experts,

I'm not in the office today, will test & reply on Sunday.
Have a nice weekend!

Thanks,
Ben

@bfuchs

The Views comment was in relation to Pat's suggestion : "You can also experiment with using views. ...."

The Index comment was in relation to Primary Key (appears to be missing, and as Pat said was needed for those updatable Views)  - which should have been automatically included as part of the "Script Table as" --> "Create to" option, and I cannot see any Primary Key which is very important in SQL Server terms (and updateable views).

Any secondary indexes use that PK information (when clustered) to help determine where a "page" exists. SQL Server stores information within a table on "pages". A Clustered index (a default condition for a Primary Key)  stores data on the Indexed page and so access becomes a lot more efficient if you do have a clustered index - or have nominated a Primary Key when creating the table.

Now, before we go too much further, I need your help so I can understand your situation.... So a few questions for you :

1) Is the goal is to be able to run the query natively within SQL Server (e.g. to run the query in SSMS) ?
2) What versions of SQL Server and Access are you using ?
3) How far have you progressed the migration of Access (data) to SQL Server ?
4) Are those ID columns autonumber / autoincrement ?
5) In Access are the ID columns Primary Key ?
6) How are you connecting to the SQL Server backend and retrieving data for Access front end ?
7) How much is live - or is this still in transition ?
8) Do you have a timeframe for completing this work ?
9) Can we please get some data samples for those two tables (otherwise it becomes a guessing game) ?
10) Can we please get some data samples from your Access Query ?


When answering the above, please be as descriptive as you can be :)

Ben, start by just removing the domain functions from the query and putting them in the ControlSource of a textbox on the form.

@Pat,

Might not need to, in so much as they appear to be retrieving values from tables (via a query) which can be accommodated a number of different ways in SQL Server. Albeit largely dependant on Ben's response to those questions above.

While we need to understand what those two DLOOKUP's are doing (or more accurately, what the SNVNotesQry is retrieving). They are possibly convertable to an APPLY or in-line subquery. And depending on what version of SQL Server, could even be an aggregate Window Function (e.g. SUM(CountOfPN) over (partition by SNV_ID order by ID) but does require SQL 2012 or more recent - similar to the Dsum() subsequently used in the query).

If we have indexes established on SNV_ID then I think we should be able to accommodate quite efficiently within SQL Server.

And because some of the datatypes are already integer, then columns such as : CInt(IIf([duration]=0,1440,[duration])/60) AS DurationHr
can easily become : IIf([duration]=0,1440,[duration])/60 AS DurationHr

Then aspects such as timeserial probably dont have to be used on datetime / datetime2, and/or convert some integers (shifttohour) to a time base from columns in SQL Server.

So, there is a lot we can do on the SQL Server side without having to change too much at all.

Which is why I have asked some of those questions above, and no doubt will lead to others.

Would like to see what that  SNVNotesQry  is doing.....

Apparently you know more about how Access works than I do.  I'm done.

Oh dear Pat.

Not at all, but I do know a bit about SQL Server. So, please, no need for that....

You may already know the answers to my questions, but I need the clarification - primarily Question 1 and that might change things dramatically.

And yet you are offering solutions that require the use of T-SQL and that will render the ACCESS form not-updateble.

Hi Experts,

@Mark,
1) Is the goal is to be able to run the query natively within SQL Server (e.g. to run the query in SSMS) ?
No, our FE application is Access and I would need that application executing it and returning an Updatable recordset.
2) What versions of SQL Server and Access are you using ?
SQL 2008, Access 2003- 2016, most users have the later version, however I as developer need to work with the oldest version users may have in place...
3) How far have you progressed the migration of Access (data) to SQL Server ?
I was able to migrate all the data into SQL (for testing only).
4) Are those ID columns autonumber / autoincrement ?
Actually since data is only being imported from 3rd party software, were SNV_ID is the PK, I would delete/ignore those ID fields.
5) In Access are the ID columns Primary Key ?
In Access yes, however was only a temp database, and will use SNV_ID as unique identifier for both tables the way they are in original source.
6) How are you connecting to the SQL Server backend and retrieving data for Access front end ?
thru DSN setup, then linking all tables.
7) How much is live - or is this still in transition ?
transition only.
8) Do you have a timeframe for completing this work ?
would love to have it done by tom or Tue, if possible...
9) Can we please get some data samples for those two tables (otherwise it becomes a guessing game) ?
will work on that
10) Can we please get some data samples from your Access Query ?
is this the same as previous request?
Would like to see what that  SNVNotesQry  is doing.....
Note- table is not in normalized state, and as stated above, I'm not in charge of data design structure, it comes from another source...

SELECT Skilled_Nursing_Visit_Note.SNV_ID, Sum(11+(IsNull([Patient_Progress_Notes_1])+IsNull([Patient_Progress_Notes_2])+IsNull([Patient_Progress_Notes_3])+IsNull([Patient_Progress_Notes_4])+IsNull([Patient_Progress_Notes_5])+IsNull([Patient_Progress_Notes_6])+IsNull([Patient_Progress_Notes_7])+IsNull([Patient_Progress_Notes_8])+IsNull([Patient_Progress_Notes_9])+IsNull([Patient_Progress_Notes_10])+IsNull([Patient_Progress_Notes_11]))) AS CountOfNotes, Count(Patient_Progress_Notes.ID) AS CountOfPN, Patient_Progress_Notes.Status
FROM Skilled_Nursing_Visit_Note INNER JOIN Patient_Progress_Notes ON Skilled_Nursing_Visit_Note.SNV_ID = Patient_Progress_Notes.SNVID
GROUP BY Skilled_Nursing_Visit_Note.SNV_ID, Patient_Progress_Notes.Status
HAVING (((Patient_Progress_Notes.Status) Is Null Or (Patient_Progress_Notes.Status)<>"draft"));

Select allOpen in new window

Attaching create table script for that table

Thanks,
Ben
1.txt

@Pat,

This is an continuous form with possible of hundreds of records displayed at the same time (if not more..) which may definitely slow the moving records up/down process,  and some domain function columns are dictating the conditional formatting, therefore they must be part of the underlying query...

P.S. after having the query constructed in SQL, I would then apply your suggestion above of altering the recordsed in the where clause, rather than the changing the filter property, and therefore your first comment was definitely helpful!

Thanks,
Ben

@Jim (Both-:)

I was barely able to convince management to do the conversion at the current time, given a max of one week for conversion.
Therefore unable to focus on things that will require longer time to accomplish, like converting existing queries/code to stored procedures etc...

currently only focusing on short tips that will do the magic...
or rather things that cannot work without it.
may return for those in depth more complete solutions at a later time...

Thanks,
Ben

@Mark,
Attaching some test data of those 3 tables involved, let me know if you need additional info.
Thanks,
Ben
Skilled_Nursing_Visit_Note.xlsx
Patient_Progress_Notes.xlsx
SNV_Printed_History.xlsx

I am unsure if Access is particularly fussed by the ROWVERSION data type, or sensitive to TIMESTAMP - maybe Jim could enlighten.

I don't believe it is, but I'm not sure about the various Access versions vs SQL versions and how JET/ACE determines that column.  I believe the underlying data type in SQL has always been the same, so it shouldn't be an issue.

 Why it's important is that JET/ACE uses it for concurrency checks.   It's the fastest way to tell if a record has been changed by another user.   If that column is not there, then it must execute an update based on the before value of every field in the table.   This is where issues with floats and bit fields creep in and it might tell you that a record has changed even though it has not.   The other issue is performance; the SQL UPDATE must do a WHERE check on every field in the table:

  WHERE Field1Before = Field1 and Field2Before = Field2 and Field3Before = Field3.....and Field100Before = Field100

  To determine if the record has been changed.   With a timestamp/rowversion, it's a check on one field (before value vs now), and there is no issue comparing floats or bit fields.

  A2000/2003 might have an issue with versions after SQL 2008.  Of course database compatibility level might also play a factor here.  SQL 2008 for example can support DB's in SQL 2000 compatibility mode.   Nothing beyond 2008 will though.

 Overall I don't think it will cause issues, but it would show up pretty fast if it did.  

Jim.

Hi Experts,

@Mark,

Which raises a question, how are you importing data into SQL Server ?

Using some append queries...

INSERT INTO Skilled_Nursing_Visit_Note ( SNV_ID, Client_Last_Name, VisitDateInt, Visit_Date, Date_Of_Birth, Medical_Record_Number, Shift_From_Hour, Shift_To_Hour, Purpose_Of_Visit_Goal, Temp, Pulse, RR, BP, O2_Saturation, O2_In_Use, Respiratory_Lung_Sounds, Respiratory_Breathing_Pattern, Bipap, Cpap, Vent, Vent_BIPAP_Settings, Cardiovascular_Skin_Color, Skin_Temp, Cardiovascular_Edema, Neuro_Alert, Neuro_Oriented, Neuro_Self_Directing, Neuro_Dev_Delay, Neuro_Deficits, Neuro_Notes, GI_Oral_Tube_Feeding, Current_Feeds, Gastric_Residuals_Present, Residual_Volume, MD_Called, Abdominal_Assessment, Feeding_Tolerance, GU_Incontinent, Number_Of_Diaper_Changes, Urine_Stool_Volume_Description, Bedbound, WheelChair, Ambulatory, Mobility_Aids, Contractures, Musculoskeletal_Notes, Pain_Score, Pain_Notes, Medication_Changes, Medication_Changes_2, Integument_Skin_Assessment, Skin_Care_Measures_Taken, Home_Environment_Safety, Treatments_Administered, Patient_Response, Instructions, Understanding, Plan_Goal, Nurse_Signature_Last_Name, Signature, Date_Signed, Nurse_Name_Stamp_SNV, Nurse_User_ID_num_SNV, Medication_Changes_Yes, Acknowledge_Accuracy, Trach, CCM_Informed, Tube_Type, Reviewed_Status, Reviewed_By, Reviewed_stamp, Client_First_Name, Nurse_Signature_First_Name, Shift_From_Minute, Shift_To_Minute, Patient_Unable_To_Sign, Reason, Status )
SELECT V_Visit_Note_Export.SNV_ID, V_Visit_Note_Export.Client_Last_Name, Int([V_Visit_Note_Export].[Visit_Date]) AS Expr1, V_Visit_Note_Export.Visit_Date, V_Visit_Note_Export.Date_Of_Birth, V_Visit_Note_Export.Medical_Record_Number, V_Visit_Note_Export.Shift_From_Hour, V_Visit_Note_Export.Shift_To_Hour, V_Visit_Note_Export.Purpose_Of_Visit_Goal, V_Visit_Note_Export.Temp, V_Visit_Note_Export.Pulse, V_Visit_Note_Export.RR, V_Visit_Note_Export.BP, V_Visit_Note_Export.O2_Saturation, V_Visit_Note_Export.O2_In_Use, V_Visit_Note_Export.Respiratory_Lung_Sounds, V_Visit_Note_Export.Respiratory_Breathing_Pattern, V_Visit_Note_Export.Bipap, V_Visit_Note_Export.Cpap, V_Visit_Note_Export.Vent, V_Visit_Note_Export.Vent_BIPAP_Settings, V_Visit_Note_Export.Cardiovascular_Skin_Color, V_Visit_Note_Export.Skin_Temp, V_Visit_Note_Export.Cardiovascular_Edema, V_Visit_Note_Export.Neuro_Alert, V_Visit_Note_Export.Neuro_Oriented, V_Visit_Note_Export.Neuro_Self_Directing, V_Visit_Note_Export.Neuro_Dev_Delay, V_Visit_Note_Export.Neuro_Deficits, V_Visit_Note_Export.Neuro_Notes, V_Visit_Note_Export.GI_Oral_Tube_Feeding, V_Visit_Note_Export.Current_Feeds, V_Visit_Note_Export.Gastric_Residuals_Present, V_Visit_Note_Export.Residual_Volume, V_Visit_Note_Export.MD_Called, V_Visit_Note_Export.Abdominal_Assessment, V_Visit_Note_Export.Feeding_Tolerance, V_Visit_Note_Export.GU_Incontinent, V_Visit_Note_Export.Number_Of_Diaper_Changes, V_Visit_Note_Export.Urine_Stool_Volume_Description, V_Visit_Note_Export.Bedbound, V_Visit_Note_Export.WheelChair, V_Visit_Note_Export.Ambulatory, V_Visit_Note_Export.Mobility_Aids, V_Visit_Note_Export.Contractures, V_Visit_Note_Export.Musculoskeletal_Notes, V_Visit_Note_Export.Pain_Score, V_Visit_Note_Export.Pain_Notes, V_Visit_Note_Export.Medication_Changes, V_Visit_Note_Export.Medication_Changes_2, V_Visit_Note_Export.Integument_Skin_Assessment, V_Visit_Note_Export.Skin_Care_Measures_Taken, V_Visit_Note_Export.Home_Environment_Safety, V_Visit_Note_Export.Treatments_Administered, V_Visit_Note_Export.Patient_Response, V_Visit_Note_Export.Instructions, V_Visit_Note_Export.Understanding, V_Visit_Note_Export.Plan_Goal, V_Visit_Note_Export.Nurse_Signature_Last_Name, V_Visit_Note_Export.Signature, V_Visit_Note_Export.Date_Signed, V_Visit_Note_Export.Nurse_Name_Stamp_SNV, V_Visit_Note_Export.Nurse_User_ID_num_SNV, V_Visit_Note_Export.Medication_Changes_Yes, V_Visit_Note_Export.Acknowledge_Accuracy, V_Visit_Note_Export.Trach, V_Visit_Note_Export.CCM_Informed, V_Visit_Note_Export.Tube_Type, V_Visit_Note_Export.Reviewed_Status, V_Visit_Note_Export.Reviewed_By, V_Visit_Note_Export.Reviewed_stamp, V_Visit_Note_Export.Client_First_Name, V_Visit_Note_Export.Nurse_Signature_First_Name, V_Visit_Note_Export.Shift_From_Minute, V_Visit_Note_Export.Shift_To_Minute, V_Visit_Note_Export.Patient_Unable_To_Sign, V_Visit_Note_Export.Reason, V_Visit_Note_Export.Status
FROM Skilled_Nursing_Visit_Note RIGHT JOIN V_Visit_Note_Export ON Skilled_Nursing_Visit_Note.SNV_ID = V_Visit_Note_Export.SNV_ID
WHERE (((V_Visit_Note_Export.Status) Is Null Or (V_Visit_Note_Export.Status)<>"draft") AND ((Skilled_Nursing_Visit_Note.SNV_ID) Is Null)) OR (((V_Visit_Note_Export.Status)="draft") AND ((Skilled_Nursing_Visit_Note.SNV_ID) Is Null) AND ((V_Visit_Note_Export.Deleted)=True));

Select allOpen in new window

INSERT INTO Patient_Progress_Notes ( PN_ID, Patient_Last_Name, Patient_DOB, [Date], Nurse_Name_Stamp_PN, Nurse_User_ID_num_Pn, Nurse_Signature_Last_Name, Nurse_Title, Patient_Progress_Notes_1, Patient_Progress_Notes_2, Patient_Progress_Notes_3, Patient_Progress_Notes_4, Patient_Progress_Notes_5, Patient_Progress_Notes_6, Patient_Progress_Notes_7, Patient_Progress_Notes_8, Patient_Progress_Notes_9, Patient_Progress_Notes_10, Patient_Progress_Notes_11, Time_1, Time_2, Time_3, Time_4, Time_5, Time_6, Time_7, Time_8, Time_9, Time_10, Time_11, Accept, Patient_First_Name, Nurse_Signature_First_Name, Dateint, Shift_From_Hour, Shift_To_Hour, Shift_From_Minute, Shift_To_Minute, Visit_Date, Status, Visit_Date_Orig )
SELECT V_Progress_Notes_Export.PN_ID, V_Progress_Notes_Export.Patient_Last_Name, V_Progress_Notes_Export.Patient_DOB, V_Progress_Notes_Export.Date, V_Progress_Notes_Export.Nurse_Name_Stamp_PN, V_Progress_Notes_Export.Nurse_User_ID_num_Pn, V_Progress_Notes_Export.Nurse_Signature_Last_Name, V_Progress_Notes_Export.Nurse_Title, V_Progress_Notes_Export.Patient_Progress_Notes_1, V_Progress_Notes_Export.Patient_Progress_Notes_2, V_Progress_Notes_Export.Patient_Progress_Notes_3, V_Progress_Notes_Export.Patient_Progress_Notes_4, V_Progress_Notes_Export.Patient_Progress_Notes_5, V_Progress_Notes_Export.Patient_Progress_Notes_6, V_Progress_Notes_Export.Patient_Progress_Notes_7, V_Progress_Notes_Export.Patient_Progress_Notes_8, V_Progress_Notes_Export.Patient_Progress_Notes_9, V_Progress_Notes_Export.Patient_Progress_Notes_10, V_Progress_Notes_Export.Patient_Progress_Notes_11, V_Progress_Notes_Export.Time_1, V_Progress_Notes_Export.Time_2, V_Progress_Notes_Export.Time_3, V_Progress_Notes_Export.Time_4, V_Progress_Notes_Export.Time_5, V_Progress_Notes_Export.Time_6, V_Progress_Notes_Export.Time_7, V_Progress_Notes_Export.Time_8, V_Progress_Notes_Export.Time_9, V_Progress_Notes_Export.Time_10, V_Progress_Notes_Export.Time_11, V_Progress_Notes_Export.Accept, V_Progress_Notes_Export.Patient_First_Name, V_Progress_Notes_Export.Nurse_Signature_First_Name, Int([V_Progress_Notes_Export].[Date]) AS Expr2, V_Progress_Notes_Export.Shift_From_Hour, V_Progress_Notes_Export.Shift_To_Hour, V_Progress_Notes_Export.Shift_From_Minute, V_Progress_Notes_Export.Shift_To_Minute, V_Progress_Notes_Export.Visit_Date, V_Progress_Notes_Export.Status, V_Progress_Notes_Export.Visit_Date
FROM Patient_Progress_Notes RIGHT JOIN V_Progress_Notes_Export ON Patient_Progress_Notes.PN_ID = V_Progress_Notes_Export.PN_ID
WHERE (((V_Progress_Notes_Export.Status) Is Null Or (V_Progress_Notes_Export.Status)<>"draft") AND ((Patient_Progress_Notes.PN_ID) Is Null)) OR (((V_Progress_Notes_Export.Status)="draft") AND ((Patient_Progress_Notes.PN_ID) Is Null) AND ((V_Progress_Notes_Export.Deleted)=True));

Select allOpen in new window

INSERT INTO SNV_Printed_History ( SNV_ID, VendorsID, ClientLastName, ClientFirstName, VisitDate, ShiftFromHour, ShiftFromMinute, ShiftToHour, ShiftToMinute )
SELECT V_Visit_Note_Export.SNV_ID, Patients.VendorsID, V_Visit_Note_Export.Client_Last_Name, V_Visit_Note_Export.Client_First_Name, V_Visit_Note_Export.Visit_Date, V_Visit_Note_Export.Shift_From_Hour, V_Visit_Note_Export.Shift_From_Minute, V_Visit_Note_Export.Shift_To_Hour, V_Visit_Note_Export.Shift_To_Minute
FROM Patients RIGHT JOIN (SNV_Printed_History RIGHT JOIN V_Visit_Note_Export ON SNV_Printed_History.SNV_ID = V_Visit_Note_Export.SNV_ID) ON (Patients.LastName = V_Visit_Note_Export.Client_Last_Name) AND (Patients.FirstName = V_Visit_Note_Export.Client_First_Name)
WHERE (((SNV_Printed_History.SNV_ID) Is Null));

Select allOpen in new window

Was a little surprised to see a datetime2 in the SQL Server table definitions....

There were some errors originally in my imports due to invalid dates, and was only able to bring them over that way.

As per your recommendations, I did the following.

Set those ID columns as PK for all tables.
Added the TimeStamp column as Jim suggested.
Ran the script you posted to drop/create all indexes.

FYI- I asked users which fields are more common to get filtered by and this was the answer.

Clients and nurses first and last name, no print checked, visit from and to date, reviewed status, reviewed from date and vendor.

So that means each field specified needs a separate index or they can be combined?
The filters will definitely be combined.

I think the only thing missing for now is the view, the rest can be done once the conversion is over and we are live.
what is your opinion?

Thanks,
Ben

There is a fine line between the "right" indexes and overindexing.

You need to carefully assess how the predicates (in joins and the 'where' clause) is going to make a tangible difference. And, how those 'where' predicates are being formed.

using a function on a date (ie cast() or conver() or date() or month() etc) will likely loose any benefit because that would render the date column un-sargible. Similarly using  " where <a column> like '%<some value>%'  " will also cause problems, however " where <a column> like '<some value>%'  "  will work ( in terms of being sargable)

Being sargable is basically helping the SQL Server Optimiser decide if an index can be used.

So when you have a lot of 'where' conditions, the optimiser could well decide to do a scan anyway.

The other thing ypu can do is to INCLUDE columns.

So, you could do :

CREATE CLUSTERED INDEX [PK_Patient_Progress_Notes_SNVID] ON [dbo].[Patient_Progress_Notes] ([ID]);
GO

DROP INDEX ix_Patient_Progress_Notes_SNVID on Patient_Progress_Notes;
GO

CREATE INDEX ix_Patient_Progress_Notes_SNVID on Patient_Progress_Notes (SNVID) include (status);
GO


SELECT Skilled_Nursing_Visit_Note.SNV_ID
--     , Sum(11+(IsNull([Patient_Progress_Notes_1])+IsNull([Patient_Progress_Notes_2])+IsNull([Patient_Progress_Notes_3])+IsNull([Patient_Progress_Notes_4])+IsNull([Patient_Progress_Notes_5])+IsNull([Patient_Progress_Notes_6])+IsNull([Patient_Progress_Notes_7])+IsNull([Patient_Progress_Notes_8])+IsNull([Patient_Progress_Notes_9])+IsNull([Patient_Progress_Notes_10])+IsNull([Patient_Progress_Notes_11]))) AS CountOfNotes
     , Count(Patient_Progress_Notes.ID) AS CountOfPN, Patient_Progress_Notes.Status
FROM Skilled_Nursing_Visit_Note 
INNER JOIN Patient_Progress_Notes ON Skilled_Nursing_Visit_Note.SNV_ID = Patient_Progress_Notes.SNVID
WHERE (((Patient_Progress_Notes.Status) Is Null Or (Patient_Progress_Notes.Status)<>'draft'))
GROUP BY Skilled_Nursing_Visit_Note.SNV_ID, Patient_Progress_Notes.Status

Select allOpen in new window

More later... Gotta duck out.

@Mark,

Not sure i fully understand all those indexing terms...

The other thing ypu can do is to INCLUDE columns

This is what I meant, when asking if I need to create 9 or 10 separate indexes for each of those columns, or I can create one or two indexes with each including 5 of those common filtered columns (if this makes sense...)?

Why did you include the select statement above?

btw, would it be helpful checking up in SQL profiler the SQL statement it currently executes when running the current SQL from Access?

Thanks,
Ben

Given your one week time frame, I think your getting too deep in the weeds here.

Jim.

@Jim,
You're actually right-:)
Hope to get an extension on that, but in order to obtain it I need to show them some significant improvements from the SQL conversion...
As mentioned, I think once I get that SQL statement converted, which will  cause SQL server to execute instead of Access, we are good to go...
Thanks,
Ben

Why did I include the Select Statement ?

Simple, was going to show the difference, but had to duck out....

So, the difference being - for the Optimizer, it changes the way the Patient_Progress_Notes is accessed. It goes from the generally preferable Clustered Index Scan to an Index scan via that new index ix_Patient_Progress_Notes_SNVID. The optimiser has determined that the JOIN + the WHERE criteria is optimally served by using the Index.

And wherever possible use the WHERE clause rather than the HAVING - you want to filter before the aggregation / group by rather than after.

Very generally speaking (as a guide) SQL Server considers the following sequence:

1. FROM
2. WHERE
3. GROUP BY
4. Aggregations
5. HAVING
6. Window Functions
7. SELECT
8. DISTINCT
9. ORDER BY
10. LIMIT

Unfortunately there can be exceptions, but generally if you follow the list, then it does help the Optimiser make the better decisions.

Now, in terms of populating your tables, those insert scripts look fine - They do ignore the ID columns, so assume you have made them IDENTITY columns / PK which given the current structure is a good thing. If not, and they are sitting as INT (without being IDENTITy) then they also need to be populated

Nominating a Primary Key like those create table scripts creates a Clustered Index. The major advantage of a clustered index is it stores data with the Index. Makes finding the data very quick and easy. Joining on those ID's therefore gives access to data (that is an over simplification - but hope you get the picture).

Secondary Indexes are fairly easy to create (and probably contributes to over indexing). The index pointer (row locator) will point to the page containing the data - and in a Clustered table that row locator is actually the clustered index key. That is why it is very important in SQL server to have a clustered index.

For now, wouldnt worry too much just yet about trying to predict which indexes are going to help or hinder.

Having said that, any of the ID type columns are good potential candidates for indexing - because it would seem to be a pointer to other data - like VendorID should ultimately point to Vendor data  (assuming there is a Vendor Table). Think of areas where referential integrity is important. Otherwise, just run the queries for a while.

What you can do, is  copy your queries to SQL Server in SSMS, comment out the Access specific syntax, and look at the Actual Execution Plan and start to tune your queries that way. You could also use the SQL Profiler. And, yes, that is whorthwhile.

Worthwhile reading : https://docs.microsoft.com/en-us/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-described?view=sql-server-2017

Bbut we are getting off-track for the immediate task at hand. This is an evolution and you have only just scratched the surface.

I strongly recommend you go through Jim's post above #a42735620


You mentioned a "view" - what View ?

Hi Mark,
First thanks for the indexing explanation.

You mentioned a "view" - what View ?

I was referring to the SQL statement posted in the initial question, as this part is essential for me to get up and running, to create a view out of that SQL that would be efficient enough and yet UPDATABLE...
Thanks,
Ben

Bit confused with that requirement.

For that query to becomes a View (at least in SQL Server parlance) you would need to convert those functions to the equivelent T-SQL

So, have to assume you mean MS Access VIEW : https://docs.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/create-view-statement-microsoft-access-sql

Here is my confusion... You can have an updateable query - and if the query isnt updateable, then nor will the view be updateable.

Further, I am not so sure you can create an Access View when the Tables are over on SQL Server.

Now, if you were to use pass-through queries, the we could do a truckload more on the SQL Server side (which would also mean converting to SQL Server syntax) - but that is probably down the track and not quite ready for that change (or similar) just yet. Your answer to question 1 put paid to that....

In either SQL Server or Access, there are a few "gotchas" with an updateable view.

Me thinks this is now a different question and needs the expertise of Pat and Jim....

Not trying to renig, rather to direct your attention where you can gain the most benefit.

Hi Experts,
Guess its time to close this question, thank and reward all participants who contributed here...
Will probably open a new thread on the remaining tasks.
In appreciation.
Thanks,
Ben

Ben,

When you do post another question, please post the reference back here as well. Would like to follow your journey through all that lies ahead....

Try to keep each thread succinct and specific to an issue at a time. It might be tempting to load a few issues at a time, but think it will be more beneficial to attack one specific issue at a time.

Kind Regards,
Mark Wills

Hi,

just some comments about this thread:

Timestamp: It's all true what Jim said but the main reason to add a timestamp column is not performance (it makes no big performance difference saving one record in a form comparing each field or one timestamp field). The main reason is that Access datatypes are not fully convertable to SQL Server datatypes and vice-versa. That leads to problems for all "not exact" datatypes like a double floating point value. As Access converts ANY SQL Server datatype to the best fitting JET/ACE datatype this conversion can lead to not exact results when comparing field by field if anything has changed in the meantime. In the end it can happen that Access says the record has changed although nothing has been changed, only because of inaccurate datatype/value conversion. That's the real reason why simply in general add a timestamp column to all tables which are used in Access.

Domain functions: The story about executing one query for each row of the source query is not correct. In fact, "VBA" functions which are usable in SQL (Access SQL) are not the same as the real VBA functions. The have the same syntax but doesn't work the same as if they are called from VBA (simple test, look for the behaviour of the IIf function in VBA and SQL, in VBA always the complete function is resolved, in SQL the first True condition only). We already made some tests in a German Access forum using a domain function in SQL compared to a subquery (which automatically makes a query no more updatable in Access in opposite to a domain function) and could prove that in most cases especially big number of records the domain function performs a lot better than the subquery (Access SQL, please remember, not T-SQL). In fact, in VBA a domain function needs to perform a lot of things like opening a connection, perform a query, close the connection and so on. In case of Access SQL the query optimizer knows from the position of the domain function in the field list that it will need to get more than one result from the subquery so it does what any good optimizer does: It will create internally a result table for the subquery and then return a result value for any fitting ID only. Comparable to if you run a domain function in a VBA loop against a recordset you open before the loop and then only search for a result value in the already opened recordset. Guess what's faster. The same Access SQL does if you use a domain function in a field list: It will of course NOT open/close a connection for each result value, it will NOT query one value only and close the recordset then, if it would do that you could wait for hours for a result of some thousands of records.

This behaviour was improved in different Access versions, I would not guarantee that this will also be the case for such an old version like A2003.

In case of an SQL Server as backend of course it makes no sense to use a domain function in Access SQL, you would use a subquery here in an updatable view. (In T-SQL it is also a good method to use a CTE which unfortunately does not exist in Access SQL.) This view (without ORDER BY) should then be used to be linked to Access and used in a form then. Using a domain function in a field directly is also a bad idea as it performs a lot slower (as Access executes them after everything else in the displaying of the form) and that is a bad feeling for the user especially when scrolling through a lot of records in a continous form.

Cheers,

Christian

@BitSqueezer,

(it makes no big performance difference saving one record in a form comparing each field or one timestamp field).

 For one record in a form, no, certainly not.   But for bulk operations, you certainly would. If you don't have a timestamp field, then the update statement must do a compare on every single field in the table to figure out if the record has been changed by another user.   Of course it depends on a number of factors, such as the width of your tables, number of records, etc, but in general when considering the database as a whole, a timestamp does have performance implications as well.

The story about executing one query for each row of the source query is not correct. In fact, "VBA" functions which are usable in SQL (Access SQL) are not the same as the real VBA functions. The have the same syntax but doesn't work the same as if they are called from VBA (simple test, look for the behaviour of the IIf function in VBA and SQL, in VBA always the complete function is resolved, in SQL the first True condition only).

 It is correct (that it executes once for each row), but I'm not sure what story your referring to.

 It is true that there are differences in expressions which are called from a query vs VBA.    Besides IIF(), there are also things like IsNumeric() that are stubbly different than in VBA.  That's because the query is actually calling things in the JET expression service and not directly in VBA.

 However the real story with the domain functions is that like any expression, the query optimizer cannot see into it, so it has no idea on how to optimize the query with that included.  That has been stated in a number of different MSKB's.

  With that said, it's not clear how much optimization JET actually does with sub selects.   Some say none because if you use JET Showplan, you never see a sub select as part of the costing plan.  It's not clear however if that is simply a lack of the JET showplan function, or if the query optimizer really doesn't bother with any sub select.   It's never been documented either way to my knowledge.

  But there is no doubt that it is called once for each row if it references a field.    When calling expressions if the value will not change, then the expression is executed once at the start of the query and never called again.   But if it references a field, then it will get called for every row and if there is criteria on it, it will get called 2x for each row.

  Performance wise, I've found it's hit or miss on whether a domain function outperforms a subselect or not, and have generally found in most cases they do not.   So I would assume that some optimization is being done, but how much no one outside of Microsoft knows.

 Of course your mileage may vary as obviously there are a large number of variables at work with query execution, but in general I've found domain functions don't work well in queries, and the statement of being not optimizable has been made in a number of places over the years.

Jim.

Hi Jim,

it's simple to test: Just create a database file with a table and write a query which contains a domain function. Then create a public VBA function in a module and create a second query on the table which calls the VBA function, too.

You should be able to execute both in Access itself.

Then go to Excel and use "Insert data from Access". This uses Access as database engine only, no VBA available, so it would also work if Access is even not installed on your machine (because the JET/ACE engine is part of Windows since many versions). What you see is that Excel offers you the table and the query which contains the domain function, it will not list the query which contains the VBA function call.

That simply proves that the domain functions are well-known by the Access database engine WITHOUT any VBA so it's really not the same as the VBA function. And if the database engine is aware of these functions it would be strange as programmer of the database engine not to optimize it's execution. Yes, of course it must deliver one result for each row of the source table - but what do you think happens with a simple JOIN? It also creates a result table in the background first containing a result set which is reduced as much as possible and then joins the fitting values from both tables - row by row. So there is really no big difference if you do the same with a subquery/domain function: Just creating a result table first and then only jump to the right record to insert it into the final table. If indexed (at least in case of SQL Server) and the value in question is in the index itself not even the table is touched for reading, only the index. So there are many possible ways to internally optimize the query for the database engine programmer and why should he just ignore the possibilities?
The only really question is why these programmers make a real subquery automatically read-only for the complete result. But that's the same question for pass-through queries...:-)

Timestamp: In case of bulk operation there is no check if someone else has changed something. If you run i.e. an UPDATE query it will not stop at any point telling you that you can't execute the UPDATE because someone else has changed anything. It will simply overwrite existing values and create a new timestamp value for each changed row.
The need to check happens only in two cases: If you save an edited record in the frontend (any frontend, not only Access) or if you have an offline dataset with a number of records which is possible in Access with ADO only and which is a feature which is used very seldom (most people think that this is possible with .NET only). So a "bulk" operation would only contain some records more, except if you download millions of records to change them offline (would need a large number of changes) and resynchronize them later on and you want to know about each change in the meantime (standard in .NET). But I also doubt that you will ever change a large number of records offline over a long time so that this could ever have issues in performance. Even with a large number the difference would be so small that I doubt it is a measurable difference where someone would say "this is really slow". We are talking about a real database server and not an Access backend. As the scenario in Access is normally only "one record will be saved" performance is really no issue here.

Cheers,

Christian