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
tmurray22Asked:
Who is Participating?
 
PreeceConnect With a Mentor Commented:
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
 
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 SasidharanAssoc Project ManagerCommented:
hi

no need for that semicolon at the end

;-)
Shiju
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.