Create Excel PivotTable that uses Access query as external data so

[alexhatzisavas]

Hello to everyone.

I'm trying to achieve the following in Access VBA:
Create an Excel PivotTable that uses an Access query as its external data
source.

Here's the scenario:
In Access, the User runs a Report query.
I would like to give the User the following option:
Create an Excel PivotTable based on the Access Query data, without passing
the data to Excel (with an ADO recordset for example).

Thus, the Excel PivotTable (that will be created in Access VBA) will use the
Query as an External Data Source, and will connect to it with ODBC.

By recording my actions in Excel, i managed to create the Connection string
in Access VBA, i do however get an error message.

Here's the relevant part of my VBA code in Access:

Public AppXL as Excel.Application

' ...
' (XL has been already initiated, Workbooks added, etc.)

Dim strSQL As String, strQry As String, strCon As String
Dim strAppAccessPath As String, strAppAccessName As String

strQry = "`RQ_ALL INFO (PAR: ANY) - 2`"
strSQL = "SELECT * FROM " & strQry

strAppAccessPath = CurrentProject.Path
strAppAccessName = CurrentProject.Name

strCon = "ODBC;DSN=MS Access Database;"
strCon = strCon & "DBQ=" & strAppAccessPath & "\" & strAppAccessName & ";"
strCon = strCon & "DefaultDir=" & strAppAccessPath & ";"
strCon = _
strCon & "DriverId=25;FIL=MSAccess;MaxBufferSize=2048;PageTimeout=5;"

With AppXL

With .ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
.Connection = strCon
.CommandType = xlCmdSql
.CommandText = strSQL
' The next line generates the error
.CreatePivotTable _
TableDestination:=AppXL.ActiveSheet.Cells(3, 1), _
TableName:="PivotTable2", _
DefaultVersion:=xlPivotTableVersion10
End With
.ActiveWorkbook.ShowPivotTableFieldList = True

End With

The error message:
When the code tries to execute the .CreatePivotTable command above, i get
the following error message:

Title:
ODBC Microsoft Access Driver Login Failed
Text:
The database has been place in a state by User 'Admin' on machine
'My_Machine' that prevents it from being open or locked.

Pressing the OK button opens a "Login" dialog box, and trying to loging to
the database won't work (i don't think that's the problem though).

Any comment or insight would be very welcome.

Thank you for your time,
Alex