Copying Field Names one table to another

[Middlemarch]

I'm using DoCmd.RunSavedImportExport to import a text file to a table in my db
The table has fieldnames Field1, Field2 etc.


I think this command will change them


CurrentDb.TableDefs("TableName").Fields("FieldName ").Name = "NewName"
but it would be repeated many times.


I do have a empty "tblTemp" (structure only) with the fieldnames. Is there a way to copy them into the new table ?

[pbaldy]

This will get the names, shouldn't be too tough to add the bit to copy to the other table, using the x variable.

  Dim strSQL  As String
  Dim db      As DAO.Database
  Dim rs      As DAO.Recordset
  Dim x As Integer

  Set db = CurrentDb()
  
  strSQL = "SELECT TOP 1 * FROM tblTempRes"

  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)

  If Not rs.EOF Then
    For x = 0 To rs.Fields.Count - 1
      Debug.Print rs(x).Name
    Next x
  End If

  rs.Close
  Set rs = Nothing
  Set db = Nothing

[Middlemarch]

Thank you Paul, that worked great. Never seen "TOP" used before. Used "CurrentDb.TableDefs("tPending").Fields("Field " & x + 1).Name = rs(x).Name"
Is there something similar for DataType as they've all become Short Text which is trouble to come.

[pbaldy]

I thought they already existed. How are they being created? Or do you want to change the data type as part of this process?

I'm heading out to dinner soon with my wife so it will likely be tomorrow before I see any replies. Nobody wants my input after a bottle of wine.

[pbaldy]

By the way, I just used TOP to make sure I only pulled one record, didn't want to drag a whole table over the wire.

[Middlemarch]

I'll have a play, may fluke something. The table is (was) a text file exported by DoCmd.RunSavedImportExport and imported back the same way.
That's to share it with someone else.
There were formatting options including the field names, but Access complained about the names, including "-------------------------" which isn't one.

Enjoy the wine

[pbaldy]

Wine was good.

I found this if you need to change data types:

https://stackoverflow.com/questions/...ba-access-2007

[Middlemarch]

Thank you, nicely sorted.

[pbaldy]

Happy to help!