“Make” queries should be avoided. Firstly they do no not copy exactly the same parameters, where field types, field sizes, captions, descriptions etc could be different, or missing. Text fields can default to 255 characters - each. Secondly, they duplicate data, which can be a taboo in the rules of normalisation. (This can also apply to append queries).
As stated previously, ensure that you do not use Append queries to duplicate data.
When Appending to your an already populated table, where some of the records may already exist, use an Append query to add only the new records then use an Update query to update all records that match those in the source. This is a common issue when routinely importing data into your tables from an external source.
Don't fill your Query collection with lots of simple execute queries, such as delete queries.
Use a bit of code to do it for you, such as :
MsgBox CStr(sptExecute("DELETE * FROM MYTABLE WHERE MY_ID=23")) & " Records deleted"
where :
Public Function sptExecute(pstr_SQL As String) As Long
On Error GoTo sptExecute_Error
sptExecute = 0
Dim lng_Affected As Long
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
cmd.ActiveConnection = MyConnection (pre-defined ADBODB connection)
cmd.CommandType = adCmdText
cmd.CommandText = pstr_SQL
cmd.Execute lng_Affected
Set cmd = Nothing
sptExecute = lng_Affected
sptExecute_Exit:
Exit Function
sptExecute_Error:
MsgBox "Function sptExecute() could not process the query : " & CStr(pstr_SQL), vbCritical, "SQL Error"
Resume sptExecute_Exit
End Function
This is a nice way to do this job as the function returns the number of records affected.
When queries are used as a source in only one place, don't save them as queries. Save the SQL in the .RecordSource or .RowSource of the form, report or control. This is to help manage your application, and those that come after you to support it.
It is true that saved queries use the query optimiser, but the difference is minimal compared to the simplification of your application.
Alternatively, don't use a query at all. Build a recordset in the OnOpen event of the form or report.
Parameter queries should not request a User to enter a criteria date. As there are so many ways that a date could be entered by the user, the use of a popup calendar is preferred.
SPT's should not expose security information in their .CONNECT property, such as User ID and password. When an application opens, it should set the connect strings, and when it closes it should clear them. For instance :
Public
Function sptConnect(pbln As Boolean) As Boolean
On Error GoTo sptConnect_Error
sptConnect = False
'---- Adds ODBC String to SPTs
Dim DB_App As Database
Dim qry As QueryDef
Dim int_i As Integer
Dim var_Rtn As Variant
Set DB_App = CurrentDb()
For int_i = 0 To DB_App.QueryDefs.Count - 1
Set qry = DB_App.QueryDefs(int_i)
If qry.Type = 112 Or qry.Type = 144 Then
qry.Connect = IIf(pbln = True, <real ODBC connection string>,
<dummy ODBC connection string>)
End If
qry.Close
DoEvents
Next int_i
If pbln = True Then
On Error GoTo spt_Connect_Fail
var_Rtn = DCount("*", < a small SPT to test>)
On Error GoTo sptConnect_Error
If var_Rtn = 0 Then
MsgBox "Could not read data via ODBC", vbCritical, "ODBC
Failure"
GoTo sptConnect_Exit
End If
End If
sptConnect = True
sptConnect_Exit:
Exit Function
sptConnect_Error:
<your error handler>
Resume sptConnect_Exit
spt_Connect_Fail:
MsgBox "Failed to make valid connection via ODBC", vbCritical, "ODBC Failure"
Resume sptConnect_Exit
End Function
If you use later versions of Access then you would be better off using ADO and dropping the use of SPT's. I've nothing against SPT's, but we've just moved on.
Of course there are no internal saved queries in an ADP. I love these things. Queries are almost as easy to build in Microsoft SQL Server Enterprise Manager, as in Access. Build queries on the fly rather than saving them in the SQL Server database. This gives the developer more autonomy from the DBA.
Where lookup tables are frequently used in queries, static memory-resident data sets should be considered as an alternative to simplify and speed up a query. For instance, if we have the User Key, we don't have to link to the User table, we could use a function to return the UserName in the query, such as :
GetUserName([lng_UserID])This will speed up the query and reduce communication and server costs.
Where possible, MS Access queries should be re-compiled before deployment, to minimise the time spent on query optimisation.
Where backend security is implemented, Users should not be able to read the User ID and Password attributes in the Query.