PDA

View Full Version : Parameter query using excel range



squaredealb
08-08-2007, 06:11 AM
Hi All,
I'm not sure if this is the right forum for this topic but I'll give it a shot...
I am trying to run a query of an Access table from excel. i would like to set it up to limit the data returned to excel to records that correspond to values that are present in a range of cells. I recorded a macro of importing the query and then modified the query slightly in the VBA code.

Here's what I have so far:
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 8/6/2007 by SQUAREDEALB
'

'
criteria = Range("A1").Value
With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=C:\Documents and Settings\sqdb\My Documents\sqdbTest." _
, _
"mdb;Mode=Share Deny Write;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Pass" _
, _
"word="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Tran" _
, _
"sactions=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("B1"))
.CommandType = xlCmdTable
.CommandText = Array( _
"SELECT Table1.PROJECT_ID, Table1.EMPLOYEE_ID, Table1.DETAILS FROM Table1 where barcode_id=" & criteria)
.Name = "sqdbTest_2"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = False
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = _
"C:\Documents and Settings\sqdb\My Documents\sqdbTest.mdb"
.Refresh BackgroundQuery:=False
End With
End Sub

With the macro/query set up in this way, I am able to extract the data for the value in cell A1. When I try to set this as a range of more than one cell, I get an error. Any ideas on how I would go about doing this? I'm still very new to this type of stuff so any suggestions or comments on what I've got so far are greatly appreciated. Thanks for the help.

Jeff

austenr
08-08-2007, 06:55 AM
Is your criteria static or can it change frequently?

mdmackillop
08-08-2007, 07:28 AM
Why not send your data from Excel to Access and create/run a query to return your result.

austenr
08-08-2007, 07:47 AM
Malcomb is right. Query your data from Access. You don't have to create an Access table, just link to your data from the "Tables" area in
Access.

Jan Karel Pieterse
08-08-2007, 09:44 AM
You can set up your query to accept parameter input in a cell which also automatically updates the query when you enter a value into the cell:

http://www.dicks-clicks.com/excel/ExternalData6.htm

rory
08-08-2007, 10:51 AM
If you want to use a range of cells, I think you would need to loop through the cell building a comma-delimited criteria string and then change your where clause to:
where barcode_id In (" & criteria & ")")
I am assuming the ids are numeric.

squaredealb
08-08-2007, 04:46 PM
Thanks for all of the input. I'm going to do some research on the different suggestions that have been posed. Rory, you are correct that the IDs are numeric. As for building the comma-delimited criteria string, can you provide any insight into how I would go about this? I'm going to see what I can dig up on my own but again, all help is appreciated.
Thanks again!
Jeff

rory
08-09-2007, 02:26 AM
Hi Jeff,
You would use something like this:
Dim strSQL As String, strWhere As String, strCriteria As String
Dim rngCell As Range, rngCriteria As Range
strSQL = "SELECT Table1.PROJECT_ID, Table1.EMPLOYEE_ID, Table1.DETAILS FROM Table1"
strWhere = " WHERE barcode_id IN (<crits>)"
Set rngCriteria = Range("A1:A10")
For Each rngCell In rngCriteria
If Len(rngCell.Value) > 0 Then strCriteria = strCriteria & "," & rngCell.Value
Next rngCell
If Len(strCriteria) > 0 Then
strWhere = Replace$(strWhere, "<crits>", Mid$(strCriteria, 2))
Else
strWhere = ""
End If
strSQL = strSQL & strWhere
' Debug.Print strSQL

squaredealb
10-10-2007, 01:33 PM
Hi Rory,
I've been away from the thread for quite a while now but I finally got my query working using your input from your last post. Thank you very much for the assistance.

Now that the query is working, I'm trying to set up a refedit control in a userform to allow the user to select the range to query. I am able to return the selected range when I click on a command button but when I try to use the refedit range in my query function, it is empty. Do I need to somehow store the selcted range for use in my query function?

Thanks,
Jeff