Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2012
    Posts
    5

    Unanswered: Pass-through query with parameter.

    Dear Expert,

    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

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The pass-through query part is easy:
    Code:
    Private Sub Command_ExecuteQuery_Click()
    
    ' Command_ExecuteQuery is the name of the command button.
    ' Combo_ListTables is the name of the combo that lists the tables.
    '
    ' Replace ServerName and DatabaseName by the actual names on the server.
    '
        Const c_Connection As String = "ODBC;DRIVER={SQL Server};SERVER=ServerName;DATABASE=DatabaseName;Trusted_Connection=Yes;"
        Const c_SQL As String = "DELETE FROM @T;"
        
        Dim qdf As DAO.QueryDef
        Dim strSQL As String
        
        If Not IsNull(Me.Combo_ListTables.Value) Then
            strSQL = Replace(c_SQL, "@T", Me.Combo_ListTables.Value)
            Set qdf = CurrentDb.CreateQueryDef("")
            With qdf
                .Connect = c_Connection
                .SQL = strSQL
                .ReturnsRecords = False
                .Execute
            End With
            Set qdf = Nothing
        End If
            
    End Sub
    It is not possible to write a query that would be able to read a text file and insert the read values into a table. You'll have to either use the import wizard of Access to import the data from the text file into a temporary table, then insert the rows of this temporary table into the server table using a pass-through query, or use the import wizard to import the data into a linked (attached) table. Another solution would consist in writing a procedure that sequencially reads the text file and generates SQL instructions to insert the data into a table with a pass-trough query. This solution requires solid VBA skills.
    Have a nice day!

  3. #3
    Join Date
    Nov 2012
    Posts
    5
    Dear Sinndho,
    Thanks for your kind help, Now it is work with this code:
    Code:
    strSQL = "DELETE FROM " & Me.cboTableList & ";"
        CurrentDb.Execute strSQL, dbFailOnError
    Thnanks
    Mir

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •