help with module to export fixed-width text file 2

[jsanderson]

I am writing a module to export the results of a query to a fixed width text file. I am using the MSKB article:

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q210052

as an example. The only difference that I can see is that the example is based on a table with a primary key and mine is based on a query with no primary key. I tried adding the main table's primary key as a column in the query (PaymentID) but that didn't help. Can this example be modified to work with a query or am I barking up the wrong tree? I am using this method because I have found no other way to justify the columns in the exported text file.

<code>
Option Compare Database
Public Function CreateTextFile()

Dim strPropertyNumber As String * 13
Dim strUnitNumber As String * 14
Dim strPaymentDate As String * 6
Dim strCheckNumber As String * 10
Dim strPaymentAmount As String * 8
Dim mydb As DAO.Database, myset As DAO.Recordset
Dim intFile As Integer

Set mydb = CurrentDb()
Set myset = mydb.OpenRecordset(&quot;qryTodayReport&quot
'should I add (, dbOpenDynaset) to the query parameters?

myset.Index = &quot;PaymentID&quot;
'(compile error - operation is not supported for this type of object)
intFile = FreeFile

Open &quot;C:\windows\desktop\Today.txt&quot; For Output As intFile

'This section puts the records from the Orders table in the text
'file.
myset.MoveFirst
Do Until myset.EOF
LSet strPropertyNumber = myset![PropertyNumber] 'Field name in brackets
LSet strUnitNumber = myset![UnitNumber] 'Field name in brackets
RSet strPaymentDate = Format(YYMMDD, myset![PaymentDate], &quot;Short Date&quot
LSet strCheckNumber = myset![CheckNumber]
RSet strPaymentAmount = myset![PaymentAmount]
'Concatenate all of the variables together as in the following:
Print #intFile, strPropertyNumber & strUnitNumber & strPaymentDate & strCheckNumber & strPaymentAmount
myset.MoveNext
Loop
Close intFile
myset.Close
mydb.Close
MsgBox &quot;Today.txt is on the desktop!&quot;
End Function
</code>

 

[vb6novice]

jsanderson,

Yes, the example will work with a query. No need to add dbOpenDynaset to the OpenRecordset statement. Trying to index the query serves no purpose if all you are doing is concatenating several fixed length strings. If the query is designed properly, the records can be in any order you want and they will be read in and written out in that order.

 

[jsanderson]

ok - so my next break in the compiler was at this line:

Open &quot;C:\windows\desktop\Today.txt&quot; For Output As intFile

I have commented out the Dim intFile As Integer and the intFile = FreeFile lines. Now I need to know how to output the file to the appropriate file location/name. I'm not sure what to do with &quot;As intFile&quot; in the Open &quot;&quot; For Output statement.

 

[vb6novice]

I don't think there is anything wrong about using the intFile as an integer for the file number but I think it needs to have the '#' symbol before the intFile variable, and don't know how the syntax for that should go.

If you are only going to open one file at a time, replace the intFile with the '#' and a number. For example

Open &quot;C:\windows\desktop\Today.txt&quot; For Output As #200

Just be sure and close #200 before you try to open it again, otherwise you will get error 55 File Already Open.

 

[MichaelRed]

While the 'sample' code can be made to work, it looks like it come from circa ver 2.0. An easier approach might be available using ADO textdriver.

More information on this is available in Help, and your documentation.


MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over