How to change the table name in MSAccess database Programatically

Question

Hello,

We are using MS-Access as our backend database. Already the database is in production environment.

Now we need to change some table names in the Ms-Access Database.

Please help me to change the table names in MS-Access database programmatically or through query execution.


Regards,
Naga Suresh D

Answer 1

Good Afternoon Naga Suresh,

You could send the Table out as text and then bring in as Table, giving it the new name at that time. Bob has an example on his web page here

http://www.thatlldoit.com/Pages/howtosarticles.aspx

---

Chris Ward

Answer 2

The easiest thing to do is to get a list of the tables you want to change the name of.  Then just create the new table and add the data from the old table.  Then delete the old table.  You can do this:

DoCmd.RunSql "Select * Into NewTbl from OldTable"

then

DoCmd.RunSql "Drop Table OldTable"

---

Rich P

Answer 3

Now we need to change some table names in the Ms-Access Database.

Hi Naga,

You can just rename the name of a table to the new name. If it is a table in the BE, then you have to relink in FE the link to that table.

If you want to rename programmatically, then you use the appropriate methods of CurrentDb.

Renaming a table can have a lot of consequences for QueryDefs, RecordSources, RowSources and all kind of used code. It is quite a job to adjust all those references to the new table name completely. If you want to do this programmatically, then the success depends very much on your used systematics.

Imb.

Answer 4

>>
We are using MS-Access as our backend database. Already the database is in production environment.
<<

If you want to change the Table names in the back-end by code using the front-end / client component or some other platform (since the implication from your post is that you are not using Access Front-End) then we problably need to know yur front-end environment.

If you are familiar with the DAO Object Model, then you should be able to change the Table name via the TableDef Object.  See MSDN article:

TableDef Object (DAO)

and its Property "Name".

 

---

Van Dinh

Answer 5

Is it me? or does anyone else think this to be a really bad idea? ....change table names while users are in & using the database?.... this a formula for disaster.....

Answer 6

>>
Is it me? or does anyone else think this to be a really bad idea? ....change table names while users are in & using the database?....
<<

It is NOT something we want to do for a production database, especially if we use Access Front-End.  However, I suspect that Naga uses a different platform for the Front-End and may have some valid reason to do so, e.g. renaming the Table to "Old" to hold the database and creating Table "New" with modified structure to work with the new Front-End and then massaging the "Old" data into Table "New".

 

---

Van Dinh

Answer 7

Is it me? or does anyone else think this to be a really bad idea? ....change table names while users are in & using the database?.... this a formula for disaster.....

Hi msdn...,

To follow my systematics of structured code, it is sometimes better to give a table a better name. From time to time I have such a situation. I am not afraid to do that, because one part of the work is done by modifying a couple of definition tables - and this is done automatically - and the other part is modifying then general modules in the FE - mostly automatically.

Upon installation a new version in the production environment, these data definition changes are automatically performed. A new installation is of course under the condition that no users are using the database.

So, it is doable in a safe way. But it is not a standard Access functionality.

Imb. 

Answer 8

It seems like you could have Googled for an answer in much less time that I would take to create this post.

http://support.microsoft.com/kb/291017/en-us

---

Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.