• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 150
  • Last Modified:

How do I express the following in a VB script

SELECT mc_tbl_calendar.ID, mc_tbl_calendar.fldNAME, mc_tbl_event.fldCAL_ID, mc_tbl_event.fldBYN, mc_tbl_event.fldEDATE, mc_tbl_event.fldSDATE
FROM mc_tbl_event LEFT JOIN mc_tbl_calendar ON mc_tbl_event.fldCAL_ID = mc_tbl_calendar.ID
WHERE (((mc_tbl_event.fldEDATE)>=(Now())) AND ((mc_tbl_event.fldSDATE)<=(Now())));


I keep getting errors, but it works if I run it as a query in ACCESS


Thanks
tmurray22
0
tmurray22
Asked:
tmurray22
1 Solution
 
PreeceCommented:
give this a shot:

Dim sSQL As String

sSQL = "SELECT mc_tbl_calendar.ID, mc_tbl_calendar.fldNAME, mc_tbl_event.fldCAL_ID, mc_tbl_event.fldBYN, mc_tbl_event.fldEDATE, mc_tbl_event.fldSDATE"
sSQL = sSQL & " FROM mc_tbl_event LEFT JOIN mc_tbl_calendar ON mc_tbl_event.fldCAL_ID = mc_tbl_calendar.ID"
sSQL = sSQL & " WHERE (((mc_tbl_event.fldEDATE)>=(Now())) AND ((mc_tbl_event.fldSDATE)<=(Now())));"
0
 
Shiju SasidharanCommented:
hi

no need for that semicolon at the end

;-)
Shiju
0
 
tmurray22Author Commented:
It works great. Is there any way to script it so I don't have to manually hit the OK button
when I execute the script ?

Here is my code


Dim cn 'As New ADODB.Connection
    Dim rs 'As New Recordset
    Dim sSQL 'As String
    Dim f 'As Integer
    set cn = CreateObject("ADODB.Connection")
    cn.ConnectionString= "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Inetpub\wwwroot\SCM\fpdb\PTDB\MC30.mdb;Persist Security Info=False"
    sSQL = "SELECT mc_tbl_calendar.ID, mc_tbl_calendar.fldNAME, mc_tbl_event.fldCAL_ID, mc_tbl_event.fldBYN, mc_tbl_event.fldEDATE, mc_tbl_event.fldSDATE"
    sSQL = sSQL & " FROM mc_tbl_event LEFT JOIN mc_tbl_calendar ON mc_tbl_event.fldCAL_ID = mc_tbl_calendar.ID"
    sSQL = sSQL & " WHERE (((mc_tbl_event.fldEDATE)>=(Now())) AND ((mc_tbl_event.fldSDATE)<=(Now())))"

    msgbox sSQL
     cn.open
     Set rs = cn.execute(sSQL)
        If Not rs.EOF Then
            set fso = createobject("scripting.filesystemobject")
               set outfile = fso.createtextfile("C:\Systems6.txt")
             Do Until rs.EOF
                 outfile.writeline rs.Fields("fldNAME")
                 rs.MoveNext
            Loop
            outfile.close
        End If
        rs.Close
    cn.Close



Thanks
tmurray22
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
PreeceCommented:
Hmmmmm, supposing you are using a form, you could make a call in the form_load to the procedure containing the code above:

Private Sub Form_Load()
   psnGetCalData

End Sub


private sub psnGetCalData()
    ' your code here
End Sub
0
 
PreeceCommented:
Or, make the call to psnGetCalData from sub main in a module if you don't want to use a form.  
0
 
tmurray22Author Commented:
I want to run in the windows scheduler, I was hoping to do it in a batch file .

tmurray22
0
 
PreeceCommented:
ok....

-  just use a module in your app
-  in sub main make the call to psnGetCalData
-  exit the app
-  compile this as a standard .exe
-  schedule the job

viola!
0
 
Robberbaron (robr)Commented:
dont forget to get rid of the MsgBox call......

you may be best to create a new project & set the unattended execution checkbox on the project / properties page.
wont be able to add forms then.
Then add Sub Main with your code.
Also will have to use a resouces file to assign an icon to your exe so it looks nice :->
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now