Running combination of multiple normal and crosstab queries in access through VBA

Question

I want to run multiple crosstab and normal queries together in Access. To do that, I took help from answer provided by #Albert D. Kallal here

Idea is to store multiple queries in a text file separated by a delimiter and parse and execute them through VBA. Using this technique, I was able to successfully run multiple normal queries. However, when I try to run include a cross-tab query, I get following error as a message box pop-up.

Run-time error '3000': Reserved error(-3002); there is no message for this error.

Below is a reproducible version of my code and data.

Sub SqlScripts()

   Dim vSql       As Variant
   Dim vSqls      As Variant
   Dim vList      As Variant
   Dim vLists     As Variant
   Dim strSql     As String
   Dim strList    As String
   Dim intF       As Integer
   Dim intL       As Integer
   Dim TableName  As String
   Dim QueryFile  As String
   Dim TableList  As String

   QueryFile = "C:\Staffing_queries.sql"
   TableList = "C:\staffing_tables.txt"        

   intF = FreeFile()
   Open QueryFile For Input As #intF

   intL = FreeFile()
   Open TableList For Input As #intL

   strSql = Input(LOF(intF), #intF)
   strList = Input(LOF(intL), #intL)
   Close intF
   Close intL

   vSql = Split(strSql, ";")
   vList = Split(strList, ";")

     For Each vLists In vList
        TableName = "" & vLists

        If TableExists(TableName) Then
            CurrentDb.TableDefs.Delete TableName
            Debug.Print TableName & " Deleted"
        End If
    Next

'   On Error Resume Next

   For Each vSqls In vSql
        If vSqls <> "" Then
            Debug.Print vSqls
            CurrentDb.Execute vSqls
        End If
   Next

End Sub

Function TableExists(TableName As String) As Boolean
    On Error Resume Next
    Dim strName As String
    strName = CurrentDb.TableDefs(TableName).Name
    TableExists = (Err.Number = 0)
End Function

Contents of Staffing_queries.sql (one crosstab and one normal query)

TRANSFORM Sum(Compens_Data.GRS_YTD) AS SumOfGRS_YTD
SELECT Compens_Data.EMPLID
INTO Test_spot_bonus
FROM Compens_Data
WHERE ((Compens_Data.EARNINGS_DESCRIPTION)="Spot Bonus") 
GROUP BY Compens_Data.EMPLID
PIVOT Compens_Data.BALANCE_YEAR;

SELECT EMPLID, BALANCE_YEAR, 
Sum(IIF(EARNINGS_DESCRIPTION="Spot Bonus",GRS_YTD,0)) as SPOT_BONUS,
Sum(IIF(EARNINGS_DESCRIPTION="Incentive Compensation",GRS_YTD,0)) as Incentive_Comp,
Sum(IIF(EARNINGS_DESCRIPTION="STIC Target",GRS_YTD,0)) as STIC_TRGT
INTO Benefits_Data_Temp
FROM Compens_Data
GROUP BY EMPLID, BALANCE_YEAR
;

Contents of Staffing_table.txt (used by VBA code to delete existing tables)

Test_spot_bonus;Benefits_Data_Temp;

Data Sample (Could not find any option to attach a non-image file)

EMPLID  BALANCE_YEAR    EARNINGS_DESCRIPTION    GRS_YTD
A1  2013    Incentive Compensation  4,926.67
A1  2013    STIC Target 5,108.00
A1  2014    Incentive Compensation  6,230.00
A1  2014    STIC Target 5,394.67
A1  2014    Spot Bonus  720
A1  2015    Incentive Compensation  6,946.67
A2  2013    Incentive Compensation  7,663.33
A2  2013    STIC Target 9,652.67
A2  2014    Incentive Compensation  12,652.00
A2  2014    STIC Target 9,813.33
A2  2015    Incentive Compensation  12,083.33
A3  2013    Incentive Compensation  8,505.33
A3  2013    STIC Target 11,465.33
A3  2014    Incentive Compensation  13,240.00
A3  2014    STIC Target 11,415.33
A3  2015    Incentive Compensation  12,790.00

score 2 · Answer 1 · answered Oct 29 '15 at 23:29

2

The error has nothing to do with the VBA code, you simply can't directly combine a crosstab query with a table creation query.

TRANSFORM ... SELECT ... INTO ... FROM ...

gives the same Reserved error(-3002) when run from the Access query window.

You need to create a querydef, set its .SQL to the TRANSFORM query, and then use this query as basis for a SELECT INTO:

SELECT * INTO Test_spot_bonus FROM MyCrosstabQuery

answered Oct 29 '15 at 23:29

Andre

26,751
7
36
80

Thanks Andre, though I should search and learn (which I will do), could you tell me how to create a querydef. I have never worked with objects in access. – Gaurav Singhal Oct 30 '15 at 07:29
1

See e.g. [here](http://stackoverflow.com/questions/5182087/how-to-view-a-recordset-in-an-access-table-by-means-of-vba/5183493#5183493). You will have to create a If-Then clause in your `For Each vSqls In vSql` loop: If vSqls contains "TRANSFORM", create or edit a querydef with constant name instead of executing the Sql, then in the next vSqls use that query name as base of the SELECT INTO. – Andre Oct 30 '15 at 08:07

Running combination of multiple normal and crosstab queries in access through VBA

1 Answers1