Passing variable from VBA to query

[dunnes]

Hi,
i am running quite a few queries automatically from a module in vba. All th equeries need a week number specifying, is there a way for me to declare a variable in the module that can be passed into the queries? This would dmean that i dont have to change 30 queies on a daily basis!

Cheers
Mike

 

[Norie]

Mike

How are you running the queries?

Can you post the code?

 

[mdmilner]

ahh

Interpreting your message, I'd guess that you've:

Built (30) queries.
Execute them.

Yes, you can include a reference to a function, the function can return a value needed in the WHERE parameter for the queries.

But, a better way might be to run all of this from code, particularly if they are action queries (queries that perform actions instead of SELECT queries)

As an example...for action queries.

Lets say I'm doing:

UPDATE tbl1 SET fld1 = 'Hi' WHERE fld2 = 'No'

And I have to repeat this for 10 different tables that are listed in an 11th table.

Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

Set dbs = CurrentDb()
strSQL = "SELECT * FROM tblSource"
Set rs = dbs.OpenRecordset(strSql, dbOpenSnapShot)

With rs
  Do Until rs.EOF
   strSQL = "UPDATE " & .Fields(0).Value
   strSQL = strSQL & " SET fld1 = 'Hi' WHERE fld2 = 'No'"
   DoCmd.RunSQL strSQL
    .MoveNext
  Loop
End With

Set rs = Nothing
Set dbs = Nothing

What this does, is create a SQL string, and use it to open a Recordset (that really is just the full contents of a table.

It grabs the first field in the table (column 0) and inserts that value (it's a table name) into (reusing strSQL) a SQL update query.

DoCmd.RunSQL executes the query.

Mike

 

[dunnes]

Thanks for your replies...

Norie i am running the queries from the following code:
Sub ExportQueriesForReports()
Dim strFileName As String



strFileName = Left(CodeDb.Name, Len(CodeDb.Name) - Len(Dir(CodeDb.Name))) & "\AreaGraphDataBook1.xls"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "xxAreaGraphBA", strFileName
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "xxAreaGraphHCIncome", strFileName
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "xxAreaGraphMORIncome", strFileName
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "xxAreaGraphMotorIncome", strFileName

each of these queries and more will need to be run with say different variable passed to them ie in the Branch name i would like to pass in 209 branches.

by the way excel 97 is our latest version!!


mdmilner,
your option seems quite complicated for a basic user as myself but i will probably try to have a play with it tomorrow when i have more time
cheers
Mike

 

[Norie]

If tou really want to automate this you will have to go down mdmilne's suggested route.

You'll have to either write code that creates the SQL for each of the queries for each of the branches.

Or change the original queries to accept a parameter (the branch. Then write code to go through all the branches, and supply the parameter to the query.

It isn't actually as hard as it sounds.

Can you post a sample of the SQL behind 1 or 2 of the queries?

Do you have a table with the branches?

 

[dunnes]

thanks for the help....
this one may be slightly easier.

The area id needs to loop between 1 and 22

SELECT AreaNames.[Area ID], [IMP - SAM Branch Activity 2005].Week, Sum([IMP - SAM Branch Activity 2005].[Mortgage Booked Appts]) AS [SumOfMortgage Booked Appts], Sum(Targets___ALL.MWBookedAppts) AS SumOfMWBookedAppts
FROM AreaNames INNER JOIN (Targets___ALL INNER JOIN ([IMP - SAM Branch Activity 2005] INNER JOIN CoreBranchLookup ON [IMP - SAM Branch Activity 2005].[Branch ID] = CoreBranchLookup.[Child Branch ID]) ON (Targets___ALL.Year = [IMP - SAM Branch Activity 2005].Year) AND (Targets___ALL.Week = [IMP - SAM Branch Activity 2005].Week) AND (Targets___ALL.Branch = [IMP - SAM Branch Activity 2005].[Branch ID])) ON AreaNames.[Area ID] = CoreBranchLookup.[Area ID]
GROUP BY AreaNames.[Area ID], [IMP - SAM Branch Activity 2005].Week;

With this one the branch needs to loop through the letters of the alphabet in the like statement

SELECT tbl2004.branch, tbl2004.status, tbl2004.clientName, tbl2004.dateCreated, tbl2004.originator, tbl2004.originatorID, tbl2004.postcode, tbl2004.phoneHome, tbl2004.phoneWork, tbl2004.phoneMobile, tbl2004.homeOwner, tbl2004.loanValue, tbl2004.term, tbl2004.lender, tbl2004.ppi, tbl2004.offerDate
FROM tbl2004 INNER JOIN tblBranches ON tbl2004.branch = tblBranches.branchOriginal
WHERE (((tbl2004.branch)="Contact Centre") AND ((tbl2004.dateCreated)>#11/1/04#) AND ((tbl2004.postcode) Like "a*"))
ORDER BY tbl2004.branch, tbl2004.status, tbl2004.dateCreated;


Cheers
Mike