query access database and write to a text file

I need a script that queries an access database for "system" not scheduled and write the system to a text file.

The system names are CFG_01, CFG_02, CFG_03 the criteria is they can not be booked
for the current day after 5:00 pm

My fields are system, startdate, enddate, starttime, currdate

It should go something like

If system = CFG_01 and currdate >= startdate and <= enddate and endtime < 5:00 then
  do nothing
Else
  write cfg_01 to textfile


any help would be appreciated

tmurray22

 
tmurray22Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GrahamSkanRetiredCommented:
Do you have the Access application?
0
tmurray22Author Commented:
Yes,
Access 2003
0
GrahamSkanRetiredCommented:
I believe that a query would take care of that. You could export the results.
Have you ever written any SQL or designed a query in the Acess graphical interface?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

GrahamSkanRetiredCommented:
I have designed a five field table to match your description.
There is one row per system.

I have called it Q_21210689.

I have also designed a query with this SQL that I believe produces a recordset to match your requirements.

SELECT Q_21210689.System
FROM Q_21210689
WHERE (((Q_21210689.startdate)>Now())) OR (((Q_21210689.enddate)<Now())) OR (((Q_21210689.endtime)>#12/30/1899 5:0:0#));
0
tmurray22Author Commented:
The problem is i need to automate this so it runs automatically everyday a 5:30pm.
I was thinking if I had a VB script with sql I could run it through the windows scheduler.


tmurray622
0
tmurray22Author Commented:
I think the query can be simplified so that the end date doesn't matter.
As long as the date = now and the end time > 5:00 pm then skip that record
otherwise write that system number to the text file.

A problem though, I only need a system written once. And if the system gets tagged
by a later record I don't want it all.


Tom
0
GrahamSkanRetiredCommented:
So you are saying that my guess at your table design was wrong?

0
tmurray22Author Commented:
The table design is close except that there can be more than one record per system.

When people book the systems they select
System: CFG_01 or CFG_02 or CFG_03

They select a startdate an enddate a starttime and endtime .

So you can have multiple records with the same system, just not at the same time.

What I have is another script that will read the textfile I generate, deterimine which
systems are open(nothing booked after 5:00 pm or the current time the script is executing) and then run a test program on the open machine.

So I need to automate a query that lets me know which systems are available and write
those results to a text file(so a perl script can read). I only need a system written once though as it can not be availble twice.

tmurray33
 
0
GrahamSkanRetiredCommented:
This is VB6 code. I'm not fluent in VBscript, but I'll try to convert it

Private Sub Command1_Click()
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim strSQL As String
    Dim f As Integer
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\User Name\My Documents\MyDatabase.mdb;Persist Security Info=False"
        strSQL = "SELECT Q_21210689.System " & _
                      "From Q_21210689 " & _
                      "GROUP BY Q_21210689.System " & _
                      "HAVING (((First(Q_21210689.startdate))>Now())) OR (((First(Q_21210689.endtime))>#12/30/1899 5:0:0#));"
        rs.Open strSQL, cn, adOpenStatic, adLockReadOnly, adCmdText
        If Not rs.EOF Then
            f = FreeFile
            Open "C:\Documents and Settings\User Name\My Documents\Systems.txt" For Output As #f
                Do Until rs.EOF
                    Print #f, rs.Fields("system")
                    rs.MoveNext
                Loop
            Close #f
        End If
        rs.Close
    cn.Close
End Sub
0
GrahamSkanRetiredCommented:
 This is the VBScript version

 Dim cn 'As New ADODB.Connection
    Dim rs 'As New Recordset
    Dim strSQL 'As String
    Dim f 'As Integer
    set cn = CreateObject("ADODB.Connection")
    cn.ConnectionString= "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\User Name\My Documents\MyDatabase.mdb;Persist Security Info=False"
    strSQL = "SELECT Q_21210689.System " & _
                      "From Q_21210689 " & _
                      "GROUP BY Q_21210689.System " & _
                      "HAVING (((First(Q_21210689.startdate))>Now())) OR (((First(Q_21210689.endtime))>#12/30/1899 5:0:0#));"
    msgbox strsql
      cn.open
     Set rs = cn.execute(strSQL)
        If Not rs.EOF Then
            set fso = createobject("scripting.filesystemobject")
                  set outfile = fso.createtextfile("C:\Documents and Settings\User Name\My Documents\Systems.txt")
             Do Until rs.EOF
                 outfile.writeline rs.Fields("system")
                 rs.MoveNext
            Loop
            outfile.close
        End If
        rs.Close
    cn.Close
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tmurray22Author Commented:
I needed a few modifications but now were working
0
GrahamSkanRetiredCommented:
Great. Thanks for the grade
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

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.