Tab Delimited Text File from Code

[Basic]

Hi everyone,

Ok, here's what I'm looking to do, and am having a bunch of trouble
figuring out how to accomplish this.

I am linking to several Oracle tables. Currently, I am using a
make-table query to import the data from those tables that I need.
This works fine.

Now, I want to export all of the data in those tables to a tab
delimited file, however...

1. I want to use the field names as headers
2. I want to insert extra columns in the middle of all the data.

The goal here is this:

I have some machinery that requires a tab delimited txt file to
operate. This file has a set number of fields, and must be in a
specific order. The database only records SOME of this information.
The rest of the information is static or calculated. For example:

date item weight location Vendor

db db db calculated db

So.. how can I create a tab delimited (MUST be tab delimited) txt file
with Access, and, at the same time, insert extra columns in between all
of the data output? I have not yet figured this out and it's driving
me nuts.

I have considered various options, but want to make this as easy as
possible for the end user. For example , I considered an embedded
worksheet, loading the data there with the columns, and providing an
export button. However, I have had a ton of trouble actually
populating the OWC Spreadsheet control properly.. I have messed with
an Unbound frame with Excel Spreadsheet in it, but ran into the same
issues.. not understanding how to populate it and address it...

I have a fairly good understanding of Access, but not nearly as good as
it could be... so any help would be appreciated.

 

[Todd Shillam]

Hi everyone,

Ok, here's what I'm looking to do, and am having a bunch of trouble
figuring out how to accomplish this.

I am linking to several Oracle tables. Currently, I am using a
make-table query to import the data from those tables that I need.
This works fine.

Now, I want to export all of the data in those tables to a tab
delimited file, however...

1. I want to use the field names as headers
2. I want to insert extra columns in the middle of all the data.

The goal here is this:

I have some machinery that requires a tab delimited txt file to
operate. This file has a set number of fields, and must be in a
specific order. The database only records SOME of this information.
The rest of the information is static or calculated. For example:

date item weight location Vendor

db db db calculated db

So.. how can I create a tab delimited (MUST be tab delimited) txt file
with Access, and, at the same time, insert extra columns in between all
of the data output? I have not yet figured this out and it's driving
me nuts.

I have considered various options, but want to make this as easy as
possible for the end user. For example , I considered an embedded
worksheet, loading the data there with the columns, and providing an
export button. However, I have had a ton of trouble actually
populating the OWC Spreadsheet control properly.. I have messed with
an Unbound frame with Excel Spreadsheet in it, but ran into the same
issues.. not understanding how to populate it and address it...

I have a fairly good understanding of Access, but not nearly as good as
it could be... so any help would be appreciated.

Here's something you can do:

1. Created a new Module; copy and past code:

==========COPY BELOW==========

Option Compare Database

Public Function ExportToTAB()

'SYNTAX TO EXPORT QUERY TO TAB DELIMITED FORMAT
DoCmd.OutputTo acOutputQuery, "qryNameHere", acFormatTXT, "C:\Test.tab"

End Function

==========END COPY==========

2. Change 'qryNameHere' to the name of your query (more on this below).
3. Change the file path to your TAB delimited file.
4. Save and close Module--use 'modExportToTAB' as the name.
5. Create a query in 'Design View' mode:
a. Add your table that holds the data you need.
b. Now add expressions to hold your extra fields, like this:

FieldName1: Null FieldName2: Null

6. Create a macro to call your function in the Module--this will output your
query with the extra "null" fields into a TAB delimited file.

Best regards,

Todd Shillam

 

[Basic]

Todd,

Thanks for your prompt reply. This works beautifully, except for one
thing:

The output is 'boxed', meaning that it is outlined by dashes and hashes
that make it appear to be 'spreadsheet like'. Unfortunately, the 'tab'
isn't recognized, and when opened into an Excel spreadsheet, for
instance, all of the information on each row appears in the first cell,
instead of a series of cells...even when I tell Excel that it is tab
delimited?