I reformated the code. Try this macro and see if it works. I would consider
the Recordset the table you are extracting which is defined by
Array("qryTRIPGROUP1"). technically the recordset should include the file
name. I number of database command I've worked with are SQL. In this case
your are using OLEDB. The code below should be much easier to understand by
putting each command on its own line.

I don't see from these commands why the error Count = RS_PSQC.Fields.Count
is being displayed. Is there more code after the command below?


Sub test()

With ActiveSheet.QueryTables.Add(Connection:=Array( "OLEDB;" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Password="""";" & _
"UserID=Admin;" & _
"Data Source=C:\Data\TripMission 1.mdb;" & _
"Mode=ReadWrite;Extended Properties="""";" & _
"Jet OLEDB:System database="""";" & _
"Jet OLEDB:Registry Path="""";" & _
"Jet OLEDB:Database Password="""";" & _
"Jet OLEDB:Engine Type=5;" & _
"Jet OLEDB:Database Locking Mode=1;" & _
"Jet OLEDB:Global Partial Bulk Ops=2;" & _
"Jet OLEDB:Global Bulk Transactions=1;" & _
"Jet OLEDB:New Database Password="""";" & _
"Jet OLEDB:Create System Database=False;" & _
"Jet OLEDB:Encrypt Database=False;" & _
"Jet OLEDB:Don't Copy Locale on Compact=False;" & _
"Jet OLEDB:Compact Without Replica Repair=False;" & _
"Jet OLEDB:SFP=False"), _
Destination:=Range("A1"))

.CommandType = xlCmdTable
.CommandText = Array("qryTRIPGROUP1")
.Name = "TripMission 1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = "C:\Data\TripMission 1.mdb"
.Refresh BackgroundQuery:=False
End With

End Sub

"Tony Bender" wrote:

When I try and run the macro here is the error msg I get:

Variable not defined:

Count = RS_PSQC.Fields.Count

Does this mean I need to add this to the other DIM entries? If so how
do I phrase it.

Thank you,