tmurray22
asked on
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
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
Do you have the Access application?
ASKER
Yes,
Access 2003
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?
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)>N ow())) OR (((Q_21210689.enddate)<Now ())) OR (((Q_21210689.endtime)>#12 /30/1899 5:0:0#));
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)>N
ASKER
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 was thinking if I had a VB script with sql I could run it through the windows scheduler.
tmurray622
ASKER
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
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?
ASKER
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
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.OL EDB.4.0;Da ta Source=C:\Documents and Settings\User Name\My Documents\MyDatabase.mdb;P ersist Security Info=False"
strSQL = "SELECT Q_21210689.System " & _
"From Q_21210689 " & _
"GROUP BY Q_21210689.System " & _
"HAVING (((First(Q_21210689.startd ate))>Now( ))) OR (((First(Q_21210689.endtim e))>#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
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.OL
strSQL = "SELECT Q_21210689.System " & _
"From Q_21210689 " & _
"GROUP BY Q_21210689.System " & _
"HAVING (((First(Q_21210689.startd
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I needed a few modifications but now were working
Great. Thanks for the grade