create custom text file from Microsoft Access

Question

I'm trying to create a text file from an Access Database that looks exactly like this:

CADWorx P&ID Drop Down List Configuration File.

Notes:

-This file contains information on what
 appears in the drop down list in the
 CEDIT Additional Data Dialog

-Entries should be separated by a semi-colon (;)

-If a value is not set, an edit box will appear
 in the CEDIT Additional Data Dialog instead
 of a drop down list.

-Example: AREA_=031;032;033;034A;034B;
 Example: SERVICE_=AEC;HW;LH;CCH;



[DOCUMENTATION]
TYPE_=
DATESUB_=
DATEAPR_=
CREATEBY_=
APRBY_=


[LINE]
SERVICE_=OIL;FUEL GAS; 
AREA_=
UNIT_=
COUNT_=
TYPE_=
RATING_=
FLGFACE_=
DESIGNPSI_=
DESIGNDEG_=
LINE_NUM_=
OPERPSI_=
OPERDEG_=
SPECPRESS_=
SPECTEMP_=
MINDEG_=
TESTPSI_=
INSULATE_=
HEATTRACE_=
XRAY_=
CODE_=
JOINTEFF_=
WELDPROC_=
INSPECT_=
MATPIPE_=
COMPNOTE_=
NOTE_=
USER1_=

All the fields on the left (that end with '_=') are field titles in my database.Then as explained above, values for those fields must be added and separated by a semicolon. I've been researching for over a week and pretty much just hitting dead ends with text file customization in Access. Can someone tell me if this is the way to go? Or should I export the data to Excel and create the text file from there?

Your help is very much appreciated. Thanks in advance.

Please edit your question, remove the screenshot, instead copy&paste the text and format it as code. Actually, only keep the relevant parts, see minimal reproducible example — Andre
– Andre, Commented Jan 18, 2016 at 16:51
And important: Which parts of the file are constant, and which parts are filled from the database? — Andre
– Andre, Commented Jan 18, 2016 at 16:51

Gustav · Accepted Answer · 2016-01-18 18:30:23Z

Here's the basics to write records to a file:

Dim dbs As Database
Dim rst As Recordset
Dim intFileDesc As Integer      'File descriptor for output file (number used by OS)
Dim strOutput As String         'Output string for entry
Dim strRecordSource As String   'Source for recordset, can be SQL, table, or saved query
Dim strOutfile As String        'Full path to output file

Kill strOutfile                 'Delete the output file before using it.
                                'Not necessary, but ensures you have a clean copy every time
intFileDesc = FreeFile          'Get a free file descriptor
Open strOutfile For Binary As #intFileDesc 'Open the output file for writing
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strRecordSource ) 'open the recordset based on our source string
With rst    'make things easier for ourselves
While Not .EOF
        strOutput = !Field1 & ";" & !Field2 & ";" & !Field3
        Print #intFileDesc, strOutput   'Print output string to file
        .MoveNext       'Advance to next record in recordset
    Wend
    .Close                  'Close this recordset
End With
Close #intFileDesc          'Close output file
Set rst = Nothing
Set dbs = Nothing       'Garbage handling before we exit the function

The essential line is this:

Print #intFileDesc, strOutput   'Print output string to file

by which you can write one line at a time.

Thus, build a function that expands on this, creating your output line by line (including empty lines for spacing) until done. That's it.

It takes a lot of code but really is quite trivial. And there is no other way for outputs like these.

Collectives™ on Stack Overflow

create custom text file from Microsoft Access

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related