Passing variables from excel to acess

[alh1002]

I have an excel sheet, which I have pulling data from an access query. The query has many aggregrate functions.

I want to prompt the user to enter a date, this is performance related data, and I want to choose an end date for the data. I do this in query by just adding the following clause to the query

WHERE (((tblPerformance.ladder_date)<=[Enter End Date]));

and if you executed the query in excel you get a date prompt.

I made a macro which is just pushing a button to execute the query (see below code).

Is it possible to pass something to query from excel? or am just stuck?

note: I am using excel because this data is being feed into presentation slides in excel, and linked tables weren't working due to SP2 issues.

Thanks


Sub GetPerformance1()
'
' GetPerformance1 Macro
' Macro recorded 6/2/2006 by Alex Henderson
'

'
Sheets("Sheet2").Select
With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=C:\Documents and Settings\ahenderson\My Documents\may25.m" _
, _
"db;Mode=Share Deny Write;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDBatabase Passw" _
, _
"ord="""";Jet OLEDB:Engine Type=5;Jet OLEDBatabase Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Trans" _
, _
"actions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB" _
, _
"on't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False" _
), Destination:=Range("A1"))
.CommandType = xlCmdTable
.CommandText = Array("qry_Performance2")
.Name = "may25"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = True
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = _
"C:\Documents and Settings\ahenderson\My Documents\may25.mdb"
.Refresh BackgroundQuery:=False
End With
Sheets("Sheet1").Select
End Sub

 

[CautionMP]

alh1002,
Remove the parameter from you query and then pass the user selected date as a [tt]WHERE[/tt] clause in [tt].CommandText[/tt].

...
Dim dteMin As Date
Dim strTemp As String
...
'Get the date from the user, default to today
Do
  strTemp = InputBox("Please enter Date:", "Data Needed", Date)
  If strTemp = "" Then
    'User canceled input box
    End Sub
  End If
Loop Until IsDate(strTemp)
dteMin = CDate(strTemp)
...
        .CommandText = Array("qry_Performance2 [b]WHERE tblPerformance.ladder_date<=#" & dteMin & "#"[/b])
...

Hope this helps,
CMP


(GMT-07:00) Mountain Time (US & Canada)

 

[CautionMP]

P.S. I botched the [tt].CommandText[/tt], it should be a complete SQL string:
[tt]Array("SELECT * FROM qry_Performance2 WHERE ladder_date<=#" & dteMin & "#")[/tt]

CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[alh1002]

I am getting an error when I insert this code

run-time error '1004'

can't find qry_Performance2 WHERE tblPerformance.ladder_date<=#6/2/2006#


note I have multiple versions of this query

qry_Performance does not have a where clause and takes the whole database

qry_Performance2 has the where clause
WHERE (((tblPerformance.ladder_date)<=[Enter End Date]))

is in it


qry_Performance_noenddate which has not end date but does have subqueries built into it so the last date of the DB is one of the returned values


also I am not sure where the end sub should have gone, so I took it out.

i am not familiar with the
.CommandText

is it running the query and then adding the where clause or what is the logic?

thanks

 

[CautionMP]

alh1002,
Try the [tt]CommandText[/tt] from the second posting, I got it worng in the first one.

The [tt]CommandText[/tt] is the actual data source so it will run using the same logic (approximate) that would be used if the query was run in Access.

The other thing you might look at is recording the macro again and setting the filter when you make the connection to your database. I did this and Excel did modify the [tt]CommandText[/tt] to include the WHERE clause but it used a verbage I'm not familiar with.
[tt](WHERE FieldName<=({ts '06/02/2006 00:00:00'}))[/tt].

Hope this helps,
CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[PHV]

but it used a verbage I'm not familiar with
It's ODBC syntax.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886