I am new to using VB, so I usually record a script that will perform the task
I wish, then I modify it to be more "general" by substituting variables for
the portions that would change. This has worked well for me in the past, but
my latest task is causing problems.

I recorded the following that creates and Excel Pivot Table from a query
stored in an Access database. As it stands, it works just fine. However, I
would like to be able to change the Access file name and to create the pivot
table in the current workbook and current worksheet.

RECORDING START
With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExter nal)
.Connection = Array(Array( _
"ODBC;DSN=MS Access
Database;DBQ=D:\work\Projects\IHHA_Temp.mdb;Defaul tDir=D:\work\Projects;DriverId=25;FIL=MS Access;MaxBufferSize=2" _
), Array("048;PageTimeout=5;"))
.CommandType = xlCmdSql
.CommandText = Array( _
"SELECT `Quarterly Volume Query`.COUNTY_NAME, `Quarterly Volume
Query`.HOSPITAL_NAME, `Quarterly Volume Query`.`DISCHARGE_DATE By Quarter`,
`Quarterly Volume Query`.`Count Of Inpatient`" & Chr(13) & "" & Chr(10) &
"FROM `D:\work\" _
, "Projects\IHHA_Temp`.`Quarterly Volume Query` `Quarterly Volume
Query`")
.CreatePivotTable TableDestination:="[prodline.xls]Sheet3!R6C1", _
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10
End With
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array( _
"COUNTY_NAME", "HOSPITAL_NAME"), ColumnFields:="DISCHARGE_DATE By
Quarter"
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Count Of
Inpatient"). _
Orientation = xlDataField
RECORDING END

Starting with the Connection statement, I created a variable that contains
the text of everything after the "=" sign as follows:

ConnectText = "Array(Array(" & Chr(34) & _
"ODBC;DSN=MS Access Database;DBQ=" &
"D:\work\Projects\IHHA_Temp.mdb" & _
";DefaultDir=" & "D:\work\Projects" & _
";DriverId=25;FIL=MS Access;MaxBufferSize=2" & _
Chr(34) & Chr(41) & ", " & _
"Array(" & Chr(34) & "048;PageTimeout=5;" & _
Chr(34) & Chr(41) & Chr(41)

I then modified the Connection statement as follows:

.Connection = ConnectText


Every time I execute, this modified statement generates a "Runtime error
1004" error. I have tried using Dim statements to set this variable to
String or Variant to no affect.

Can someone explain what could be wrong?

Also, is the DefaultDir portion of the Connection statement related to the
Excel or Access file? I assume the Access file.


Thanks for the help.