MS Query

[Gustavo]

Hello everyone,
I created a Query in Excel 2000 to retrieve some data
from Access 2000. I used the wizard to do it and I enter
a filter (criteria)to bring only data for one customer at
the time.
I need to have the user selecting the customer from a
dropdown list control(maybe)in Excel and use that result
to refresh the query.
I've looked for help in the KB with no success. I also
try to record a macro but it gives me a very very long
string that won't run again and it is very difficult to
edit.
Does anybody know how to accomplish this?
I thank you all in advance for your help.
Gustavo

 

[Greg Koppel]

Hello Gustavo,

Here is a snippet of code that may help. You will likely have to live with
the long string that is difficult to edit as you can see from the code.

Do Until ActiveCell.Value = ""
Counter = Counter + 1
Application.StatusBar = "Finding data for Lot " & Counter & " of " &
BatchCount & " Lots. "
Workbooks("new edi.xls").Activate
Sheets("Shipments").Activate
ItemNo = Left(ActiveCell.Value, 5) & Right(ActiveCell.Value, 2)
PartNo = ActiveCell.Value
LotNo = Trim(ActiveCell.Offset(0, 1).Value)

' Open connection
cntSQL.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist
Security Info=False;Initial Catalog=testsql;Data Source=SQL"

sSQL = "SELECT DESC_DAT.F_NAME, SGRP_EXT.F_TSNO, SGRP_EXT.F_VAL,
TEST_DAT.F_NAME, SPEC_LIM.F_USL, SPEC_LIM.F_TAR, SPEC_LIM.F_LSL FROM
att.dbo.DESC_DAT DESC_DAT, att.dbo.DESC_GRP DESC_GRP, att.dbo.PART_DAT
PART_DAT, att.dbo.PRCS_DAT PRCS_DAT, att.dbo.SGRP_DSC SGRP_DSC,
att.dbo.SGRP_EXT SGRP_EXT, att.dbo.SPEC_LIM SPEC_LIM, att.dbo.TEST_DAT
TEST_DAT WHERE DESC_DAT.F_DESC = SGRP_DSC.F_DESC AND SGRP_EXT.F_SGRP =
SGRP_DSC.F_SGRP AND PART_DAT.F_PART = SGRP_EXT.F_PART AND TEST_DAT.F_TEST =
SGRP_EXT.F_TEST AND DESC_DAT.F_DSGP = DESC_GRP.F_DSGP AND PRCS_DAT.F_PRCS =
SGRP_EXT.F_PRCS AND SPEC_LIM.F_PART = PART_DAT.F_PART AND SPEC_LIM.F_TEST =
TEST_DAT.F_TEST AND (PRCS_DAT.F_NAME In ('Attachment Final Dimensions','Arm
IP Production Dims','BP IP Production Dims','BP XIP Production Dims')) AND
DESC_DAT.F_NAME='" & LotNo & "' AND PART_DAT.F_NAME='" & PartNo & "'"

'execute the SQL query
Set cmdSQL.ActiveConnection = cntSQL
cmdSQL.CommandText = sSQL
Set rstSQL = cmdSQL.Execute()
If rstSQL.EOF = True Then sSQL = "SELECT DESC_DAT.F_NAME,
SGRP_EXT.F_TSNO, SGRP_EXT.F_VAL, TEST_DAT.F_NAME, SPEC_LIM.F_USL,
SPEC_LIM.F_TAR, SPEC_LIM.F_LSL FROM att.dbo.DESC_DAT DESC_DAT,
att.dbo.DESC_GRP DESC_GRP, att.dbo.PART_DAT PART_DAT, att.dbo.PRCS_DAT
PRCS_DAT, att.dbo.SGRP_DSC SGRP_DSC, att.dbo.SGRP_EXT SGRP_EXT,
att.dbo.SPEC_LIM SPEC_LIM, att.dbo.TEST_DAT TEST_DAT WHERE DESC_DAT.F_DESC =
SGRP_DSC.F_DESC AND SGRP_EXT.F_SGRP = SGRP_DSC.F_SGRP AND PART_DAT.F_PART =
SGRP_EXT.F_PART AND TEST_DAT.F_TEST = SGRP_EXT.F_TEST AND DESC_DAT.F_DSGP =
DESC_GRP.F_DSGP AND PRCS_DAT.F_PRCS = SGRP_EXT.F_PRCS AND SPEC_LIM.F_PART =
PART_DAT.F_PART AND SPEC_LIM.F_TEST = TEST_DAT.F_TEST AND (PRCS_DAT.F_NAME
In ('Attachment Final Dimensions','Arm IP Production Dims','BP IP Production
Dims','BP XIP Production Dims')) AND DESC_DAT.F_NAME='" & Left(LotNo, 7) &
"' AND PART_DAT.F_NAME='" & PartNo & "'"

Set cmdSQL.ActiveConnection = cntSQL
cmdSQL.CommandText = sSQL

'execute the SQL query
Set rstSQL = cmdSQL.Execute()

CurrentWorkbook = FilePrefix & "_" & ItemNo & ".xls"
Workbooks(CurrentWorkbook).Activate
Worksheets("sheet3").Activate
If rstSQL.EOF = True Then MsgBox Prompt:="There is no data for Lot " &
LotNo & ", part number " & PartNo

HTH, Greg

 

[TroyW]

Gustavo,

The property that you want to change is the QueryTable .Sql property. This
is the query string that is used to return the data from the database file.

For example the current MSQuery query string may look something like this:

SELECT Table1.fYear, Table1.fMonth, Table1.fCustomer
FROM `C:\My Documents\Excel\Junk1\Access\Testdb1`.Table1 Table1
WHERE (Table1.fCustomer='myCustomer1')

Below is some VBA code that will modify the example query string to include
the name of the new customer (that you will need to provide). I'm not sure
how familiar you are with the SQL language. Be careful to include spaces
appropriately within the query string. Write back if you need help building
your specific query string (below is an example which won't work for your
database file).

You can use the Get_Current_SQLstring subroutine below to get your
current query string. You can use this information to construct the new
query string.
[Warning: The routine will overwrite the contents of Cell A1 on the first
worksheet.]

Troy


Sub Get_Current_SQLstring()
Dim qt1 As QueryTable
Dim sSQL As String

'''Get the current query string.
Set qt1 = Worksheets(1).QueryTables(1)
sSQL = qt1.Sql

'''Copy the string to cell A1 on Sheet1.
Worksheets(1).Range("A1").Value = sSQL
End Sub


Sub Change_qt1_SQLstring()
Dim qt1 As QueryTable
Dim sSQL As String
Dim sCust As String

'''=== Put your code here... ===
sCust = "myCustomer2" '''<== change this
'''=== (Get the name of the customer the user selected) ===

'''Construct the new query string.

sSQL = "SELECT Table1.fYear, Table1.fMonth, " & _
"Table1.fCustomer " & _
"FROM Table1 " & _
"WHERE (Table1.fCustomer = '" & _
sCust & "')"



'''Print the query string to the debug window so that you can check it.
Debug.Print sSQL

'''Refresh the QueryTable using the new query string.
''' (This example assumes the QueryTable is
''' the first QT on the first worksheet.)
Set qt1 = Worksheets(1).QueryTables(1)
qt1.Sql = sSQL
qt1.Refresh
End Sub