Parse data from 1 table into multiple txt files

[sherylnance]

I am trying to write a module that will basically loop through a list of values, execute a sql statement based on the value and write the query results to a file with the filename being XXvalue.txt

I have gotten to the point where the sql statement is built and I can print it out in the immediate window. But I can't for the life of me get the darn SQL statement to execute. Here is my code:

Sub loopSchool()

Dim myConn As ADODB.Connection
Set myConn = CurrentProject.Connection
Dim MyRecSet As New ADODB.Recordset
MyRecSet.ActiveConnection = myConn
MyRecSet.Open "Schools", , adOpenStatic, adLockOptimistic
Dim schNbr
Dim mySQL As String

MyRecSet.MoveFirst

While Not MyRecSet.EOF

schNbr = MyRecSet.Fields("No").Value
mySQL = "select * from schools where no = '" & schNbr & "'"

'Debug.Print mySQL 'this works
DoCmd.RunSQL mySQL, 0 'this doesn't

MyRecSet.MoveNext
Wend

MyRecSet.Close
Set MyRecSet = Nothing
Set myConn = Nothing

End Sub

[PEB]

Hi,

Yeah it shouldn't work! It's sure!

Try with

MyRecSet1.Open mySQL, , adOpenStatic, adLockOptimistic

And cycle this recordset to output the information in your txt file!

Best regards!

I am trying to write a module that will basically loop through a list of values, execute a sql statement based on the value and write the query results to a file with the filename being XXvalue.txt

I have gotten to the point where the sql statement is built and I can print it out in the immediate window. But I can't for the life of me get the darn SQL statement to execute. Here is my code:

Sub loopSchool()

Dim myConn As ADODB.Connection
Set myConn = CurrentProject.Connection
Dim MyRecSet As New ADODB.Recordset
MyRecSet.ActiveConnection = myConn
MyRecSet.Open "Schools", , adOpenStatic, adLockOptimistic
Dim schNbr
Dim mySQL As String

MyRecSet.MoveFirst

While Not MyRecSet.EOF

schNbr = MyRecSet.Fields("No").Value
mySQL = "select * from schools where no = '" & schNbr & "'"

'Debug.Print mySQL 'this works
DoCmd.RunSQL mySQL, 0 'this doesn't

MyRecSet.MoveNext
Wend

MyRecSet.Close
Set MyRecSet = Nothing
Set myConn = Nothing

End Sub

[dasanchez]

Hi,

Yeah it shouldn't work! It's sure!

Try with

MyRecSet1.Open mySQL, , adOpenStatic, adLockOptimistic

And cycle this recordset to output the information in your txt file!

Best regards!

How can I cycle the recorset to send to the TXT file? Can you please provide the code? Thanks much!

[PEB]

Ok your version can run coz this is Select Query and isn't an action query

Sub loopSchool()
 
Dim myConn As ADODB.Connection
Set myConn = CurrentProject.Connection
Dim MyRecSet As New ADODB.Recordset
Dim MyRecSet1 As New ADODB.Recordset
MyRecSet.ActiveConnection = myConn
MyRecSet.Open "Schools", , adOpenStatic, adLockOptimistic
Dim schNbr
Dim mySQL As String
 
MyRecSet.MoveFirst
 
While Not MyRecSet.EOF
 
schNbr = MyRecSet.Fields("No").Value
mySQL = "select * from schools where no = '" & schNbr & "';"
 
MyRecSet1.ActiveConnection = myConn
MyRecSet1.Open mySQL, , adOpenStatic, adLockOptimistic
    Open "myFile.txt" for output AS #1
    While Not MyRecSet.EOF
     Print #1, MyRecSet1.Fields("A").Value+", "+ MyRecSet1.Fields("B").Value ... and so on /your fields to be vizualized/
 
    MyRecSet1.MoveNext
    Wend
   Close #1
'Debug.Print mySQL 'this works
DoCmd.RunSQL mySQL, 0 'this doesn't
 
MyRecSet.MoveNext
Wend
 
MyRecSet.Close
Set MyRecSet = Nothing
Set myConn = Nothing
 
End Sub
 

Here i give you idea how you can export your recordsets in files.. but nothing about the names of your fields and the columns of your recordsets... Here you can a lot on this function

There is also other way... Save the SQL that you've generated in a query and then use docmd. to transfer the information to a text file...

As you want and as is more suitable for your case!

[MMcCarthy]

Ok, the first thing is you can't use DoCmd.RunSQL on a SELECT statement. It is for Action queries only, like INSERT, UPDATE and DELETE.

Secondly, what you are doing doesn't make any sense. You open a recordset of the table Schools. In your loop you get the value in the No field. The you try to run a select everything from schools where that value is in the no field.

However, because you are looping through every record in the Schools table then even if it was working it would create multiple repeats. For instance if the value in no was repeated 1000 times you would have a query of those 1000 records being repeated 1000 times for every time the loop reached the next record of that value in the query.

If you tell me the logic of what you are trying to do and why I will help you build the function.

Sub loopSchool()
 
Dim myConn As ADODB.Connection
Set myConn = CurrentProject.Connection
Dim MyRecSet As New ADODB.Recordset
MyRecSet.ActiveConnection = myConn
MyRecSet.Open "Schools", , adOpenStatic, adLockOptimistic
Dim schNbr
Dim mySQL As String
 
MyRecSet.MoveFirst
 
While Not MyRecSet.EOF
 
schNbr = MyRecSet.Fields("No").Value
mySQL = "select * from schools where no = '" & schNbr & "'"
 
'Debug.Print mySQL 'this works
DoCmd.RunSQL mySQL, 0 'this doesn't
 
MyRecSet.MoveNext
Wend
 
MyRecSet.Close
Set MyRecSet = Nothing
Set myConn = Nothing
 
End Sub
 
 
[quote=sherylnance]I am trying to write a module that will basically loop through a list of values, execute a sql statement based on the value and write the query results to a file with the filename being XXvalue.txt
 
I have gotten to the point where the sql statement is built and I can print it out in the immediate window. But I can't for the life of me get the darn SQL statement to execute. Here is my code:
 
Sub loopSchool()
 
Dim myConn As ADODB.Connection
Set myConn = CurrentProject.Connection
Dim MyRecSet As New ADODB.Recordset
MyRecSet.ActiveConnection = myConn
MyRecSet.Open "Schools", , adOpenStatic, adLockOptimistic
Dim schNbr
Dim mySQL As String
 
MyRecSet.MoveFirst
 
While Not MyRecSet.EOF
 
schNbr = MyRecSet.Fields("No").Value
mySQL = "select * from schools where no = '" & schNbr & "'"
 
'Debug.Print mySQL 'this works
DoCmd.RunSQL mySQL, 0 'this doesn't
 
MyRecSet.MoveNext
Wend
 
MyRecSet.Close
Set MyRecSet = Nothing
Set myConn = Nothing
 
End Sub

[NeoPa]

Also check type of [No].
If it's a string then fine - otherwise there should be no quotes (').