UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> SQL In VBA - Field Names & Performance, Access 2013    
 
   
john_willmott
post Oct 11 2018, 02:06 PM
Post#1



Posts: 523
Joined: 12-July 03
From: South Wales, UK


Just wondering...


I have some code to modify a table based upon controls and fields on a form.


I am not the worlds best at SQL so I use the QBE interface and copy the SQL into my code and put my control values in as criteria


eg

SQL = "SELECT T_KS3PupilNCRecords.AssessSourceID, T_KS3PupilNCRecords.StaffID, T_KS3PupilNCRecords.DateAchieved " & _
"FROM T_KS3PupilNCRecords " & _
"WHERE T_KS3PupilNCRecords.PID=" & PID & " AND T_KS3PupilNCRecords.StatementID=" & StrandID & ";"
Set rst = CurrentDb.OpenRecordset(SQL, dbOpenDynaset)
With rst
.MoveFirst
.Edit
.Fields(0) = Me.CB_Assessment
.Fields(1) = StaffID
.Fields(2) = Date + Time()
.Update
End With
rst.Close
Set rst = Nothing

The first line of the SQL could be ...
SQL = "SELECT AssessSourceID, StaffID, DateAchieved " & _


Does anyone know...
1) if queries run quicker in VBA using TableName.FieldName or just FieldName ?
2) is there any performance benefit of the ";" at the end of the SQL as produced by the QBE interface - what does it do ?

Just wondering!

Any suggestions welcome!

John


Go to the top of the page
 
GroverParkGeorge
post Oct 11 2018, 02:08 PM
Post#2


UA Admin
Posts: 33,802
Joined: 20-June 02
From: Newcastle, WA


1) I don't think the difference in speed, if any at all, would be noticeable to a user.
2) It should not matter if the SQL string is terminated or not.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 
theDBguy
post Oct 11 2018, 02:10 PM
Post#3


Access Wiki and Forums Moderator
Posts: 73,549
Joined: 19-June 07
From: SunnySandyEggo


Hi John,

Just one person's humble opinion because I don't have any research data to back it up, but I suspect the following:

1. I don't think there's any difference in speed whether you include the name of the table or not in the query. The only problem is if it causes an error, in which case, you'll have to include it anyway.

2. In Access, the ; at the end is optional. However, in other SQL variants, it could be mandatory. It typically just indicates an "end of statement" marker to the database engine.

Just my 2 cents...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
john_willmott
post Oct 11 2018, 02:13 PM
Post#4



Posts: 523
Joined: 12-July 03
From: South Wales, UK


Thanks George,

So if no difference, what is best practice or does it simply not matter?

Should an SQL have the termination or not? If no, why does the QBE always put it on?


John
Go to the top of the page
 
GroverParkGeorge
post Oct 11 2018, 02:13 PM
Post#5


UA Admin
Posts: 33,802
Joined: 20-June 02
From: Newcastle, WA


There are OTHER, very good reasons to prefer fully qualified names (tablename.fieldname), such a clarity to a later developer looking at your SQL, or perhaps to disambiguate fields in two tables with the same names.

You may also see the use of Aliases for Table names to make the SQL more readable. e.g.

SQL = "SELECT KS3.AssessSourceID, KS3.StaffID, KS3.DateAchieved " & _
"FROM T_KS3PupilNCRecords AS KS3" & _
"WHERE KS3.PID=" & PID & " AND KS3.StatementID=" & StrandID & ";"

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 
GroverParkGeorge
post Oct 11 2018, 02:14 PM
Post#6


UA Admin
Posts: 33,802
Joined: 20-June 02
From: Newcastle, WA


It's the standard, so it should be included, but Access is fine without it.

I prefer fully qualified naming in most situations because I'm old and easily confused.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 
john_willmott
post Oct 11 2018, 02:18 PM
Post#7



Posts: 523
Joined: 12-July 03
From: South Wales, UK


Thanks DBGuy.

When I am doing complex queries in SQL, I generally leave the TableName.FieldName as it is easier to understand when code is revisited.

If there is no performance difference, will continue to do so!!

Cheers
John
Go to the top of the page
 
john_willmott
post Oct 11 2018, 02:21 PM
Post#8



Posts: 523
Joined: 12-July 03
From: South Wales, UK


Thanks George,

That is a nice idea - it does read easier especially if you end up revisiting code.

I am old too and am still learning!!


Thanks again

John
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    23rd October 2018 - 11:49 PM