Tabledef.field.description / Transfertext specs

  • Thread starter Thread starter Andy
  • Start date Start date
A

Andy

Access 97
I have inherited a database with a huge number of tables. Each tables has
about 30-40 fields but only some fields are actually required. The end
purpose of the database is to accept data from one database and convert it
to a fixed length text file for importing to another database.
The problem I have is that I want to use the Transfertext function to create
the text file but I cannot find a way for the Specification file to select
specific fields and ignore others.

My alternative idea was to create temporary output tables for this purpose
since and export from there. My plan was to step through each tabledef in
the database and copy on the required fields to the temporary database. My
idea was to filter out the unwanted fields using a comment in the
description as a filter key.

I cannot find a way to access the descriptions in the field. Can the
description be accessed ?
Failing that can the transfertext specification ignore certain fields?

Many thanks

Andy
 
Could you create a query that selects only the fields you want?
You could then use TransferText on the query to export the text file.

If that's not adquate, you can use Properties("Description") on the Field of
the TableDef. That will error if the field has no Description. Example of
reading the fields' Description:
http://allenbrowne.com/func-06.html
 
Allen Browne said:
Could you create a query that selects only the fields you want?
You could then use TransferText on the query to export the text file.
Click to expand...

I am trying to keep the number of queries down. In my experience the fewer
queries I have, the less chance that some self-appointed 'expert' will have
to mess with the database.
If that's not adquate, you can use Properties("Description") on the Field of
the TableDef. That will error if the field has no Description. Example of
reading the fields' Description:
http://allenbrowne.com/func-06.html
Click to expand...

This is exactly what I wanted. I cannot thank you enough.

I now have a frighteningly small piece of code that steps through each of
the tables, checks for valid tables , checks for valid fields and builds a
string that is run as a make-table query.
Thanks to the code in your link, I have managed to effectively put filtering
and make-table queries for 37 tables in less than 30 lines of code. I'm
quite pleased with the results.

Many thanks

Andy

In case anyone is interested it is listed below, bear in mind that I am a
hardware engineer and am self-taught with access (thanks to help files and
NGs) so I will have made a some mistakes in programming technique and
protocol:-

Sub ExportDS0060Tables()

Dim strQuery As String
Dim tbl As TableDef
Dim fldTest As Field
Dim strTestString As String
Dim obj As Object

DoCmd.SetWarnings False
RemoveTempTables ' Delete any temp tables

For Each tbl In CurrentDb.TableDefs 'step through all the tables
strTestString = "Not Issue 3"
strQuery = "SELECT " ' start building the
query
If Left(tbl.Name, 6) = "DS0060" Then 'is it a DS0060 table?
For Each fldTest In tbl.Fields ' step through every
field
Set obj = fldTest
If Not GetDescrip(obj) = strTestString Then
'if it is a field valid for this issue
strQuery = strQuery & tbl.Name & "." & fldTest.Name & ", "
'add field details to the query string
End If
Next
' delete extra comma & space and add end details to query (source &
target table name)
strQuery = Left(strQuery, Len(strQuery) - 2) & " INTO " & tbl.Name &
"_Temp FROM " & tbl.Name
SysCmd acSysCmdSetStatus, "Exporting DS00-60 Table : " & tbl.Name
'keep user informed
DoCmd.RunSQL strQuery 'run
query
End If
strQuery = ""
Next
'tidy up
DoCmd.SetWarnings True
SysCmd acSysCmdClearStatus

End Sub
 
Back
Top