Rename Access tables dynamically (1 Viewer)

mayasak

New member
Local time
Today, 11:21
Joined
Jun 24, 2022
Messages
3
Hi,

I have an ms access database with 6 tables. Table names are long and have spaces/slashes. I need to rename the tables every day so that it's shorter and has no spaces or slashes as below:

table 123/abc 2022 --> table123

any ideas if this can be done dynamically(automated) will be appreciated.

Is it doable with VBA?
 

piano_playr

Member
Local time
Today, 11:21
Joined
Oct 31, 2014
Messages
30
I use this procedure to trim "dbo_" from linked SQL Server tables when relinking. Access consists of collections and objects. It helps to review the Access object model. There is a collection called "tabledefs". Looping through it will let you rename tables using the .name property.

Code:
Public Sub DropDBO()
    
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim tdfSubscr As TableDef
    Dim tdfLoop As TableDef
    Dim prpLoop As Property
    Dim strName As String
    
    Set dbs = CurrentDb()
    With dbs
        For Each tdfLoop In .TableDefs
            strName = tdfLoop.Name
            Debug.Print strName
            If Mid(strName, 1, 4) = "dbo_" Then
                'Debug.Print tdfLoop.Name
                tdfLoop.Name = LTrim(RTrim(Mid(strName, 5, Len(strName))))
                Debug.Print tdfLoop.Name
            End If
            
        Next tdfLoop
        Set dbs = Nothing
    End With
    
End Sub
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 14:21
Joined
Feb 19, 2002
Messages
42,971
Why do you have to rename them every day? Once they are renamed, are you saying that somehow they revert? If you are talking about tables you import or link, you can fix the problem as you import by changing the target name.
 

plog

Banishment Pending
Local time
Today, 13:21
Joined
May 11, 2011
Messages
11,611
Is it doable with VBA?

Probably, but you've neither defined the renaming rules nor given enough context.

1. Why must you constantly rename them?

2. Are they always named the same to start? Will table 123/abc 2022 always be the starting name?

3. What are the renaming rules?

4. What does renaming achieve for you?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 13:21
Joined
Feb 28, 2001
Messages
26,999
Another problem is that if you rename the tables and they contribute to queries, all such queries will be broken the moment you change the tables. Any forms and reports that directly use those tables as .RecordSources will break. On forms, any combo boxes or list boxes that use such tables as .RowSources will break. Any VBA code that opens recordsets directly to those tables will break. Any macros that try to do things with those tables will break.

These consequences will make you very busy for quite a while, I think. Perhaps you would do better to consider WHY you want to do this.

If the problem is that you create new tables like this very often, perhaps you should instead consider not using such troublesome names.

There IS such a thing as creating a query that refers to a single table and that allows you to use the same field names. You could provide the query with the "alias" name that you wanted to use for the rename. However, if this oddball naming system is going to continue, you might do better to rethink your strategy completely.
 

jdraw

Super Moderator
Staff member
Local time
Today, 14:21
Joined
Jan 23, 2006
Messages
15,364
mayasak,

Others have hinted, but I want to explicitly ask you to describe What exactly is the reason that tables have to be renamed on a daily basis? This seems to be a strange request. If you are getting data from some external source, then you could ask that source to name the records to your mutual benefit OR
you could import to a staging table and adjust in a consistent manner. That is set up the process and run it for every import. It becomes 1 step in the process without having to manually change table names daily.
Perhaps you have a rare case/condition that reader have not heard before.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 14:21
Joined
Feb 19, 2002
Messages
42,971
Three of the five experts think that the changing names is the problem that needs to be solved. Giving you code to rename them is just going to bury or even exacerbate the actual problem. This is not normal behavior. Tables don't arbitrarily change names so once we know what you are actually doing, we'll be in a better position to provide a real solution.
 

Isaac

Lifelong Learner
Local time
Today, 11:21
Joined
Mar 14, 2017
Messages
8,738
I'm going to go out on a limb here and guess that you have a process to link new tables each day, OR, you have a process where you have to import a bunch of stuff each day.
Right?

So the better approach (IMO) would be to make the improvement to your linking routine or importing routine in order to name the tables "correctly" - rather than letting the junk fly in and then code stuff to clean it up.

This way you'll have more precision and control over what is being done - rather than have to worry that your "batch rename" routine might cast too wide a net, or too narrow a net, or otherwise mess something up.

Renaming tables en masse sounds pretty scary to me.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 13:21
Joined
Feb 28, 2001
Messages
26,999
Not to mention that you will run into limits if the older tables stick around, since you can only have (I think) 32K tables maximum. If you must rename 6 tables per day to make room for other new tables, you face something like total shutdown in 14 years.
 

piano_playr

Member
Local time
Today, 11:21
Joined
Oct 31, 2014
Messages
30
May I propose the solution below? I am not going to attempt to talk you out of this. I assume you know what you're doing and this is what you want to do. Of course, this solution has no warranty of fitness for a particular purpose, an implied warranty of merchantability for products, or an implied warranty of workmanlike quality for services. Proceed at your own risk. I bear no responsibility for the outcome.

First, build a table (see code below). This is a SQL Server table. It could otherwise be an Access table. Your choice. Populate the table with old table names and new table names. Then run the VBA procedure below.

Code:
-- SQL Server script to create table from/to table
set ansi_nulls on
go

set quoted_identifier on
go

create table [dbo].[Table_1](
    [ID] [int] identity(1,1) not null,
    [OldName] [nvarchar](50) null,
    [NewName] [nvarchar](50) null,
constraint [PK_Table_1] primary key clustered
(
    [ID] asc
)with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on, allow_page_locks = on) on [PRIMARY]
) on [PRIMARY]
go

Code:
Public Sub ChangeTableNames()
' sub to rename tables

On Error GoTo Error_Handler

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim tdfLoop As TableDef
    Dim sTable As String
    Set db = CurrentDb
    Set rst = db.OpenRecordset("Table_1", dbOpenDynaset)
    With db
        For Each tdfLoop In .TableDefs
            sTable = tdfLoop.Name
            With rst
                .FindFirst Criteria:="[OldName] = '" & sTable & "'"
                    If Not .NoMatch Then
                        tdfLoop.Name = rst("NewName")
                    End If
            End With
        Next tdfLoop
    End With
    rst.Close
    Set rst = Nothing
    db.Close
    Set db = Nothing
   
Exit_Point:
    Exit Sub
   
Error_Handler:
    MsgBox _
    "An unexpected error has occurred.  The system's description " & _
    "of this error is:" & vbCr & vbCr & _
    "Error " & Err.Number & ": " & Err.Description, _
    vbExclamation, _
    "Unexpected Error"
   
    Resume Exit_Point
   
End Sub
 

Attachments

  • rename_tables.accdb
    868 KB · Views: 105

mayasak

New member
Local time
Today, 11:21
Joined
Jun 24, 2022
Messages
3
Thank you all for this discussion. This is what I'm doing exactly: I pull the data from a website to MS Access daily. The imported table names are long and have spaces/slashes. The number of imported files is 10 with 6 tables in each file. The data should be cleaned, recoded where necessary, deduplicated, then merged into one dataset. So what I do is import the Access files into SAS to manipulate and merge the data. What I'm trying to do is to streamline the process as much as I can.
 

KitaYama

Well-known member
Local time
Tomorrow, 03:21
Joined
Jan 6, 2022
Messages
1,489
Another problem is that if you rename the tables and they contribute to queries, all such queries will be broken the moment you change the tables. Any forms and reports that directly use those tables as .RecordSources will break. On forms, any combo boxes or list boxes that use such tables as .RowSources will break.
Just for your information, In recent versions of Access (at least in 365) if I change the name of tables, the queries and forms based on these tables are corrected accordingly.
Likewise, if I change the name of queries, all other queries and forms' record sources or row sources are corrected. Access manages everything including correcting source row of list boxes and combo boxes. In case of VBA, I have to correct them manually.
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 13:21
Joined
Feb 28, 2001
Messages
26,999
Ah, thanks KitaYama. I don't have O365 since I am now retired. Stopped updating when I retired.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:21
Joined
Oct 29, 2018
Messages
21,358
Just for your information, In recent versions of Access (at least in 365) if I change the name of tables, the queries and forms based on these tables are corrected accordingly.
Likewise, if I change the name of queries, all other queries and forms' record sources or row sources are corrected. Access manages everything including correcting source row of list boxes and combo boxes. In case of VBA, I have to correct them manually.
Hi. I don't have 365 either. Is that different from this?
1656169441789.png

Do you still have that option? If so, can you show/tell us what your setting is? Thanks!
 

KitaYama

Well-known member
Local time
Tomorrow, 03:21
Joined
Jan 6, 2022
Messages
1,489
Hi. I don't have 365 either. Is that different from this?
View attachment 101433
Do you still have that option? If so, can you show/tell us what your setting is? Thanks!
@theDBguy

I never noticed the option you mentioned. Yes, it seems that's the one. I have it ticked.
If I untick it, Access doesn't correct list boxes, combo boxes row source or forms' record source after renaming a table or a query.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:21
Joined
Oct 29, 2018
Messages
21,358
@theDBguy

I never noticed the option you mentioned. Yes, it seems that's the one. I have it ticked.
If I untick it, Access doesn't correct list boxes, combo boxes row source or forms' record source after renaming a table or a query.
Hi. Thanks for the clarification.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 14:21
Joined
Feb 19, 2002
Messages
42,971
The imported table names are long and have spaces/slashes.
Back in post #3, I told you where to fix this problem. Do you need detail instructions?
 

Isaac

Lifelong Learner
Local time
Today, 11:21
Joined
Mar 14, 2017
Messages
8,738
Thank you all for this discussion. This is what I'm doing exactly: I pull the data from a website to MS Access daily. The imported table names are long and have spaces/slashes. The number of imported files is 10 with 6 tables in each file. The data should be cleaned, recoded where necessary, deduplicated, then merged into one dataset. So what I do is import the Access files into SAS to manipulate and merge the data. What I'm trying to do is to streamline the process as much as I can.
That's what I thought.
So follow my suggestion and handle it when you import them not after.
Case closed.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 13:21
Joined
Feb 28, 2001
Messages
26,999
OK, I'll try explaining this differently, and to my colleagues, I'm just trying to get a point across because it seems to have not been made clearly. I make no claim of a new idea, just a new approach.

When you have these tables, I'm going to assume there is some value to be had in knowing from where the data originated (based on the name of the input file, for example). So in plain language (no code) this is what to do.

I'm assuming that the daily tables have similar or identical structures each day, just different contents. (If they don't, you have an impossible situation to organize.) Build as many tables as you need in the main database to "cover" the incoming file structures. If they are ALL the same structure, just different sources and contents, then you only need one main table. If a day's set of six files each has a different structure, but for any given day they have the same structure (from day to day), you need six tables, one for each structure. But that's ALL you need.

When you import the new tables, don't do it in your main DB. Instead, create (once, beforehand) an empty working database file. Or it can even have tables such as you might need for the incoming files' structures. Before importing anything, COPY the empty working DB to a new DB file. Open that (copied) database. Import the six tables including the name data into the working file. If you have to update the tables from those imported files to diddle formats or other tailoring, do it in the working file. Do whatever you have to do.

Now you have the names of the six files. Enter those names into a table that lists each file name, the date, and a unique index number for each table. This index COULD be an autonumber. Probably should be.

Using appropriate queries, import the data from the six new tables, appending the data to the main file with the matching structure. As part of that query, you include the index number of the file from which it came. So that means one extra field in each of the main tables to hold info about the data sources.

Now you can delete the copy of the working DB because you are done with it. This means that you don't have to do any other cleaning of your main DB because the dirty work didn't occur there. If you later need to know from where something came, you have the index to the table that recorded file name and date. But you renamed NOTHING in your main DB. And you deleted NOTHING. So you minimize two of the main sources of database bloat.

Sounds complicate? Yes, it is. But not much more complicated that renaming files every day and trying to track them in some obscure method.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 18:21
Joined
Sep 12, 2006
Messages
15,614
You can easily rename a table in DAO, This needs a bit more attention, but basically

db.tabledefs("tablename").name = "newname"

It's hard to see why you would want to rename any meta object of this nature though, as it will can easily stop things working.
But if you import an unlinked table, and want to rename it, that will do it.

Note that if the imported table has illegal characters as far as access is concerned, the table name will be modified by access.


Code:
Sub RenameATable()
Dim db As Database

Set db = CurrentDb

db.TableDefs("existingtablename").Name = "newtablename"
db.TableDefs.Refresh 'may not be necessary

End Sub
 
Last edited:

Users who are viewing this thread

Top Bottom