?
Solved

query access database and write to a text file

Posted on 2004-11-17
12
Medium Priority
?
131 Views
Last Modified: 2010-05-02
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

 
0
Comment
Question by:tmurray22
  • 7
  • 5
12 Comments
 
LVL 77

Expert Comment

by:GrahamSkan
ID: 12608892
Do you have the Access application?
0
 

Author Comment

by:tmurray22
ID: 12609210
Yes,
Access 2003
0
 
LVL 77

Expert Comment

by:GrahamSkan
ID: 12609321
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
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.

 
LVL 77

Expert Comment

by:GrahamSkan
ID: 12609787
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
 

Author Comment

by:tmurray22
ID: 12610661
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
 

Author Comment

by:tmurray22
ID: 12618385
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
 
LVL 77

Expert Comment

by:GrahamSkan
ID: 12618700
So you are saying that my guess at your table design was wrong?

0
 

Author Comment

by:tmurray22
ID: 12618872
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
 
LVL 77

Expert Comment

by:GrahamSkan
ID: 12619874
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
 
LVL 77

Accepted Solution

by:
GrahamSkan earned 2000 total points
ID: 12620332
 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
 

Author Comment

by:tmurray22
ID: 12666970
I needed a few modifications but now were working
0
 
LVL 77

Expert Comment

by:GrahamSkan
ID: 12667143
Great. Thanks for the grade
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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question