Naming Fields in VBA after Import 1

[PSUIVERSON]

I have setup a Macro that imports a text file into Access. Because the field names are not in the text file I cannot just name the fields based off the first row of the text file. I am relatively new to VBA programming in ACCESS and wanted to try and shoot the macro to code and call a function out of a module. I am having a small problem with figuring out how to grab recordsets from this new table and then figuring out how to name the fields?

Example:

Sub nameField()
Dim db As Database
Dim rst As Recordset

'How to open table and how to write to field names how do I do this?

'Rough Guess:

Set db = CurrentDb
Set rst = db.OpenRecordset("TABLE_NAME_FROM_IMPORT&quot

'With the database open I now have no idea how to name the FIELD NAMES?!

'Rough Guess with 3 fields!?

rst.Fields.Item.Name = "FIELD1"
rst.MoveNext
rst.Fields.Item.Name = "FIELD2"
rst.MoveNext
rst.Fields.Item.Name = "FIELD3"
rst.MoveFirst

Set rst = db.Close

End Sub

 

[WayneRyan]

psuiverson,

I don't think that you really want to programmatically
rename the fields, but this will add an "x" to each of
their names.

' ***********************************************
Dim dbs As Database
Dim tdf As TableDef
Dim intLoop As Integer

Set dbs = CurrentDb
Set tdf = dbs.TableDefs("Projects&quot
For intLoop = 0 To tdf.Fields.Count - 1
tdf.Fields(intLoop).Name = tdf.Fields(intLoop).Name & "x"
Next Temp
' ***********************************************

Or

' ***********************************************
Dim dbs As Database
Dim rst As RecordSet
Dim strSQL As String

Set dbs = CurrentDb
strSQL = "Select * from Projects"
Set rst = dbs.OpenRecordSet(strSQL)
While Not rst.EOF and Not rst.BOF
rst.Edit
rst!Field1 = "abc"
rst!Field2 = "def"
If rst!Field3 = "ghi" Then
rst!Field3 = "jkl"
End If
rst.Update
rst.MoveNext
Wend
Set rst = Nothing
Set dbs = Nothing
' ***********************************************

The first changes the NAMES of the fields, the second
changes their contents.

hth,
Wayne

 

[PSUIVERSON]

Thanks man! I realize this looks overcomplicated but I'm just trying to figure things out and for the one specific .txt file I ran this off a getcode macro after doing the import.

Sub nameField()

Dim dbs As Database
Dim tdf As TableDef

Set dbs = CurrentDb
Set tdf = dbs.TableDefs("TABLENAME&quot

tdf.Fields(0).Name = "FIELD1"
tdf.Fields(1).Name = "FIELD2"
tdf.Fields(2).Name = "FIELD3"
tdf.Fields(3).Name = "FIELD4"
tdf.Fields(4).Name = "FIELD5"
tdf.Fields(5).Name = "FIELD6"

End Sub

* I realize this is inflexible for different files but I'll try to figure that out based off the ideas you gave me. WHAT I REALLY NEEDED WAS THE TABLEDEF answer. Now I can figure out all sorts of things once you demonstrated how to bring up the current dbase.

Also the 2nd code is great as well as I am new to this but the SQL stmt for selecting the dbase is very useful moving forward and going through recordsets. Thanks man!

BTW - What exactly does the If then statement do for the final field you put in there? Check to see what?

PSUIVERSON

 

[WayneRyan]

PSUIVERSON,

The If-Then was just an example of how to conditionally
change the value of a field.

Good luck,
Wayne

 

[PSUIVERSON]

Gotcha - I thought maybe it was something to avoid going to the last field and not renaming the first somehow?

Thanks

PSUIVERSON