Using VBA to rename a table in Access

I tried looking this up and maybe I am using the wrong words, but I can't seem to find anything. That will help me.

What I need to do is loop through all the tables in the database which start with the word "Agreement" and take out the word "Agreement" from the title.

It seems simple... but I don't know how to make it work... can someone help me here?

Here's the code I have which loops through the tables... but I don't know how to reference the table name in order to cange it:

Dim dbCurr As DAO.Database
Dim intLoop As Integer
Dim tblName As String
Dim tblNewName As String

Set dbCurr = CurrentDb()

For intLoop = (dbCurr.TableDefs.Count - 1) To 0 Step -1

If Left$(dbCurr.TableDefs(intLoop).Name, 4) = "Agre" Then
tblName = dbCurr.TableDefs(intLoop).Name
tblNewName = Mid(tblName, 10, Len(tblName) - 9)

'OK so here is where I am missing the correct code
'what I want to say is something like:

'dbCurr.TableDefs(intLoop).Name tblNewName (but I know this won't work...)

End If

Next intLoop

Set dbCurr = Nothing

End Function

Click to expand...

Thanks for your help!
-Gary

close

dbCurr.TableDefs(intLoop).Name = tblNewName

should work - just needs an equal sign

--------
not sure about your looping mechanism though (the step -1) - try it and see if it works

the problem might be that changing the name may cause the loop to find the same file again (as renaming it may replace it in a different positon in the tabledefs collection, and accordingly the loop may not examine all the files - hope that makes sense - the problem is changing the details that you are using to manage the looping)

the worst way, just do it again, until you are happy that all the tables have been checked

never sure about these things until you try them

----------
eg its similar to saying

for x = 1 to 6
x=x+2
next

how many iterations would you get before the loop ended?

Nope it doesn't work... that's what I need though... something like this.

gblack said:

Nope it doesn't work... that's what I need though... something like this.

Click to expand...

I have just tested this and it worked fine. Where are you getting an error?

It was giving me an error on this line:

dbCurr.TableDefs(intLoop).Name = tblNewName

I ran it several times trying differnt things and to no avail... then I just tried it and it worked... (and I did have the = sign in there when I tried... my error in the post was a cut and paste issue)

Anyway... it works!!! Woot!

Thanks guys
-G