How to Run Sql query in VBA code & Macro?

solargovind

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.

Post Reply

code green

Expand|Select|Wrap|Line Numbers

Delete * from Payment_detail_Table

Your SQL syntax looks illegal. Remove the asterisk

Expand|Select|Wrap|Line Numbers

Delete from Payment_detail_Table

Then test the query outside of VBA first to make sure iy does as required.

ADezii

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

 Dim strSQL As String
 
'DELETE ALL Records from Employee Table

DoCmd.SetWarnings False     'don't feel like seeing Deletion Prompts!

  strSQL = "DELETE * FROM tblEmployees;"

  DoCmd.RunSQL strSQL

DoCmd.SetWarnings True

Mr Key

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

 
Delete MytableName.Field1, MytableName.Field2, MytableName.Field3, MytableName.Field4 from MytableName WHERE MytableName.Field1=Forms!Mytableform!Combobox20

Happy Xmas and NewYear

NeoPa

No. I'm sorry, but that's just 100% wrong.

Snoday

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

 
Function uRunSQL(SQL As String, Optional sDatabase As String, Optional strPassWord As String) As Boolean

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

' DESCRIPTION:

'~~~~~~~~~~~~~

' This function executes the specified SQL statement in the 

' specified dao.Database.  I used to just use the

' DoCmd.RunSQL SQL but found it to leak memory.  Sooo, I now wrap the call in a transaction and leave

' it here in this central function so that future changes can occur in a single location.

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

' INPUT:

'~~~~~~~

' SQL   - The string of SQL to execute.

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

' OUTPUT:

'~~~~~~~~

' Global Const FNEXIT_ERR / FNEXIT_OK for error or successful run of function (stored proc exit code is returned).

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

' SAMPLE CALL:

'~~~~~~~~~~~~~

' If uRunSQL(SQL, "") Then Goto WrapUp

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

' HISTORY:

'~~~~~~~~~

' MATPIE    - 02/10/1999 - Wrote function.

' Yadon     - 09/26/2008 - Update & add db password support

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Const C_PROC_NAME = "uRunSQL"
 
'DECLARATIONS:

'~~~~~~~~~~~~~

    Dim cCursor As New clsHourGlass  ' Class Hourglass

    cCursor.SetCursor                ' Replaces DoCmd.Hourglass True

    Dim flgTrans As Boolean
 
'INITIALIZE:

    '~~~~~~~~~~~

    On Error GoTo ErrHandler

    If (IsMissing(sDatabase) Or sDatabase = "") Then sDatabase = CurrentDb.Name
 
    If IsMissing(strPassWord) Or strPassWord = "" Then

        Set dbs = OpenDatabase(sDatabase, False)

    Else

       Set dbs = OpenDatabase(sDatabase, False, False, "MS Access;PWD=" & strPassWord)

    End If

    strErrNotes = "Passed SQL:" & vbCrLf & SQL
 
'MAIN BODY:

    '~~~~~~~~~

    flgTrans = True: BeginTrans

    dbs.Execute SQL, dbSeeChanges

    uRunSQL = dbs.RecordsAffected
 
    flgTrans = False: CommitTrans

    uRunSQL = FNEXIT_OK
 
'WRAP-UP

'~~~~~~~

WrapUp:

    dbs.Close: Set dbs = Nothing

    Exit Function
 
'ERROR HANDLER

'~~~~~~~~~~~~~

ErrHandler:

    Select Case Err

    Case Else

        If flgTrans Then Rollback: flgTrans = False

        Call LogError(conSevMajor, Err, DBEngine.Errors, C_MODULE_NAME, C_PROC_NAME, strErrNotes, Erl)

    End Select

        uRunSQL = FNEXIT_ERR

    Resume WrapUp

End Function

How to Run Sql query in VBA code & Macro?

Post your reply

Question stats

Most Active

Most Questions Answered

Top Questions

Latest Questions

Similar topics

More