Execute SQL procedure code via query string on Excel VBA (query table same workbook)

0
votes

Newbie with SQL. I am trying to execute SQL code through a query string via excel VBA to test a dynamic pivot query from a table stored in a sheet on the same workbook. Normal query's are working perfectly (SELECT), but SQL statements not.

I receive Invalid SQL statement expected; 'DELETE','INSERT'... error again and again

What i want to achieve is an sql query to retrieve a dynamic pivot (rows to variable columns). I don't want to achieve this with pivot tables i would like to do it with SQL string query from VBA.

Sample data

How can this be done?

    On Error GoTo ErrorConexion
    objAdoConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
     "Data Source=" & ThisWorkbook.path & "\" & ThisWorkbook.Name & ";" & _
     "Extended Properties=""Excel 8.0;HDR=YES;FMT=Delimited; IMEX=1;"""

    strSQL = "SET NOCOUNT ON " & Chr(13) _
        &  "CREATE TABLE #tempdata(tmpf1 date, tmpf2 int)" _
        & " INSERT INTO #tempdata(tmpf1,tmpf2)" _
        & " SELECT [DATE], [AMOUNT] FROM " & GetTableRange(StrTable)

    On Error GoTo ErrorSQL
    objAdoRS.Open strSQL, objAdoConn

I would like to execute code like this:

DECLARE @Category AS VARCHAR(MAX)
SELECT @Category = 
    COALESCE(@Category + ', ', '') + CAST(Category AS VARCHAR(20)) 
    FROM (SELECT DISTINCT Category FROM mytable) Books
DECLARE @DynamicPIVOT AS VARCHAR(MAX)
SELECT @DynamicPIVOT = 'SELECT ' + @Category +
' FROM (
    SELECT amount, Category FROM mytable
) Books
PIVOT (
    MAX(amount) FOR Category IN (' + @Category + ')
) Result;'

EXEC (@DynamicPIVOT)

Regards, Vince.

2
sqlexcelvbaado
You are attempting to replicate a SQL Server stored proc (an intricate dynamic pivot) with Jet/ACE -two different SQL engines and dialects. Plus you cannot send multiple SQL commands within a single call with ADO like you can in a stored proc. Please show us data and desired output. – Parfait
Thank you Parfait, i am mixing to much enviroments. I added sample data. – Vince

2 Answers

0
votes

Did you try this? Much easier than creating a temp table, insert and select. You are directly inserting into the temp table by one select statement.

  & "SELECT [DATE], [AMOUNT] INTO #TempData FROM " & 
  GetTableRange(StrTable)
0
votes

Simply use JET/ACE SQL's very own crosstab query to dynamically run the pivot query.

strSQL = "TRANSFORM MAX(t.[AMOUNT]) AS MaxAmount" _
           & " SELECT t.[DATE] " _
           & " FROM [SheetName$] t" _
           & " GROUP BY t.[DATE]" _
           & " PIVOT t.[PEOPLE]"

Do note: there is a limit of 255 columns. So adjustment is needed if PEOPLE are more than 254.