Menu
May 20, 2005 Database Execute Method. My favorite method for executing SQL, be it saved queries or raw SQL, is the Execute method of the Database object. You can execute a statement with a single line, like this: CurrentDb.Execute 'UPDATE titles SET price = price. 1.10' In this case, CurrentDB references the currently open database.
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:
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 & _ Ms Access Vba Currentdb'Once complete it can not be undone.', vbCritical + vbYesNo, '2nd Delete Confirmation') = vbYes ThenDoCmd.SetWarnings False ![]() 'Delete Guardian, District, Student attendance,student years Vba Currentdb.execute QueryDoCmd.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 Currentdb Execute OptionsEnd If End Sub
Error I get: Could not delete the specified tables
The Classic Traveller Canon The basic reference materials for Traveller as published by GDW between 1977 and 1996 comprise the Traveller canon. The materials on this page were part of the series called Classic Traveller. Classic Traveller appeared in 1977 with a universe set in the golden age of the Third Imperium. The Imperial year 1107 to. 51 rows Classic Traveller (indeed, all of the Traveller editions including later editions) was inducted. Traveller game pdf. Classic Traveller was replaced in 1986 by a new version of the game called MegaTraveller which not only radically revised the mechanics of the game (notably a task based system) but also the format of publication (now in books) and the background setting.
The Execute method executes an 'action' query; it does not open a query in datasheet view, which I assume is what you are attempting. For this sort of 'on the fly' task I use the following function:
Public Function OpenTempQuery(strSQL As String) Dim qdf As DAO.QueryDef Static n As Integer n = n + 1 ' delete temporary querydef object if exists On Error Resume Next CurrentDb.QueryDefs.Delete 'Temp' & n Select Case Err.Number Case 0 ' no error Case 3265 ' temporary querydef does not exist ' ignore error Case Else ' unknown error MsgBox Err.Number, vbExclamation, 'Error' End Select ' create temporary querydef object Set qdf = CurrentDb.CreateQueryDef('Temp' & n) CurrentDb.QueryDefs('Temp' & n).SQL = strSQL ' open query and then delete temporary querydef object DoCmd.OpenQuery 'Temp' & n ' delete temporary querydef object CurrentDb.QueryDefs.Delete 'Temp' & n End Function Call it like this: OpenTempQuery 'SELECT [mess] FROM [etypes] WHERE [etypecode] = 'CURR' Because the integer variable n is declared as Static, giving each temporary querydef object's name a distinct suffix, the function can be called multiple times while leaving each datasheet open. This can be useful for ad hoc 'what if?' comparisons, which is why I wrote it in the first place. Comments are closed.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |