I just tested this and it works in Access 2010. Say you have a SELECT query with parameters: PARAMETERS startID Long, endID Long; SELECT Members.* FROM Members WHERE (((Members.memberID) Between [startID] And [endID])); You run that query interactively and it prompts you for [startID] and [endID]. That works, so you save that query as [MemberSubset]. … Read more
In your example, because you use CurrentDB to execute your INSERT you’ve made it harder for yourself. Instead, this will work: Dim query As String Dim newRow As Long ‘ note change of data type Dim db As DAO.Database query = “INSERT INTO InvoiceNumbers (date) VALUES (” & NOW() & “);” Set db = CurrentDB … Read more
According to http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbcommand.parameters.aspx OleDbCommand does not support named parameter The OLE DB .NET Provider does not support named parameters for passing parameters to an SQL statement or a stored procedure called by an OleDbCommand when CommandType is set to Text. In this case, the question mark (?) placeholder must be used. For example: SELECT * … Read more
Access requires parentheses in the FROM clause for queries which include more than one join. Try it this way … FROM ((tbl_employee INNER JOIN tbl_netpay ON tbl_employee.emp_id = tbl_netpay.emp_id) INNER JOIN tbl_gross ON tbl_employee.emp_id = tbl_gross.emp_ID) INNER JOIN tbl_tax ON tbl_employee.emp_id = tbl_tax.emp_ID; If possible, use the Access query designer to set up your joins. … Read more
I found that using DAO in a specific manner is roughly 30 times faster than using ADO.NET. I am sharing the code and results in this answer. As background, in the below, the test is to write out 100 000 records of a table with 20 columns. A summary of the technique and times – … Read more
No “VALUES”, no parenthesis: INSERT INTO Table2(LongIntColumn2, CurrencyColumn2) SELECT LongIntColumn1, Avg(CurrencyColumn) as CurrencyColumn1 FROM Table1 GROUP BY LongIntColumn1;
They do not both mask errors. DoCmd.SetWarnings masks errors and is system wide, not confined to the single application that you are using. DoCmd.SetWarnings False without the corresponding DoCmd.SetWarnings True will mean that action queries will run without any prompts in any Access application on the PC. Execute does throw warnings, the warnings that you … Read more
Assuming there are not duplicate rows in AA and BB (i.e. all the same values), a full outer join is the equivalent of the union of a left join and a right join. SELECT * FROM AA LEFT JOIN BB ON AA.C_ID = BB.C_ID UNION SELECT * FROM AA RIGHT JOIN BB ON AA.C_ID = … Read more
(The following applies when every row is SQL DISTINCT, and outside SQL code similarly treats NULL like just another value.) Every base table has a statement template, aka predicate, parameterized by column names, by which we put a row in or leave it out. We can use a (standard predicate logic) shorthand for the predicate … Read more
There is perfect logic to the Parentheses Rule in VB(A), and it goes like this. If a procedure (function or sub) is called with arguments, and the call is on a line with other statements or keywords, the arguments must be enclosed in parentheses. This to distinguish the arguments belonging to the procedure call from … Read more