Use "#" for date qualifiers in your SQL statement (instead of single quotes)
- see the included example. Try returning a a simple Select query ("SELECT
REC_NO FROM daily_mailbox") to determine if you're hitting the connection
properly.

strSQL = "SELECT Sampler.* " & _
"FROM Sampler " & _
"WHERE Sampler.When #1/1/2004#"



"Seamus Conlon" wrote:

Thanks for the tip and I have tried to use it but still have a
problem when including the date parameter.
My SQL is

strsql = "SELECT Sum(REC_NO) " & strSeparator & _
"FROM daily_mailboxes " & strSeparator & _
"WHERE trunc(logdate) = '17-09-2006'"
Range("B10").QueryTable.CommandText = Array(strSQL)
Range("B10").QueryTable.Refresh

This gives a general ODBC error. When I use the following
WHERE clause it is ok.

"WHERE trunc(logdate) = trunc(sysdate)"

I really want to have a variable date, but I can't even get
a literal one to work.

Seamus



"Bill Pfister" wrote in message
...
Seamus, sorry for getting to this earlier, but you can simply change the
SQL
statement of a previously established QueryTable. The following example
modifies the Where clause for the QueryTable that begins in cell C6.



Public Sub TestRefresh()
Dim strSQL As String
Dim strSeparator As String
Dim strFilename As String


strFilename = "D:\Data\Test.mdb"
strSeparator = Chr(13) & "" & Chr(10)

' first query
strSQL = "SELECT Category.Name" & strSeparator & _
"FROM `" & strFilename & "`.Category Category " & strSeparator & _
"WHERE Category.Name < 'Administrative' "
Range("C6").QueryTable.CommandText = Array(strSQL)
Range("C6").QueryTable.Refresh

' need to wait while the first query refreshes
application.wait timeserial(hour(now()),minute(now()),second(now()+ 5))

' second query
strSQL = "SELECT Category.Name" & strSeparator & _
"FROM `" & strFilename & "`.Category Category " & strSeparator & _
"WHERE Category.Name = 'Administrative' "
Range("C6").QueryTable.CommandText = Array(strSQL)
Range("C6").QueryTable.Refresh

End Sub



"Seamus Conlon" wrote:


Sorry, forgot to say that I do have an ODBC connection set up and
it works fine for external linking of the database within Access.

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