Solved VBA To WildCard Search For A Table And Rename

[jo15765]

How can I search all tables in my database for a table named *DavidsData and rename it to _DavidsDataLocal through VBA?

 

[theDBguy]

Hi. I think there is an add-in that can help you do that. However, I would caution you because changing the table's name might affect other objects in your database project.

 

[jo15765]

an add-in that can be run through vba? I was hoping to run this solely through vba code...

I feel it will be something along the lines of this, altho this does not allow me to specify a table name with wild cards...

For Each tdf In CurrentDb.TableDefs
    If Left(tdf.Name, 10) <> "MSys" Then
      tdf.Name = tdf.Name & "_DavidsDataLocal"
    End If
Next

Maybe something like this

For Each tdf In CurrentDb.TableDefs
    If Right(tdf.Name, 10) = "DavidsData" Then
      tdf.Name = tdf.Name & "_DavidsDataLocal"
    End If
Next

 

[theDBguy]

an add-in that can be run through vba? I was hoping to run this solely through vba code...

Maybe, maybe not. I haven't used the add-in to say for sure.

I feel it will be something along the lines of this, altho this does not allow me to specify a table name with wild cards...

For Each tdf In CurrentDb.TableDefs
    If Left(tdf.Name, 10) <> "MSys" Then
      tdf.Name = tdf.Name & "_DavidsDataLocal"
    End If
Next

Maybe something like this

For Each tdf In CurrentDb.TableDefs
    If Right(tdf.Name, 10) = "DavidsData" Then
      tdf.Name = tdf.Name & "_DavidsDataLocal"
    End If
Next

What happened when you tried that?

 

[jo15765]

When I ran my second VBA syntax it worked, but not sure if I am not thinking of, or not aware of, any use cases that could cause this to fail?

 

[theDBguy]

When I ran my second VBA syntax it worked, but not sure if I am not thinking of, or not aware of, any use cases that could cause this to fail?

It might fail if you have more than one table that ends with "DavidsData."