Writing Microsoft Access table to text file 2

[pellet]

Hello everyone,

First of all, I am new to scripting and I am trying to learn it on my own using books and the internet. Right now I am trying to write a program that will open an existing access database, copy (reverse order preferably) the data out of one column of a table to a text file. I then intend on searching the text file for needed information.

My question is, I think I can get the database opened, I just can't seem to get the data written to the text file. I have pasted some code that I am using, the database name is eventlogs, the table name is eventtable, and the column I need to search is called "type" without the quotes and "Error" is what I am searching for.

-------------------------------------
Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
"Provider = Microsoft.Jet.OLEDB.4.0; " & _
"Data Source = "C:\eventlogs.mdb"

objRecordSet.Open "SELECT * FROM EventTable " & _
"WHERE Type = 'Error'", objConnection, adOpenStatic, _
adLockOptimistic

objRecordSet.MoveFirst
Do Until objRecordset.EOF = True
objRecordset.Writeline("C:\file.txt")
objRecordset.MoveNext

Loop

objRecordSet.Close
objConnection.Close
--------------------------------------

I apologize if this post is incorrectly posted or does not have enough information. This is my first post here.

Thank you for your help.
Pete

 

[tsuji]

>objRecordset.Writeline("C:\file.txt")
No such thing exists.

To do "writing" to a file, you can simply use scripting.filesystemobject.
[tt]
Const adOpenStatic = 3
Const adLockOptimistic = 3
[blue]
sfile="C:\file.txt"
set fso=createobject("scripting.filesystemobject")
[/blue]
'etc etc...
[red]'[/red]objRecordSet.MoveFirst
[red]'[/red]Do Until objRecordset.EOF = True
[red]'[/red]objRecordset.Writeline("C:\file.txt")
[red]'[/red]objRecordset.MoveNext
[red]'[/red]Loop
[blue]
'adClipString=2, etc
'check all the meaning of magic numbers in the methods yourself
fso.opentextfile(sfile,2,true,-2).write objRecordset.getString(2,-1,",",vbcrlf)
[/blue]
'etc etc
[blue]set fso=nothing[/blue]
[/tt]

 

[pellet]

Thank you so much! That worked! Now I can concentrate on parsing the text file from the bottom up looking for certain items.

Thanks again!

 

[ettienne]

Now I can concentrate on parsing the text file from the bottom up looking for certain items.

Why not query the MDB file?
Heck that's a lot easier.

 

[pellet]

Would you be able to point me in the right direction as to querying the MDB file? I need to parse out some information, copy it to a varible, and then dump that variable into a different program...

I am trying to do it one step at a time.

Thanks so much for all your help!

 

[ettienne]

You are already part way there in your first post.
Rewrite your Select query to get the records you are looking for, and then loop through them and do your stuff.

 

[pellet]

I think I know what you mean by rewriting the Select query, but would you (or anyone) be able to answer two questions for me?

1 - Is there a way to query the access database table from the bottom up?

2 - This may sound foolish and I apologize for asking, but I do not know how to find the text I am looking for and then "cut and pasted" into the other program...

Thank you all so much for reading my posts. Again, I apologize for not having the knowledge I need for this script. I hope to be able to learn a lot more and help others with my scripting experiences.

 

[pellet]

Sorry, I wanted to be a little more specific. My variable that I will be passing to the other program will be constantly changing and will never be the same. The newest additions to the access database are listed at the bottom few lines, but the variable I want to pass always starts out with the letters PMC: and then has 18 number after it. The numbers are what I am interested in passing along to the next program in a variable form. Sometimes, two PMC numbers are in the same line - when that happens, I would like to pass them both to the other program... I really hope that all makes sense.

As always, thank you!

 

[ettienne]

To query the table from the bottom up, use this in your first post:

objRecordSet.MoveLast
Do While Not objRecordset.BOF
   'Do your stuff
   objRecordset.MovePrevious
Loop

 

[ettienne]

Basically you want to find PMC in a field in the record set.
Say you have a field named TEXT in your record set, then

Msgbox InStr(objRecordst("TEXT"),"PMC")

will return the first occurrence of PMC. This returns the position of PMC withing TEXT, zero if not found. So to extract the text you are looking for you can use a combination of Mid() and InStr().

Will the second PMC be in the same field, or a different field?

 

[pellet]

It would be in the same field, in fact, once the PMC number is found from the bottom up, I need it to stop retrieving the numbers. I only need more than one number if they are on the same line. The PMC doesn't need to be included in the variable - only the 18 numbers after it.

Thank you so much for your responses. I hope you don't feel I am asking you to write the script for me, I am just looking for advice and knowledge because I have hit a road block.

 

[ettienne]

Add a variable to your script:

Dim Position

and then use this to get the number portion of PMC:

objRecordSet.MoveLast
Do While Not objRecordset.BOF
   Position = InStr(objRecordst("TEXT"),"PMC")
   'Check for first occurrence
   If Position > 0 then
       Msgbox Mid(objRecordst("TEXT"),Position + 3, 18)
       Position = InStr(Position + 3, objRecordst("TEXT"),"PMC")
       'Check for second occurrence
       If Position > 0 then
           Msgbox Mid(objRecordst("TEXT"),Position + 3, 18)
       end if
       Exit Do
   end if
   objRecordset.MovePrevious
Loop

 

[Remou]

It should be possible to extract only the relevant records, note that Access has no order unless assigned one, so 'last' is quite hazy without using Order By:

objRecordSet.Open "SELECT * FROM EventTable " & _ 
    "WHERE Type = 'Error' AND [TEXT] Like '%PMC%'" & _
    "ORDER BY [ASuitableDateOrIDField]", objConnection, adOpenStatic, _ 
         adLockOptimistic

'Last PMC Field
objRecordSet.MoveLast

It is possible to search backwards:

objRecordSet.MoveLast
objRecordSet.Find "[TEXT] Like '%PMC%'", , -1 'adSearchBackward'

 

[pellet]

Thank you all so much for your responses. I am trying to implement them in my script but I am getting an error that says Item cannot be found in the collection corresponding to the requested name or ordinal. This error happens right when it comes to the line:

Position = InStr(objRecordst("TEXT"),"PMC")

I did put in a variable: Dim Position

I don't know if there is anything I need to define after the variable. Looking at the code, it appears that position is defined as InStr(objRecordst("TEXT"),"PMC")

I am still working with it and the other code to locate the records using the backward search.

Thank you to everyone again!

 

[pellet]

Sorry, I had some code spelling errors... I am getting the correct info to pop up in a msgbox. Now I am working at getting it to dump it into the other program.

 

[Remou]

Did you change TEXT to the name of your field?

 

[pellet]

After a couple of hours of working, reading, and reworking, I finally got it working! Thank you all so much for helping me with it. I plan on continuing to study and get better at it.

Thanks again so much! I really appreciate it.