locked
Pass-through query with parameter. RRS feed

  • Question

  • Dear Expert,

    I want to use parameter to delete query and I found a VBA module, I don't understand how do this work and how to implement in my database please help, code as shown in below:

    Public Sub CreateSPT(SPTQueryName As String, SQLString As String, _
                ConnectString As String)
    '-----------------------------------------------
    ' SUB: CreateSPT()
    ' PURPOSE:
    '   Creates an SQL pass-through query using the supplied arguments:
    '      SPTQueryName: the name of the query to create
    '      SQLString: the query's SQL string
    '      ConnectString: the ODBC connect string, this must be at
    '         least "ODBC;"
    '-----------------------------------------------
    '   On Error Resume Next
       Dim mydatabase As DAO.Database, myquerydef As DAO.QueryDef
    
       Set mydatabase = DBEngine.Workspaces(0).Databases(0)
       mydatabase.QueryDefs.Delete SPTQueryName
       Set myquerydef = mydatabase.CreateQueryDef(SPTQueryName)
    
       myquerydef.Connect = ConnectString
       myquerydef.SQL = SQLString
       myquerydef.Close
    End Sub
    

    Thanks

    Mir


    Mir Abzal Ali

    Thursday, May 1, 2014 4:18 PM

Answers

  • If you just want to delete one record -- I modified my code sample to use straight Tsql code.  Use this:  use your server name, your database name, your table name and your record ID value.

    Sub DeleteRecord()
       Dim cmd As New ADODB.Command  
                   
       cmd.ActiveConnection = "Provider=SQLOLEDB;Data Source=yourSever;Database=testDB;Trusted_Connection=Yes;" 
       cmd.ActiveConnection.CursorLocation = adUseClient 
       cmd.CommandType = adCommandText  
       cmd.commandText = "Delete * from yourTbl Where someID = 12345"  
    
       cmd.Execute   
       cmd.ActiveConnection.Close  
       Set cmd = Nothing         
    
    End If


    Rich P


    Thursday, May 1, 2014 8:31 PM
  •    cmd.commandText = "Delete * from " & Me.cboTableList & " "

    Your syntax is incorrect.   As an experiment to see if this works -- try typing your table name directly in the commandText (hardcode the table name) instead of getting it from your combobox:

     cmd.commandText = "Delete * from yourTestTbl"

    If this works then you could try something like 

    cmd.commandText = "Delete * from " & Me.cboTableList

    -- & " "  is incorrect syntax in your statement.  I removed it.  But try hardcoding a table name first.  I would try it on a test table on your sql server DB -- just incase it works too good :). 

    Here is something else you could try -- Here is another VBA code sample using ADODB to create a (test) table in your sql Server DB and add three rows of (fake) data to it and then using an ADODB Recordset to retrieve that data to make sure you have data in the table.  You do this all from Access

    Sub CreateSqlTbl_AddData()
        Dim cmd As New ADODB.Command, RSado As ADODB.Recordset
                    
        cmd.ActiveConnection = "Provider=SQLOLEDB;Data Source=MSI-AMD;Database=Pdata;Trusted_Connection=Yes;"
        cmd.ActiveConnection.CursorLocation = adUseClient
        cmd.CommandType = adCommandText
        cmd.commandText = "Create Table testTbl(fld1 varchar(10), fld2 varchar(10))"
        cmd.Execute
    
        '--now add some test data
        cmd.commandText = "Insert Into testTbl Select 'abc1', 'def1' Union All Select 'abc2', 'def2' Union All Select 'abc3', 'def3'"
        cmd.Execute    
    
        cmd.CommandText = "Select * From testTbl"
        Set RSado = cmd.Execute
    
        Do While Not RSado.EOF
           Debug.Print RSado(0) & " -- " & RAado(1)
           RSado.MoveNext
        Loop
    
        cmd.ActiveConnection.Close
        Set cmd = Nothing
     End Sub

    --

    You call this procedure from a command button

    Private Sub button1_Click()
       CreateSqlTbl_AddData
    End Sub


    Rich P






    Friday, May 2, 2014 10:24 PM

All replies

  • Hi Mir,

    If I understand your requirement correctly, that VBA code isn't probably what you're looking for because it doesn't address the "parameter" requirement. Can you post the actual query's SQL so we can help you change it and then create a different VBA function to adjust the parameters? Thanks.

    Thursday, May 1, 2014 4:38 PM
  • Dear,

    Actually I have so many link table which connected to backend SQL Server by ODBC. In the Access Form View I created a combo for list of all table name. I just want to select a table name from the combo and then click a command button which will generate a pass-through query to remove all data from selected table in the combo. And then import new text file in same table.

    Thanks

    Mir


    Mir Abzal Ali


    • Edited by Abzal Ali Thursday, May 1, 2014 5:01 PM
    Thursday, May 1, 2014 5:01 PM
  • It looks like you are using an ODBC connection in your code sample.  So I will assume you are connecting to a sql server, and SPTQueryName looks like it is probably a stored procedure on the sql server.  I myself don't use ODBC and DAO for interacting with a sql server (however, I used DAO a lot within a local Access DB).  For interacting/interfacing with a sql server -- I prefer ADODB.  I provide a VBA code sample for using ADODB to run a stored procedure on a sql server -- from Access.  Assuming you are trying to run a stored procedure on the sql server -- first get the server name from your ODBC DSN and the sql server database name (or get it directly from the sql server if you have access to it).  Then apply these values to the connection string in the VBA code sample below.  You will need to know the parameter name in the stored procedure -- if you don't know it then you WILL need access to the sql server so you can look at the stored procedure code to get the parameter name and data type (int, varchar, datatime).

    '--make a reference to Microsoft ActiveX Data Objects 2.x Library  -- library 2.5 or higher

    Sub RunStoredProcedure()
       Dim cmd As New ADODB.Command  
                   
       cmd.ActiveConnection = "Provider=SQLOLEDB;Data Source=yourSever;Database=testDB;Trusted_Connection=Yes;"
       cmd.ActiveConnection.CursorLocation = adUseClient 
       cmd.CommandType = adCmdStoredProc  
       cmd.commandText = "SPTQueryName"  
    
       cmd.parameters("@paramName").value = theRequiredValue  '--whatever your parameter requires -- text value, numeric value 
    
       cmd.Execute   
       cmd.ActiveConnection.Close  
       Set cmd = Nothing         
    
    End If


    Rich P


    • Edited by Rich P123 Thursday, May 1, 2014 8:33 PM .....
    Thursday, May 1, 2014 5:07 PM
  • Dear,

    Actually I have so many link table which connected to backend SQL Server by ODBC. In the Access Form View I created a combo for list of all table name. I just want to select a table name from the combo and then click a command button which will generate a pass-through query to remove all data from selected table in the combo. And then import new text file in same table.

    Thanks

    Mir


    Mir Abzal Ali


    Hi Mir,

    Maybe it's just me but I don't see the connection where a parameter plays a role in that. If the tables are linked in Access, then a regular DELETE and APPEND queries should work just fine.

    What am I missing?

    Thursday, May 1, 2014 5:16 PM
  • Dear the DB guy,

    It's huge data. Normal Access qeury takes so many times rather than SQL query. And When I use regular Access query to delete then it shows the message "Single-row update/delete affected more than one row of a link table. Unique index contain duplicate values".

    How do I solve this without pass through query.

    Thanks Mir


    Mir Abzal Ali


    • Edited by Abzal Ali Thursday, May 1, 2014 6:15 PM
    Thursday, May 1, 2014 6:10 PM
  • Dear Rich,

    I found another code, could please help me.

    Private Sub Command49_Click()
    Dim dbCurr As DAO.Database
    Dim qdfCurr As DAO.QueryDef
    Dim strSQL As String
    
      strSQL = "DELETE FROM " & Me.cboTableList & " "
      Debug.Print strSQL
      Set dbCurr = CurrentDb()
      Set qdfCurr = dbCurr.QueryDefs("NameOfQuery")
      qdfCurr.SQL = strSQL
    End Sub

    getting error with this line:

    Set qdfCurr = dbCurr.QueryDefs("NameOfQuery")


    Mir Abzal Ali

    Thursday, May 1, 2014 6:26 PM
  • If you just want to delete one record -- I modified my code sample to use straight Tsql code.  Use this:  use your server name, your database name, your table name and your record ID value.

    Sub DeleteRecord()
       Dim cmd As New ADODB.Command  
                   
       cmd.ActiveConnection = "Provider=SQLOLEDB;Data Source=yourSever;Database=testDB;Trusted_Connection=Yes;" 
       cmd.ActiveConnection.CursorLocation = adUseClient 
       cmd.CommandType = adCommandText  
       cmd.commandText = "Delete * from yourTbl Where someID = 12345"  
    
       cmd.Execute   
       cmd.ActiveConnection.Close  
       Set cmd = Nothing         
    
    End If


    Rich P


    Thursday, May 1, 2014 8:31 PM
  • Dear Rich,

    I'm trying with little bit modification as below:

    Private Sub cmdUpdate_Click()
       Dim cmd As New ADODB.Command
                   
       cmd.ActiveConnection = "Provider=SQLOLEDB;Data Source=MSI-AMD;Database=Pdata;Trusted_Connection=Yes;"
       cmd.ActiveConnection.CursorLocation = adUseClient
       cmd.CommandType = adCommandText
       cmd.commandText = "Delete * from " & Me.cboTableList & " "

       cmd.Execute
       cmd.ActiveConnection.Close
       Set cmd = Nothing
    End Sub

    But I'm getting error:

    with this line: 

       cmd.CommandType = adCommandText


    Mir Abzal Ali


    • Edited by Abzal Ali Friday, May 2, 2014 1:49 PM
    Friday, May 2, 2014 1:47 PM
  •    cmd.commandText = "Delete * from " & Me.cboTableList & " "

    Your syntax is incorrect.   As an experiment to see if this works -- try typing your table name directly in the commandText (hardcode the table name) instead of getting it from your combobox:

     cmd.commandText = "Delete * from yourTestTbl"

    If this works then you could try something like 

    cmd.commandText = "Delete * from " & Me.cboTableList

    -- & " "  is incorrect syntax in your statement.  I removed it.  But try hardcoding a table name first.  I would try it on a test table on your sql server DB -- just incase it works too good :). 

    Here is something else you could try -- Here is another VBA code sample using ADODB to create a (test) table in your sql Server DB and add three rows of (fake) data to it and then using an ADODB Recordset to retrieve that data to make sure you have data in the table.  You do this all from Access

    Sub CreateSqlTbl_AddData()
        Dim cmd As New ADODB.Command, RSado As ADODB.Recordset
                    
        cmd.ActiveConnection = "Provider=SQLOLEDB;Data Source=MSI-AMD;Database=Pdata;Trusted_Connection=Yes;"
        cmd.ActiveConnection.CursorLocation = adUseClient
        cmd.CommandType = adCommandText
        cmd.commandText = "Create Table testTbl(fld1 varchar(10), fld2 varchar(10))"
        cmd.Execute
    
        '--now add some test data
        cmd.commandText = "Insert Into testTbl Select 'abc1', 'def1' Union All Select 'abc2', 'def2' Union All Select 'abc3', 'def3'"
        cmd.Execute    
    
        cmd.CommandText = "Select * From testTbl"
        Set RSado = cmd.Execute
    
        Do While Not RSado.EOF
           Debug.Print RSado(0) & " -- " & RAado(1)
           RSado.MoveNext
        Loop
    
        cmd.ActiveConnection.Close
        Set cmd = Nothing
     End Sub

    --

    You call this procedure from a command button

    Private Sub button1_Click()
       CreateSqlTbl_AddData
    End Sub


    Rich P






    Friday, May 2, 2014 10:24 PM