By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,109 Members | 969 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,109 IT Pros & Developers. It's quick & easy.

How to Run Sql query in VBA code & Macro?

P: 41
Hi,

In VBA code, How can i run Sql query..? I need to delete a record by the below query...

Docmd.runsql(Delete * from Payment_detail_Table where Payment_id=(Select Max(Payment_id) from Payment_Table)

Is it correct..? I tried, but its not working.
-------------
How can i run Sql query from MACRO..?

I put below code in Sql statement coloumn where it shows the below error...

Code:
Delete * from Payment_detail_Table where [Payment_id]=(Select Max([Payment_id]) from Payment_Table)

Error
Missing ),], or Item in query expression '[Payment_id]=(Select Max([Payment_id]) from Payment_Table.

Here, My accomplishment is to delete a record in both table..

Any suggestion would be greatly appreciated if you suggest in both VBA Code & MACRO code..

Thanks in advance..

ngr.
May 15 '07 #1
Share this Question
Share on Google+
5 Replies


code green
Expert 100+
P: 1,726
Expand|Select|Wrap|Line Numbers
  1. Delete * from Payment_detail_Table 
Your SQL syntax looks illegal. Remove the asterisk
Expand|Select|Wrap|Line Numbers
  1. Delete  from Payment_detail_Table 
Then test the query outside of VBA first to make sure iy does as required.
May 15 '07 #2

ADezii
Expert 5K+
P: 8,628
Hi,

In VBA code, How can i run Sql query..? I need to delete a record by the below query...

Docmd.runsql(Delete * from Payment_detail_Table where Payment_id=(Select Max(Payment_id) from Payment_Table)

Is it correct..? I tried, but its not working.
-------------
How can i run Sql query from MACRO..?

I put below code in Sql statement coloumn where it shows the below error...

Code:
Delete * from Payment_detail_Table where [Payment_id]=(Select Max([Payment_id]) from Payment_Table)

Error
Missing ),], or Item in query expression '[Payment_id]=(Select Max([Payment_id]) from Payment_Table.

Here, My accomplishment is to delete a record in both table..

Any suggestion would be greatly appreciated if you suggest in both VBA Code & MACRO code..

Thanks in advance..

ngr.
Your RunSQL Syntax is wrong, here is a Generic Code Template to assist you:
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2.  
  3. 'DELETE ALL Records from Employee Table
  4. DoCmd.SetWarnings False     'don't feel like seeing Deletion Prompts!
  5.   strSQL = "DELETE * FROM tblEmployees;"
  6.   DoCmd.RunSQL strSQL
  7. DoCmd.SetWarnings True
May 15 '07 #3

100+
P: 131
This guy were looking to delete specific records in a table not the whole table from database!
The problem is in his code Delete * from Payment_detail_Table where [Payment_id]=(Select Max([Payment_id]) from Payment_Table)
If you want to delete specific records you don't have to use * (All), instead you have to list all fields in that table where your criteria are met! For example
Expand|Select|Wrap|Line Numbers
  1. Delete MytableName.Field1, MytableName.Field2, MytableName.Field3, MytableName.Field4 from MytableName WHERE MytableName.Field1=Forms!Mytableform!Combobox20
  2.  
Happy Xmas and NewYear
Dec 20 '11 #4

NeoPa
Expert Mod 15k+
P: 31,473
No. I'm sorry, but that's just 100% wrong.
Dec 21 '11 #5

Snoday
P: 2
I found a function designed to fix a problem with a memory leak when running SQL in VBA code. I adapted it and use it heavily in my coding. Below is a great way to run sql in your database!
Expand|Select|Wrap|Line Numbers
  1. Function uRunSQL(SQL As String, Optional sDatabase As String, Optional strPassWord As String) As Boolean
  2. '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  3. ' DESCRIPTION:
  4. '~~~~~~~~~~~~~
  5. ' This function executes the specified SQL statement in the 
  6. ' specified dao.Database.  I used to just use the
  7. ' DoCmd.RunSQL SQL but found it to leak memory.  Sooo, I now wrap the call in a transaction and leave
  8. ' it here in this central function so that future changes can occur in a single location.
  9. '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  10. ' INPUT:
  11. '~~~~~~~
  12. ' SQL   - The string of SQL to execute.
  13. '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  14. ' OUTPUT:
  15. '~~~~~~~~
  16. ' Global Const FNEXIT_ERR / FNEXIT_OK for error or successful run of function (stored proc exit code is returned).
  17. '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  18. ' SAMPLE CALL:
  19. '~~~~~~~~~~~~~
  20. ' If uRunSQL(SQL, "") Then Goto WrapUp
  21. '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  22. ' HISTORY:
  23. '~~~~~~~~~
  24. ' MATPIE    - 02/10/1999 - Wrote function.
  25. ' Yadon     - 09/26/2008 - Update & add db password support
  26. '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  27. Const C_PROC_NAME = "uRunSQL"
  28.  
  29. 'DECLARATIONS:
  30. '~~~~~~~~~~~~~
  31.     Dim cCursor As New clsHourGlass  ' Class Hourglass
  32.     cCursor.SetCursor                ' Replaces DoCmd.Hourglass True
  33.     Dim flgTrans As Boolean
  34.  
  35. 'INITIALIZE:
  36.     '~~~~~~~~~~~
  37.     On Error GoTo ErrHandler
  38.     If (IsMissing(sDatabase) Or sDatabase = "") Then sDatabase = CurrentDb.Name
  39.  
  40.     If IsMissing(strPassWord) Or strPassWord = "" Then
  41.         Set dbs = OpenDatabase(sDatabase, False)
  42.     Else
  43.        Set dbs = OpenDatabase(sDatabase, False, False, "MS Access;PWD=" & strPassWord)
  44.     End If
  45.     strErrNotes = "Passed SQL:" & vbCrLf & SQL
  46.  
  47. 'MAIN BODY:
  48.     '~~~~~~~~~
  49.     flgTrans = True: BeginTrans
  50.     dbs.Execute SQL, dbSeeChanges
  51.     uRunSQL = dbs.RecordsAffected
  52.  
  53.     flgTrans = False: CommitTrans
  54.     uRunSQL = FNEXIT_OK
  55.  
  56. 'WRAP-UP
  57. '~~~~~~~
  58. WrapUp:
  59.     dbs.Close: Set dbs = Nothing
  60.     Exit Function
  61.  
  62. 'ERROR HANDLER
  63. '~~~~~~~~~~~~~
  64. ErrHandler:
  65.     Select Case Err
  66.     Case Else
  67.         If flgTrans Then Rollback: flgTrans = False
  68.         Call LogError(conSevMajor, Err, DBEngine.Errors, C_MODULE_NAME, C_PROC_NAME, strErrNotes, Erl)
  69.     End Select
  70.         uRunSQL = FNEXIT_ERR
  71.     Resume WrapUp
  72. End Function
  73.  
Dec 27 '11 #6

Post your reply

Sign in to post your reply or Sign up for a free account.