The output from the interactive pane is below:

?activesheet.querytables(1).connection
ODBC;DRIVER={Microsoft ODBC for
Oracle};UID=seamus;PWD=xfgtwy;SERVER=animotroi;

This is for the query I set up manually and it works fine.

So, in your code I put
strConn = "ODBC;DRIVER={Microsoft ODBC for
Oracle};UID=seamus;PWD=xfgtwy;SERVER=animotroi;"
strSQL="SELECT Sum(daily_mailboxes.REC_NO)" & strSeparator & _
"FROM daily_mailboxes "

I left the following code as is:

With ActiveSheet.QueryTables.Add(Connection:=strConn,
Destination:=Range("C6"))
.CommandText = Array(strSQL)
End With

I haven't tried anything further yet.

Seamus


"Bill Pfister" wrote in message
...
Seamus, would you mind dumping the connection string to the debug window
and
posting it? Try using the macro recording to nail down the exact syntax.
Do
you have an ODBC connection for your database? In my test case, I
actually
created an ODBC connection (in the Control Panel / Administrative Tools /
Data Sources tool) before running recording the action. That may have an
effect.

Bill


"Seamus Conlon" wrote:

Bill, I modified your code slightly to suit and when I run
it I get an error "Invalid procedure call or argument" at
the first 'With ActiveSheet.QueryTables.Add' line.

I got the connection string from the existing query
so I expect it is correct.

Any ideas on what is wrong?

Seamus

"Bill Pfister" wrote in message
...
Using VBA, you can craft your SQL statement to contain whatever
parameters
you need. In the following example, I'm pointing to a local Access
database,
you would only have to modify the details (driver, etc.) in the connect
string to point to Oracle or another DBMS.


Sub AccessQueryData1()
Dim strFilename As String
Dim strSQL As String
Dim strConn As String
Dim strSeparator As String

strSeparator = Chr(13) & "" & Chr(10)

strFilename = "D:\Data\Test.mdb"
strConn = "ODBC;DSN=0_ptd_test1;DBQ=" & strFilename &
";DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"


strSQL = "SELECT Category.Name" & strSeparator & _
"FROM `" & strFilename & "`.Category Category " &
strSeparator
& _
"WHERE Category.Name < 'Administrative' "


' Focus on only the parameter
With ActiveSheet.QueryTables.Add(Connection:=strConn,
Destination:=Range("C6"))
.CommandText = Array(strSQL)
End With

' All the other parameters
With ActiveSheet.QueryTables.Add(Connection:=strConn,
Destination:=Range("C6"))
.CommandText = Array(strSQL)
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
Range("E11").Select
End Sub