none
How do I pass a table parameter from MS Access VBA to SQL Server? RRS feed

  • Question

  • I know how to do this in vb.net.

    cmd.Parameters.Add("@IntermediateContTable", SqlDbType.Structured).Value = dt

    How do I do this in Access VBA? I've hunted and hunted and can't find any information on this. I'm using Access 2010.

    Thanks,

    Keith

    Thursday, October 22, 2015 1:29 PM

All replies

  • There are several ways. If you use ADO, you can use an ADODB.Command object.

    If you're using DAO, you have several options, depending if this is a select query or an action query, and if this is a passthrough query or not.

    Here is one example to whet your appetite:

    'ARGUMENTS:
    '   sql - sql statement (SQL SERVER syntax). Be sure to avoid SQL Injection!
    'RETURNS:
    '   Long    - Records affected.
    Public Function Passthrough_ExecuteActionQuery(ByVal sql As String) As Long
        Dim qd As DAO.QueryDef

        Debug.Assert Len(sql) > 0                               'Curious if there is an empty sql statement.

        Set qd = CurrentDB.CreateQueryDef("")                     'Create an in-memory querydef object.
        qd.connect = GetConnectString()
        qd.sql = sql
        qd.ReturnsRecords = False

        qd.Execute dbFailOnError
        Passthrough_ExecuteActionQuery = qd.RecordsAffected
        qd.Close
        Set qd = Nothing
    End Function


    -Tom. Microsoft Access MVP


    Thursday, October 22, 2015 2:13 PM
  • Thanks Tom. I should have mentioned I'm using ADO. Generally when I call a SQL procedure I do one of the following:

    Dim cmd As ADODB.Command
    With cmd
        .Parameters.Append .CreateParameter("@ClientIDs", adVarWChar, adParamInput, -1, strClientIDs)
    End With

    or
    .Parameters.Refresh
    .Parameters.Item("@ClientIDs").value = strClientIDs

    For report sources I generally do this in the OnOpen event of the report (this has worked well for me for a long time):

    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    Dim sSql As String
    
    sSql = "EXEC SomeStoredProcedure '" & Format(dteGetDate, "mm/dd/yyyy") & "', " & strClientIDs
    
    rs.Open sSql, cnnCurrProj, adOpenStatic
    
    Me.RecordSource = rs.Source

    My problem right now is that we ran into an unusual error. When I was able to track it down I found that the sSql string was more than 32,500 characters (the source limit for Access reports). This is due to the fact that when users are able to choose the clients for which to run the report, they generally choose ALL of them. My code in the "choose clients" screen concatenates the ClientID's together into strClientIDs. But as they've grown, the # of clients has increased subsantially so that the sSql is very long.

    In my more recent vb.net work for other clients I've switched over to passing table parameters to the backend instead of strings that I have to parse in the called procedure. I'd like to adopt the same thing in my Access VBA code where needed. Thought I'd do that here.

    I don't think that I can do that using my current code strategy that I use in reports. I think I'm going to have to use the ADODB.Command as you suggested (which is what I normally do when I'm calling other procedures for other purposes). But there is no sql type in VBA that I can find that is similar to the "structured" one in vb.net. That's my hiccup here.





    • Edited by keithrh Thursday, October 22, 2015 7:43 PM
    • Proposed as answer by Asaf_S Saturday, October 31, 2015 3:35 AM
    • Unproposed as answer by Asaf_S Saturday, October 31, 2015 3:35 AM
    Thursday, October 22, 2015 7:28 PM
  • Thanks for clarifying.

    32767 is 2^15-1 and I bet that's a string size limitation held over from 16-bit land in the nineties.

    As a workaround I think you could save the ClientID values to a table, millions if you want to, then have your sproc inner join with that table. Since the server is multi-user, said table would have an additional argument of MachineName, and this value is also passed into the sproc.

    Or re-think the overall situation and in some cases leave out the "where ClientID in (...)" clause altogether.


    -Tom. Microsoft Access MVP

    Friday, October 23, 2015 3:37 AM
  • Yes, all that makes sense. And I have already considered some of what you suggest. But I still need an answer to the subject of my post so that I can try that. Or are you saying it's not possible?

    Keith

    Friday, October 23, 2015 1:10 PM
  • There was a typo in my other reply. The limit is 32,750 per MS:

    https://support.office.com/en-us/article/Access-2010-specifications-1e521481-7f9a-46f7-8ed9-ea9dff1fa854

    Friday, October 23, 2015 4:40 PM
  • Hi Keith,

    >> How do I pass a table parameter from MS Access VBA to SQL Server?

    No, you could not pass a table parameter directly from MS Access Vba to SQL Server. ADO API does not support type-valued parameters (TVPs) directly through ADO parameter objects.

    I agree with Tom, and suggest you try to use inner join instead of using the string in sql statement.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, October 26, 2015 7:22 AM
  • Hello .
    If you have access to the  MS SQL server then
    I think the effective way to accomplish this is by changing the stored proc
    For example, access the server>
    your_database > Programmability > Stored Procedures > your_Proc_Name
    right click > script as alter

    and then you will see somthing like this

    ALTER PROCEDURE [dbo].[YourStoredProcedure] (
         @Cdbl_D_Start DATE datetime
        ,@VAR2 NVARCHAR(max)
    )
    AS
    BEGIN.....
    
    so change it like this
    
    ALTER PROCEDURE [dbo].[YourStoredProcedure] (
         @VAR1 DATE datetime
        ,@VAR2 NVARCHAR(max)
        ,@VAR3 NVARCHAR(max)
    )
    AS
    BEGIN
    set @VAR2 = @VAR2 + @VAR3  
    ......
    

    click execute and if all is well

    you can call the proc like this

    sSql = EXEC SomeStoredProcedure '" & Format(dteGetDate, "mm/dd/yyyy") & "', " &  left( strClientIDs, 30000)  & "','" & mid( strClientIDs, 30000) & "'"

    rs.Open sSql, cnnCurrProj, adOpenStatic

    Good luck

    Asaf

    Saturday, October 31, 2015 3:42 AM