I am a beginner to Microsoft Access and have built a simple inventory
database for our upcoming online store. The database will be used to
enter and update our inventory, which will then be output into a flat
file and uploaded to the online store (for background, the online
store uses osCommerce with the EasyPopulate contribution to facilitate
this).

I've built the database, and generally have the forms working to my liking.

I now need someone to assist in creating a module (or perhaps it's a
macro - can't even tell which is the right way to begin) that would
create a flat text file, tab delimited, with specific information from
the "Inventory" table of my database.

I think this should be fairly simple & straight forward. There are 27
fields that need to be output per item, 7 of which are either always
blank or have a fixed text output (to maintain the file import
format), about 10 of which are straight output of what's in the
Inventory, and the remainder have very limited (in my estimation)
lookup/formatting/parsing required before output.

I have posted the database, plus an Instructions.rtf file (for the
specifics on the module required) in a zip here:
http://www.cottagepast.com/access/TextFileDatabaseProject.zip

Hopefully this is less than an hour's work for anyone with decent MS
Access skills...

Thanks,
Mike

For a start....Try this and see. 
You might have to test this well though. This will create a tab
delimited file from the records in your recordset. (Error handling
etc, validating etc is not done here, you have to do your own)

Private Sub TabDelimitedTable(TabFile As String)
    smemo = ""
    sLine = ""
 
   'open your recordset here with your specific sql
    rs.Open strsql, myConnection, adOpenKeyset, adLockOptimistic
    
     If Not (rs.EOF And rs.BOF) Then
       If rs.RecordCount > 0 Then
         rs.MoveFirst
         Set flds = rs.Fields
         For Each fld In flds
           ' Create column headers
           sLine = sLine & fld.Name & vbTab
         Next
         sLine = Left(sLine, (Len(sLine) - Len(vbTab))) & vbCrLf
         smemo = smemo & sLine
         With rs
           While Not .EOF
             'Set flds = rs.Fields
             sLine = ""
             For Each fld In flds
               ' Create column headers
               sLine = sLine & fld.Value & vbTab
             Next
             sLine = Left(sLine, (Len(sLine) - Len(vbTab))) & vbCrLf
             smemo = smemo & sLine
             .MoveNext
           Wend
         End With
       End If
     End If
     
     outfile = TabFile
     Set ts = fso.OpenTextFile(outfile, ForWriting, True)
     
     'Write to file.
     ts.Write smemo
     
     'Close all
     ts.Close
     rs.Close
End Sub

Here is your dim statements.... 

    Dim myConn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim smemo As String
    Dim fso As New FileSystemObject
    Dim ts As TextStream
    Dim sLine As String
    Dim flds As ADODB.Fields
    Dim fld As ADODB.Field
    Dim outfile As String

' end

1. go in to each of you tables and clear out the captions on each table.

2. Make a new query. Name it Inventory_Export.

3. In design view goto view and change it to sql view.

4. Paste this code.

SELECT IIf([DefaultModel]<>0,Right(Year([EntryDate]),2) & "-" &
Format([Inventory].[ID],"000000"),[AltModelNumber]) AS
v_products_model, "http://www.cottagepast.com/images/" &
IIf([DefaultModel]<>0,Right(Year([EntryDate]),2) & "-" &
Format(Inventory.[ID],"000000"),[AltModelNumber]) & ".jpg" AS
v_products_image, Inventory.ItemName AS v_products_name_1,
CleanString([Description]) AS v_products_description_1,
Inventory.URLofProduct AS v_products_url_1, Inventory.SellingPrice AS
v_products_price, Inventory.Weight AS v_products_weight,
Format([PurchaseDate],"yyyy-mm-dd hh:nn:ss") AS v_date_avail,
Format([PurchaseDate],"yyyy-mm-dd hh:nn:ss") AS v_date_added,
Inventory.Quantity AS v_products_quantity, Null AS
v_attribute_options_id_1, Null AS v_attribute_options_name_1_1, Null
AS v_attribute_options_id_2, Null AS v_attribute_options_name_2_1,
Regions.Region AS v_manufacturers_name, Categories.Category AS
v_categories_name_1, Inventory.Subcategory AS v_categories_name_2,
Null AS v_categories_name_3, "Taxable Goods" AS v_tax_class_title,
IIf([Sold]=0,"Active",Null) AS v_status, DimensionTypes.DimensionTypes
AS v_products_dim_type, Inventory.Depth AS v_products_length,
Inventory.Width AS v_products_width, Inventory.Height AS
v_products_height, WeightTypes.Weight AS v_products_weight_type, Null
AS v_products_ready_to_ship, "EOREOR" AS EOREOR
FROM WeightTypes RIGHT JOIN (DimensionTypes RIGHT JOIN ((Categories
RIGHT JOIN SubCategories ON Categories.ID = SubCategories.CategoryID)
RIGHT JOIN (Regions RIGHT JOIN Inventory ON Regions.RegionID =
Inventory.RelatedRegion) ON SubCategories.SubCategoryID =
Inventory.Subcategory) ON DimensionTypes.DimensionID =
Inventory.DimensionFormat) ON WeightTypes.WeightID =
Inventory.WeightFormat;

5. Goto File Export select text files.

6. Click Advanced. Set field Delimeter to tab Text Quail to none. Then
save the spec as Inventory_Export.
7. Cancel out of the export to main screen.

8. New macro --> Select TransferText.

9. Set the options as follows.

Transfer Type: Export Delimeted
Specification Name: Inventory_Export
Table Name: Inventory_Export
FileName: c:\Inventory_Export.txt
Has Field Names : Yes

Huge thanks, dsmiller... however, I've hit a few major snags...

a) If I try to run the query that you've built, I get an error
"Undefined function 'Cleanstring' in expression."

b) When I go to file export and select Text Files, I do not see an
advanced tab - the only options I have are "Save formatted" and if I
select that then I can choose "Autostart" - when I select "Save
formatted" I get a menu option to choose what type of encoding I want
but nothing allowing me to set the field delimiters or to save the
spec - of course, as a result I can't create the macro as it doesn't
recognize that spec.

Am I doing something blatantly or stupifyingly wrong?

here is the clean string. function you will need to create a module
and paste this in there. That will noop out the tabs in the desc.
Anyway you need to highlight the query Then advanced button is on the
next screen. After you set save as type to this "Text Files
(*.txt;*.csv;*.tab;*.asc)" Than all that other stuff should work.

Function CleanString(DirtyString)
If Nz(DirtyString) <> 0 Then

For i = 0 To Len(DirtyString)
    CurrentChar = Right(Left(DirtyString, i), 1)
    
    If CurrentChar = Chr(11) Then CurrentChar = "-"
    
    CleanString = CleanString & CurrentChar
Next i

End If
End Function

Or just download it. This is in Access 2000. Just run the macro and it
should work. It will dump the file to your c: drive as
Inventory_Export.

http://www.dsmiller.com/CottagePast.zip

Thanks dsmiller - how do I repay you - clearly you've done exactly
what I was looking for...

buy something from my site ha. It's on the house. Only took 5 min.