Extracting data from Access using script

[JohnCR]

Hello,

I'd like to extract data from a MS Access query. Can this be done using a script? If so, does anyone have any examples?

I'm trying to get just a date out of the query. I can use a Macro to do this and then run a script to get just the data I need but I'd rather skip the macro if I can and just get the data using a script.

Thanks in advance,

JohnCR

 

[EBGreen]

Do a keyword search for 'Access' in this forum and you will find several threads on this topic.

[blue]"Well, once again my friend, we find that science is a two headed beast. One head is nice, it gives us aspirin and other modern conveniences,...but the other head of science is BAD! Oh, beware the other head of science, Arthur; it bites!!" - The Tick[/blue]

 

[JohnCR]

Well, I got past that. Now I'm trying to get this to work

---------------------------------
dim connection,recordset
dim sSQL,sConnString
dim Linecount

Const ForReading = 1, ForWriting = 2, ForAppending = 8

On Error Resume Next

Set objTextFileout = objFSO.CreateTextFile("C:\###\###" & "out.txt", 2)

'declare SQL statement that will query the database
sSQL="SELECT * FROM [Query - All Tasks]"


'define the connection string, specify database
'driver and the location of database
sConnString="DRIVER={Microsoft Access Driver (*.mdb)};" & _
"DBQ=I:\###\###\errorlog.mdb"

Linecount = 0

Yesterday = DateAdd("d", -1, Date())

'create an ADO connection and recordset
Set connection = CreateObject("ADODB.Connection")
Set recordset = CreateObject("ADODB.Recordset")
Set objFSO = CreateObject("Scripting.FileSystemObject")

'Open the connection to the database
connection.Open sConnString

'Open the recordset object, execute the SQL statement
recordset.Open sSQL,connection

dateinfo = recordset("Date Found")

'first of all determine whether there are any records
'If Recordset.EOF Then
'wscript.echo No records returned.
'Else
'if there are records then loop through the fields
Do While Not recordset.EOF
condate = CDate(strDate)
Wscript.Echo dateinfo
wscript.echo yesterday
wscript.echo Linecount
If recordset = yesterday Then
Linecount = Linecount + 1
End IF
'move on to the next record
recordset.MoveNext
Loop
'End If

objTextFileout.WriteLine("Number of Issues yesterday were " & Linecount)

objTextFileout.Close
Recordset.Close
Connection.Close
Set Recordset = Nothing
Set Connection = Nothing
--------------------------------

All I'm trying to do is read a date record in a data base, compare it to a date (yesterday) and if it is the same increment a counter and then print a line to an out file.

I'm sure the 'If recordset = yesterday Then' line isn't correct but I'm not certain what else it should be.

Thank in advance,

John

 

[EBGreen]

Try:
if recordset.Fields(0) = yesterday

Change Fields(0) to be wichever field is the date.

[blue]"Well, once again my friend, we find that science is a two headed beast. One head is nice, it gives us aspirin and other modern conveniences,...but the other head of science is BAD! Oh, beware the other head of science, Arthur; it bites!!" - The Tick[/blue]

 

[JohnCR]

Actually 'recordset' did the trick.

That's working. But the writing to the file isn't.

Thanks!

 

[JohnCR]

It tells me 'object required: objFSO'

 

[JohnCR]

DOH!

I got it.

Thanks for the help