Change field names based on row data

[RD]

Hi all,

I did some searching but didn't find anything helpful.

I need to produce some reports from data extracts. The imported extracts do not
have field names so Access automatically names them "Field 1, "Field 2" ... etc.
I get the field names from a different source and import them into a separate
table. This does have field names: Field No, Cobol Field Name, Field Size, ...
etc. Data in the Field No field looks like this: F1, F2, ... etc.

Since I'm going to be doing a lot of these I was hoping to assign the field
names in vba instead of creating a different import spec for each extract (there
are a *LOT* of extracts).

Anyone know of some example code that will do this?

Thanks for any help,
RD

 

[Allen Browne]

I understand that you have:
- a table of values, in fields named Field1, Field2, etc.
- another table of values to use as the field names.

If you are comfortable with VBA and SQL, you will be able to generate a SQL
Statement as a VBA string, to alias the fields from the first table with the
field names from the second table.

This is really rough aircode, not debugged, no error handling, hard-coded
names, doesn't check for nulls or invalid names, etc, etc, but hopefully it
gives you the idea:

Function ExportMyData() As Boolean
Dim db As DAO.Database
Dim tdfData As DAO.TableDef 'Table of data.
Dim tdfFldName As DAO.TableDef 'Table of field names.
Dim i As Integer
Dim strSql As String
dim lngLen As long

'Initialize.
Set db = CurrentDb()
Set tdfData = db.TableDefs("tblData")
Set tdfFldName = db.TableDefs("tblFldName")

'Must have the same number of fields.
If tdfData.Fields.Count <> tdf.FldName.Fields.Count Then
MsgBox "Different number of fields."
Else
'Loop through the fields, aliasing the field names.
For i = 0 To tdfData.Fields.Count - 1
strSql = strSql & "[" & tdfData.Fields(i).Name & _
"] AS [" & tdfFldName.Fields(i).Name & "], "
Next
End If

'Chop off the trailing comma and space.
lngLen = Len(strSql) - 2
If lngLen > 0 Then
'Complete the SQL statement
strSql = "SELECT " & Left$(strSql, lngLen) & " FROM tblData;"

'Assign the SQL statement the the query you will export.
db.QueryDefs("Query1").SQL = strSql

'Export the query.
DoCmd.TransferText acExportDelim,,"Query1", "C:\MyData.csv", True
ExportMyData = True
End If

'Clean up.
set tdfFldName = Nothing
Set tdfData = Nothing
Set db = Nothing
End Function

 

[RD]

Thanks a bunch, Allen. It does indeed get me going in the right direction.

Regards,
RD