Get table's field list in a new table

[phinix]

I'm trying to get a list of fields in one table and create a new table with those fields.

In a big picture - I'm working on a macro that will process data imported to the access db.
I want to have a form that user can manually map the imported table fields to a table template and run macro that will process all data.
So I thought I will create a form where user could pick specific imported table fields and map it to template which will be processed later on.

This is why I need to get all fields from imported table in a new table, which I will display in drop-down list on the form for user to map.

I hope that makes sense.

So far, finding different vba codes for similar jobs, I got this.
It gives me a list of fields, but I don't know how to create a table from those...

Please help me to get them in a table

Function listTblFlds() As String
    On Error GoTo Error_Handler
    Dim db                    As DAO.Database
    Dim rs                    As DAO.Recordset
    Dim sSQL                  As String
    Dim fld                   As Field
    Dim tTable                  As String
    
    Set db = CurrentDb()
    
    tTable = "Table1"
    
    sSQL = "SELECT *" & _
           " FROM [" & tTable & "]" & _
           " WHERE (False);"
    Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)
    For Each fld In rs.Fields    'loop through all the fields of the tables
        Debug.Print fld.Name
    Next
 
Error_Handler_Exit:
    On Error Resume Next
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: listTblFlds" & vbCrLf & _
           "Error Description: " & Err.Description _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

 

[theDBguy]

Hi. If you're planning to use a form anyway, why go through all that exercise? If you simply want to have a list of fields for the user to select and do something with like map it, then just use a combobox or a listbox with the Row Source Type set as Field List.

 

[phinix]

Hi. If you're planning to use a form anyway, why go through all that exercise? If you simply want to have a list of fields for the user to select and do something with like map it, then just use a combobox or a listbox with the Row Source Type set as Field List.

I thought it would be best as imported table will be always different, will have diff number of fields. But, yes, you are right, this is really a lot easier, thanks

How can I create a form with same number of combo boxes as fields in the imported table?

 

[plog]

Why not teach users how to import data via the existing Access import tools?

 

[theDBguy]

I thought it would be best as imported table will be always different, will have diff number of fields. But, yes, you are right, this is really a lot easier, thanks

How can I create a form with same number of combo boxes as fields in the imported table?

Rather than do it that way, maybe just use a continuous form instead. It sounds like you are planning/thinking of using fields in your mapping table for each field to map. If so, don't. Instead, create a table that can represent each mapped field as a record, rather than a column.

 

[phinix]

Why not teach users how to import data via the existing Access import tools?

If this was so easy, I would do that

 

[phinix]

Rather than do it that way, maybe just use a continuous form instead. It sounds like you are planning/thinking of using fields in your mapping table for each field to map. If so, don't. Instead, create a table that can represent each mapped field as a record, rather than a column.

Yep, I think this is what I wanted to achieve.
A table with records where values will be field names from imported table.
Sorry if I'm not explaining it correctly.
If I had this, I would then create continuous form with field names and use would pick a fields from combo box of template table to each record.

Plan would be:
1. Data table is imported
2. User opens a form where on the left is a list of fields from that imported table. On the right a combo box next to each field - user picks which imported table field matches template field (like imported add1 will be Address1 in template).
3. After mapping, user presses a button that creates a mapping table with two columns - one with imported table fields, second with mapped template fields.
4. Then user clicks on macro which processes the data in template table, then export processes table using mapping table initial/original field names.

 

[theDBguy]

Yep, I think this is what I wanted to achieve.
A table with records where values will be field names from imported table.
Sorry if I'm not explaining it correctly.
If I had this, I would then create continuous form with field names and use would pick a fields from combo box of template table to each record.

Hi. Would you need to know the table name too? If so, what about the file's name or maybe a "spec" name? If so, maybe something like this.


tblSpecs
SpecID, pk
SpecName


tblSpecMaps
MapID, pk
SpecID, fk
SpecField
TableName
TableField


Just a thought...

 

[phinix]

Hi. Would you need to know the table name too? If so, what about the file's name or maybe a "spec" name? If so, maybe something like this.


tblSpecs
SpecID, pk
SpecName


tblSpecMaps
MapID, pk
SpecID, fk
SpecField
TableName
TableField


Just a thought...

I've added a plan in previous post.

Well, mapping table would have these:

Col1 Col2
Add1 Address1
Add2 Address2
...

Tables names would be always the same - imported table would be something like InitialTable, mapping one - MappingTable, then results one would be ResultTable.

All this could be easily done by importing a table, then running append table query by manually fixing all fields, but thing is I need to do it automatically, taking into account different field names and amount.

 

[phinix]

Hmm... how can I pass over a field name being a field value from mapping table?

I think I know how I'm gonna get this mapping table.
But how am I going to use it later?
I mean how can I then run append query that will append data from ImportTable IT to ProcessTable PT by appending MT.field1 to MT.field2?

I only hope you guys will understand what I'm talking about here

 

[theDBguy]

Hmm... how can I pass over a field name being a field value from mapping table?

I think I know how I'm gonna get this mapping table.
But how am I going to use it later?
I mean how can I then run append query that will append data from ImportTable IT to ProcessTable PT by appending MT.field1 to MT.field2?

I only hope you guys will understand what I'm talking about here

Hi. To me, this is nothing more like mapping fields to a mail merge bookmark. I think what I'll do is look for some existing demos doing the same thing. Maybe you won't have to reinvent the wheel on this.