VBA, Excel and MS Query

[Hartke]

I have a complex issue revolving around MS Query links to an Access database. I have a general query written in Access and then use MS Query parameters based on certain cell values to return the data desired in the particular workbook.

In other words, if A1 has "New York" it will return one set of information, as opposed to "Chicago" etc.

The problem I have is that when I copy the sheet the parameter string in MS Query refers back to the original.

So my questions are:

1 Can VBA be used to update an MS Query parameter string in Excel?
2 Is there a way to make the MS Query parameter referential like a cell?

I apologize that I cannot upload any code, but this thing is a mess, what with an Access db, MS Query script, vba macro and target sheets. Any other general advice would be welcome.

 

[Hui]

Hartke

The answer is yes and yes

I would start by using the Macro recorder to record you SQL commands
Then simply add a variable in front of that to extract the values from the cells
and replace the fixed values with the variables in the SQL String

Then link a button to your macro

If that doesn't work post a copy of your file / code here for us to review

 

[dan_l]

I don't follow?

Are you trying to run multiple queries that are identical save for parameters?

 

[Hartke]

Thank you both for the replies. I didn't realize that I could put the MS Query call into the code itself and was looking to use VBA to refresh it.

So now I'm writing the code to call the query and the syntax is a little sticky. I've recorded a macro to use as a base and now am looking to insert the variables. Can you point me towards an example that I can use to make sure the syntax is correct?

Here's what I have now, with the very long strings boiled down and italicized:

  With .Sheets("CommData")
  .ListObjects.Add(SourceType:=0, Source:=Array(Array( _
  "ODBC;DSN=(a long string);DefaultDir=(another long string);DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _
  )), Destination:=Range("$A$1")).QueryTable
  .CommandText = Array( _
  "SELECT `(Many fields) & Chr(13) & "" & Chr(10) & "FROM `location`")
  .RowNumbers = False
  .FillAdjacentFormulas = False
  .PreserveFormatting = True
  .RefreshOnFileOpen = False
  .BackgroundQuery = True
  .RefreshStyle = xlInsertDeleteCells
  .SavePassword = False
  .SaveData = True
  .AdjustColumnWidth = True
  .RefreshPeriod = 0
  .PreserveColumnInfo = True
  .ListObject.DisplayName = "Table_Query_from_MS_Access_Database"
  .Refresh BackgroundQuery:=False

I'm having the dickens of a time getting the WHERE clause written with correct syntax, I miss regular old SQL.

 

[Hartke]

The good news is: I did it! Thanks for setting me on the right path Hui.

Keeping all the syntax straight was a truly miserable experience though, what with the need for double quotes in both the statement and the string. Argh. This may be the reason for the QueryDefs object which was not available to me in Access 2000.

Here is an example of the code I ended up using for the CommandText statement, with the parameters being passed from an excel helper workbook.

strSelect = "SELECT `JH-YoYforMFR`.`Acct #` & Chr(13) & "" & Chr(10) & "FROM `JH-YoYforMFR` `JH-YoYforMFR`"
strFrom1 = Chr(13) & "" & Chr(10) & "WHERE (`JH-YoYforMFR`.Branch="
strParamBr = "'" & strBranch & "'" & ")"
strFrom2 = " AND (`JH-YoYforMFR`.monthnumber<="
strParamPer = currPeriod & ")"
strCommandText = strSelect & strFrom1 & strParamBr & strFrom2 & strParamPer