Aug 25, 2017 Public Sub createTable Dim dbs As DAO.Database Set dbs = CurrentDb dbs.Execute 'Create Payroll History' End Sub This VBA will cause an error if the table you want to create already exists, so you should either add a line of code to delete the Table first, or consider using Append Query. Append Table / Update Table / Delete Table Records. VBA offers the following methods for executing Action queries in code: RunSQL Executes a SQL statement, not a saved query. Shows messages that require user interaction. OpenQuery Runs a saved query, not a SQL statement. Shows messages that require user interaction. Execute Executes either a SQL statement or a saved query. Does not show messages that require user interaction. Oct 19, 2006 sql Select Count result to variable? Microsoft Access / VBA Forums on Bytes. Set rst = CurrentDb.OpenRecordset('SELECT Count(.) AS Total FROM tblCustomer;') x = rst!Total rst.Close. In VBA, how do you see the results of an sql count statement like the following?
You can create a Recordset object based on a stored select query. In the following code example, Current Product List is an existing select query stored in the current database.
If a stored select query does not already exist, the OpenRecordset method also accepts an SQL string instead of the name of a query. The previous example can be rewritten as follows.
The disadvantage of this approach is that the query string must be compiled each time it runs, whereas the stored query is compiled the first time it is saved, which usually results in slightly better performance.
Support and feedback
Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.
Can someone please help me with the correct way to execute a delete query using vba. I've tried both of these methods and I get an error message. Running the query directly works fine but I want my user to be able to do so with a button click and I'd prefer for it all to happen in the background (i.e., I don't want it to open and see the datasheet).
Query SQL:
Access Vba Execute
DELETE Students_GuardiansAndContacts.StudentID, Students_GuardiansAndContacts.*, GuardiansAndContacts.* FROM GuardiansAndContacts INNER JOIN Students_GuardiansAndContacts ON GuardiansAndContacts.ID = Students_GuardiansAndContacts.GuardianID WHERE (((Students_GuardiansAndContacts.StudentID)=[Forms]![frmStudentInformation]![StudentID]));
Code I've tried:
Private Sub cmdDeleteStudent_Click() If MsgBox('Do you wish to delete this Student?', vbInformation + vbYesNo, 'Delete Confirmation') = vbYes Then If MsgBox('Are you SURE you want to delete this student?' & vbCrLf & _ 'This will permanently delete the student, student years, guardian(s), attendance records and district information.' & vbCrLf & _ 'Once complete it can not be undone.', vbCritical + vbYesNo, '2nd Delete Confirmation') = vbYes Then DoCmd.SetWarnings False CurrentDb.Execute 'DELETE Students_GuardiansAndContacts.StudentID, Students_GuardiansAndContacts.*, GuardiansAndContacts.* FROM GuardiansAndContacts INNER JOIN Students_GuardiansAndContacts ON GuardiansAndContacts.ID = Students_GuardiansAndContacts.GuardianID WHERE (((Students_GuardiansAndContacts.StudentID)=[Forms]![frmStudentInformation]![StudentID]))', dbFailOnError DoCmd.SetWarnings True End If End If End Sub
Error I get: Too few parameters. Expected 1.
And I've tried:
Private Sub cmdDeleteStudent_Click() If MsgBox('Do you wish to delete this Student?', vbInformation + vbYesNo, 'Delete Confirmation') = vbYes Then If MsgBox('Are you SURE you want to delete this student?' & vbCrLf & _ 'This will permanently delete the student, student years, guardian(s), attendance records and district information.' & vbCrLf & _
'Once complete it can not be undone.', vbCritical + vbYesNo, '2nd Delete Confirmation') = vbYes Then DoCmd.SetWarnings False 'Delete Guardian, District, Student attendance,student years DoCmd.RunSQL 'DELETE Students_GuardiansAndContacts.StudentID, Students_GuardiansAndContacts.*, GuardiansAndContacts.* FROM GuardiansAndContacts INNER JOIN Students_GuardiansAndContacts ON GuardiansAndContacts.ID = Students_GuardiansAndContacts.GuardianID WHERE (((Students_GuardiansAndContacts.StudentID)=[Forms]![frmStudentInformation]![StudentID]));' DoCmd.SetWarnings True End If End If
Currentdb Execute Options
End Sub
Error I get: Could not delete the specified tables