How to rename a field name if I do not know the original field name

[SachAccess]

Hi,

I am trying to rename a field from a table. I have copied below code to perform the task.
The problem here is, in real scenario, I will not be aware about oldName = "Utilitatis|causa|amicitia|est|quaesita|Utilitatis|causa|amicitia"
How do I rename a field without knowing original name of the field.
Can anyone please help me in this.

'https://www.access-programmers.co.uk/forums/threads/how-to-automate-renaming-field-names.173179/
Option Compare Database
Sub TableColumnAlter()
    Dim counter1 As Long
    Dim counter2 As Long
    Dim tbl As TableDef
    Dim fld As Field
    Debug.Print CurrentDb.TableDefs.Count
    
    Table = "NewTable"
    oldName = "Utilitatis|causa|amicitia|est|quaesita|Utilitatis|causa|amicitia"
    newName = "txtString"
    
    For Each tbl In CurrentDb.TableDefs
        If tbl.Name = Table Then
            For Each fld In tbl.Fields
                If fld.Name = oldName Then
                    fld.Name = newName
                    Exit For
                End If
            Next
            Exit For
        End If
    Next
End Sub

 

[arnelgp]

just a word of caution, you only alter fieldname when it is not part of Any Relationship.

Sub TableColumnAlter()
    Dim counter2 As Long
    Dim tbl As DAO.TableDef
    Dim fld As DAO.Field
    Dim db As DAO.Database
    
    
    Table = "NewTable"
    oldName = "|Utilitatis|causa|amicitia|est|quaesita|Utilitatis|causa|amicitia|"
    newName = "txtString"
    
    Set db = Currentdb
    On Error Goto Err_Handler
    Set tbl=db.TableDefs(Table)
    For Each fld In tbl.Fields
        If Instr(1, OldName, "|" & fld.Name & "|")<> 0 Then
            fld.Name = newName
            Exit For
        End If
    Next
Exit_Sub:
    set tbl=nothing
    set db=nothing
    Exit Sub
Err_Handler:
    Msgbox Err.Number & ": " & Err.Description
    Resume Exit_Sub
End Sub

 

[Gasman]

I saw that in your other post in the DB
You will no know what oldname is from that code.

You really are trying too much too soon in my opinion, and is only going to make it harder for you.

Sit back, take a breath, and work out the steps you need on paper.
Then code for each step. Test each step works before moving on to the next step.

 

[SachAccess]

Hi @Gasman , really thanks for your advice. I totally agree with you.
I completely agree that I am trying too many things without knowing anything.

Please do not think that am ignoring what you are saying or being arrogant.
But at the moment, only option I have is, try and build this thing.

 

[Gasman]

Hi @Gasman , really thanks for your advice. I totally agree with you.
I completely agree that I am trying too many things without knowing anything.

Please do not think that am ignoring what you are saying or being arrogant.
But at the moment, only option I have is, try and build this thing.

I do not think that, but think you are overwhelmed and just trying snippets of code without understanding it, or at least having a basic idea as to how it works, is just going to slow you down.
Imagine having to build a car or an engine, where do you start. At the beginning. the same with code.
You cannot put the roof on when the car body has yet to be made.?
Think of the old fable with the tortoise and the hare. https://en.wikipedia.org/wiki/The_Tortoise_and_the_Hare

 

[SachAccess]

Hi @Gasman , yes, I 100% agree with you. I am trying to slow down but as you said overwhelmed at the moment...

 

[moke123]

Hi @Gasman , yes, I 100% agree with you. I am trying to slow down but as you said overwhelmed at the moment...

All the more reason to slow down.

For other readers there is a companion thread here . . .https://www.access-programmers.co.uk/forums/threads/how-to-split-text-data-from-table.321687/

In your other thread you have your data as "|Utilitatis|causa|amicitia|est|quaesita|Utilitatis|causa|amicitia|"
Now in this thread you have ... oldName = "|Utilitatis|causa|amicitia|est|quaesita|Utilitatis|causa|amicitia|" as field name?

 

[jdraw]

SachAccess,

You have several posts and responses covering a variety of topics. I recall this one.
I wonder if you could step back and tell us in simple English what exactly you are doing/trying to accomplish.
Not complaining, just curious - your sample data hasn't changed.

 

[SHANEMAC51]

oldName = "Utilitatis|causa|amicitia|est|quaesita| Utilitatis|causa|amicitia"

select Utilitatis as client, causa as lastname,amicitia as firstname .... from tab1

 

[SachAccess]

Hi @jdraw
I will try to explain it in a simpler way, may be that will help me only to understand.

1) I have a text file
2) Size of this text file will very
3) Data is separated with pipe character in the text file
4) once we import and delimit the data, number of fields will be the same
5) I have created a dummy data for uploading purpose
6) I can not access my real data from here, at a different system
7) Once I import the data I need to separate it
8) example of a string would be I am | too much | overwhelmed at | the moment |
9) When I import data in DB from text in a table, I can see it as ' I am | too much | overwhelmed at | the moment |' entire value
10) Now I need to delimit it
11) So post delimiting it will look like A) I am B) too much C) overwhelmed at D) the moment
12) ABCD are just for visual reference here
13) When I import the text file in DB, I am not sure what will be field name there, it can be anything
14) That is why I am renaming it as 'txtString'
15) I got a code from @moke123 to delimit imported text
16) It has used Auto number and field name as 'txtString' that is why I am editing field name in my table too
17) Since @moke123 help code has auto number I populate auto number in my imported text table too
18) Original imported text does not have auto number field
19) Once I import the entire text file, delimit it further, I need to split it and export to Excel
20) For example if the imported text file has 100,000 rows of data then I will export two tables with data of 50K in each
21) There will be n number of text files in a folder
22) I need to import each text file from the folder and process it in above manner

Am trying to do this, my apologies if am spamming the Forum.
Thanks.

 

[SachAccess]

Hi @jdraw I first tried to import the text file in DB however I did not know how to use specifications while importing.
Hence I copied the code from below URL to import the text file in DB.

VBA Code - Import a text file into an Access table - With Condition

I need to import a text file into an Access table, but using import conditions in TXT file. Below is my VBA code. It works only when you do not use the WHERE clause, because it is asking for param...

stackoverflow.com

One issues, I faced with this solution was, it does delimit the data while importing, it imports the way it is.
That is why I was trying to delimit imported text.

@Gasman is helping me a lot since yesterday, but somehow I was not able to provide complete information and I am very much new to MS Accesss. That is why I could edit his solutions to suit my requirement. I was able to delimit the text with @moke123 code.
But his code has auto number field in the imported text file table which was used as primary key.
This is the reason, I populated auto number field in my table and assigned it as primary key to get the results.

 

[SHANEMAC51]

2) Size of this text file will very

WHY NOT IMMEDIATELY TXT IN EXCEL

 

[SachAccess]

WHY NOT IMMEDIATELY TXT IN EXCEL

Data more than 1 million of rows. Thanks.

 

[moke123]

13) When I import the text file in DB, I am not sure what will be field name there, it can be anything

At what point do you know?

 

[SachAccess]

Hi @moke123 , thanks for the help, until and unless and open the table and see I won't know.
Sorry, I do not know other way to check it. Reason is, field name will be different with each file.

 

[jdraw]

SachAccess,
I provided 2 methods/samples to import "|" delimited text here.
That would handle your steps 1-6.
The first example also shows how to split the incoming data into fields via "Split function"
Example 2 will import the data into fields that you can name.

I think you are jumping from topic to topic. Perhap we should focus on your task list and solve each and all of the steps.
I don't understand your sample file --multiple rows of the same data??

As for field names, if there are always 4 or 7 or whatever you can refer to them as field(0)....field(x).
We just need to focus --1 step at a time.

 

[SachAccess]

Hi @jdraw , thanks for the help. I have used dummy data for testing purpose as original data is at another environment.
I will start from step 1 again.

Edited now.
PS - As am testing data, am getting a doubt, will it handle data more than 1 million rows.
Reason is, it is taking time to handle only 60K records.

 

[SHANEMAC51]

PS - As am testing data, am getting a doubt, will it handle data more than 1 million rows.
Reason is, it is taking time to handle only 60K records.

I had something similar, but first I split 1,000,000 records into 20 files of 50,000
each, only then I imported them into excel
. I also had UTF-8 encoding and a CHR(10) record separator

 

[SachAccess]

Hi @SHANEMAC51 will it be possible for your share Excel code for splitting TEXT files.
Originally I tried same thing but later on moved to Access. Am thinking of splitting the files and run in Excel only.
Reason is, am very new to Access and seeking help at every stage.

 

[The_Doc_Man]

How big are the files to be imported? More specifically, in total file size and in average file size? With 1 million records of multi-character text, and with using an intermediate import, my question is what is the size in KB or MB or GB of the files being imported? I am concerned because while you are importing, you might end up with double the actual file size because of Access intermediate tables and I wonder if you will blow out the 2 GB hard limit.

Another part of this is that an import of 1 million of anything will take a while and you commented on a mere 60K records taking a long time. Well, importing a million will take 16 times longer.

I see that you are involved in renaming table fields. Why rename them? If you are importing delimited text to an intermediate table, you can just import into that table with no specified field names, in which case the field names will be written as field0, field1, ..., fieldn - and thus will be predictable. Since you say it will ALL be text AND you know the delimiter (|), you should be able to do the import pretty easily.

The requirement to rename something is what is confusing here and may be a barrier to our fully understanding what you are trying to do.