query access database and write to a text file

Do you have the Access application?

Yes,
Access 2003

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?

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#));

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

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

So you are saying that my guess at your table design was wrong?

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
 

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

I needed a few modifications but now were working

Great. Thanks for the grade