By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,064 Members | 1,445 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,064 IT Pros & Developers. It's quick & easy.

Question with

P: 98
Can someone please explain to me what the line of code:

qdf.SQL = strSQL
is accomplishing?
1.strSQL = "SELECT DailyPrice.Symbol, DailyPrice.LocateDate, DailyPrice.MarketPrice, DailyPrice.PriceFlag FROM DailyPrice " & _
"WHERE DailyPrice.Symbol IN (" & strCriteria & ") " & _
"ORDER BY DailyPrice.Symbol, DailyPrice.LocateDate;"
'*****great stuff here type: Set qdf = db.querydefs into thescripts
Set qdf = CurrentDb.QueryDefs("qryDummy")
qdf.SQL = strSQL
DoCmd.OpenQuery ("qryDummy")

Private Sub cmbSelectionDone_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String

Set db = CurrentDb()
Set qdf = db.QueryDefs("qryMultiSelect")
If Me!cboxRangeName = -1 Then
myPath = txtFilePath & txtImportName
myExport = Me!txtExportName
myImport = Me!txtImportName
DoCmd.SetWarnings (False)
DoCmd.RunSQL "DELETE * FROM TempSymbol"
DoCmd.SetWarnings (True)
DoCmd.TransferSpreadsheet acImport, , "TempSymbol", "" & myPath & "", True, "" & txtRangeName & ""
MsgBox "The symbols have been successfully imported."
strSQL = "SELECT DailyPrice.Symbol, DailyPrice.LocateDate, DailyPrice.MarketPrice, DailyPrice.PriceFlag " & _
"FROM TempSymbol, DailyPrice " & _
"WHERE (((DailyPrice.Symbol) = [TempSymbol]![Symbol])) " & _
"ORDER BY DailyPrice.Symbol, DailyPrice.LocateDate;"
qdf.SQL = strSQL
'check this out...maybe delete it later
myPath = txtFilePath & txtExportName
DoCmd.TransferSpreadsheet acExport, , "qryMultiSelect", "" & myPath & ""
MsgBox "The table have been successfully exported to: " & myExport & "."
DoCmd.Close
qdf.Close
Set db = Nothing
Set qdf = Nothing
Exit Sub..................
Oct 25 '06 #1
Share this Question
Share on Google+
4 Replies


NeoPa
Expert Mod 15k+
P: 31,494
Expand|Select|Wrap|Line Numbers
  1. Set qdf = CurrentDb.QueryDefs("qryDummy")
  2. qdf.SQL = strSQL
  3. DoCmd.OpenQuery ("qryDummy")
First line sets qdf to point to CurrentDb.QueryDefs("qryDummy").
Second line amends the embedded SQL within qdf ==> CurrentDb.QueryDefs("qryDummy") to be strSQL.
Third line executes the amended query.

This is an extended way of executing SQL code within VB - similar to DoCmd.RunSQL().

HOWEVER, it is possible that certain QueryDef properties have been set in qryDummy which would effect how the query runs - so may not execute in EXACTLY the same way.

I use a similar method when I want to execute SQL code which needs a longer TimeOut for instance.
Oct 25 '06 #2

P: 98
thank you for your explanation...so if I understand correctly, when I create a statement

strSQL = "SELECT DailyPrice.Symbol, DailyPrice.LocateDate, DailyPrice.MarketPrice, DailyPrice.PriceFlag " & _
"FROM TempSymbol, DailyPrice " & _
"WHERE (((DailyPrice.Symbol) = [TempSymbol]![Symbol])) " & _
"ORDER BY DailyPrice.Symbol, DailyPrice.LocateDate;"
qdf.SQL = strSQL
DoCmd.TransferSpreadsheet acExport, , "qryDummy", "" & myPath & ""

I am creating a query "strSQL" and changing the definition already in place by qryDummy and then executing qryDummy with this NEW definition of strSQL. Am I close?!!! Thanks again for your help!
Oct 25 '06 #3

NeoPa
Expert Mod 15k+
P: 31,494
Yes close.

You cannot leave out the line
Expand|Select|Wrap|Line Numbers
  1. Set qdf = CurrentDb.QueryDefs("qryDummy")
though (unless that is entered earlier somewhere).

You are not 'creating' a query here as such : You are updating a previously existing query in the database, called 'qryDummy'.
What you're doing is changing the SQL part of the query to what you've just set up in strSQL.
The TransferSpreadsheet line will, however, execute the SQL found in strSQL (and save it to myPath), but only because you transferred it to qdf.SQL or qryDummy.SQL (<== that is not valid code - it is there merely for illustrative purposes).
Oct 25 '06 #4

P: 98
Thank you very much for the explanation...I get it now!
Oct 26 '06 #5

Post your reply

Sign in to post your reply or Sign up for a free account.