Parsing Data in one table (multiple conditions) to several .txt files

[dasanchez]

Hello, everyone. Please bear in mind that I have a very basic knowledge of VBA, but I think I can understand logic.

I'm looking for a way to build a module that can help me parse the data in one massive table, using "control tables" to determine the valid value set for the fields I need to consider in the selection. I cannibalised a piece of code from a similar post that I think will help me, but need your guidance, by explaining what each of the commands actually do, so I can try it out in my database. I think that I can figure out the rest.

' START CODE <---------------------

Sub loopFiles()

Dim myConn As ADODB.Connection
Set myConn = CurrentProject.Connection
Dim MyRecSet As New ADODB.Recordset
MyRecSet.ActiveConnection = myConn
MyRecSet.Open "Schools", , adOpenStatic, adLockOptimistic ' Comment - suposse I can replace "Schools" with any table in my current DB....
MyRecSet1.Open mySQL, , adOpenStatic, adLockOptimistic ' Comment - What is this?!?

Dim schNbr
Dim mySQL As String


MyRecSet.MoveFirst

While Not MyRecSet.EOF ' While loop that goes through RecordSet for execution - I get this

schNbr = MyRecSet.Fields("No").Value
mySQL = "select * from schools where no = '" & schNbr & "'" ' This I can figure out

Debug.Print mySQL ' debug statement
'DoCmd.RunSQL mySQL, 0 ' for actual execution

MyRecSet.MoveNext
Wend

MyRecSet.Close
Set MyRecSet = Nothing
Set myConn = Nothing

End Sub

' END CODE <---------------------


I wish I could import the data into an Oracle DB and write an SQR for this, but can't, since the team has been using this DB (and plan to use it for years to come).

Thank you very much in advance!

D

[NeoPa]

MyRecSet1.Open mySQL, , adOpenStatic, adLockOptimistic ' Comment - What is this?!?

Open Method (ADO Recordset)
Opens a cursor.
Syntax
recordset.Open Source, ActiveConnection, CursorType, LockType, Options

I 'm not sure exactly what you're after here but if you look in the help system for things you should get fuller info.
I can't see how this line can come before any code that sets up mySQL - a string containing SQL code.
Looks like an 'alternative' bit of code - doesn't go here nor can be run exactly as is, but an example of another approach.
If used, should have extra code in body processing through this recordset.

3 lines before

MyRecSet.ActiveConnection = myConn

Should probably have a 'Set' before it as it's an object assignment.

[MMcCarthy]

Any questions let me know ...

 
  ' as you are putting this in a module I would recommend using Function rather than sub as it is easier to call 
Function loopFiles() 
 
 ' create a variable to hold connection to database 
Dim myConn As ADODB.Connection
 ' set that connection to current database
Set myConn = CurrentProject.Connection
 
 'create a variable to hold recordset in database (table or query)
Dim MyRecSet As New ADODB.Recordset 
 
 'reference the current database as location of recordset
MyRecSet.ActiveConnection = myConn
 'open the recordset, in this case presumably the Schools table
MyRecSet.Open "Schools", , adOpenStatic, adLockOptimistic 
 
 ' Comment - suposse I can replace "Schools" with any table in my current DB....Answer yes, any table or saved query or sql statement
 
 'mySQL refers to a previously defined sql statement
 'Look for 'Dim mySQL As String' - defines string variable to hold sql statement
 'And mySQL="SELECT <Fields> FROM <TableName> WHERE <Criteria>" - Query definition
 
MyRecSet1.Open mySQL, , adOpenStatic, adLockOptimistic ' Comment - What is this?!? - This then opens the query as a recordset
 
Dim schNbr ' defaults to a variant which essentially can hold any type of value
Dim mySQL As String ' see above
 
 ' this moves to first record in the recordset
MyRecSet.MoveFirst
 
While Not MyRecSet.EOF ' While loop that goes through RecordSet for execution - I get this (until it reaches end of file)
 
 'Looks for a field in the recordset called "No" and finds its value in the current record then passes it to schNbr variable 
schNbr = MyRecSet.Fields("No").Value 
mySQL = "select * from schools where no = '" & schNbr & "'" ' This I can figure out (See above for opening it as a recordset)
 
Debug.Print mySQL ' debug statement 
'DoCmd.RunSQL mySQL, 0 ' for actual execution (won't work for a SELECT statement used for UPDATE, DELETE and INSERT statements mostly - i.e. Action queries)
 
MyRecSet.MoveNext ' move to next record
Wend ' loop unless end of file
 
MyRecSet.Close ' all recordsets must be closed
Set MyRecSet = Nothing ' and then variable reset to nothing
Set myConn = Nothing ' don't close database but must reset variable to nothing
 
End Function