Renaming tables (1 Viewer)

zbird · Nov 7, 2014

Hi there,

I have created some tables but I used names with space. I split the database to back end and front end and I am a bit worried that adding space to the table's name will cause problem for me in the future. Can I just simply change the name or will I loose the data from the table if I do that? How can I get over this problem if not?

Many thanks

pr2-eugin · Nov 7, 2014

Well it is going to be a painful task I would say, but it is a very good step. I would recommend you that you go through the pain one time to rename the tables. Mind you also have to change all Queries and Code and Form's recordsources to reflect the change.

No data will be lost, just need to figure out how you will manage the changes all through the DB.

zbird · Nov 7, 2014

Thank you, Paul. These tables are mainly 'drop down' lists of fields. Would it be a problem if I don't change them at all? The main tables are without space.
The database has got a hundred queries and reports, do I need to go through them individually?

Many thanks,
Susanna

stopher · Nov 7, 2014

As Paul said, renaming tables does not lose the data. Nevertheless, it would be prudent to take a complete backup of both front and backend before you embark on anything like this.

Just thinking this through in my head, I think you can do this:

Change the backend table name
Re-link the front-end table-link to the backend table
Rename the front end table link

I think if you do that you should find that Access will change all the table name references in your forms, queries and reports etc for you in your front end. But I'm not sure on this so maybe give it a go with one table first.

hth
Chris

zbird · Nov 7, 2014

Thank you, Chris. So I have changed the name of 2 tables and added some fields to others in the back end. What is the next step? How can I re-link the modified back end table links to the front end?

The database has a password. One for the front end and one for the back end. Will these be affected when I re-split or re-link the database?

Many thanks,
Susanna

zbird · Nov 7, 2014

I think I have the answer for re-linking tables, but I don't know whether I need to delete all at first or just update them rather. (Exernal Data > Import & Link > LInked Table Manager). I tried to update it at first, but my renamed tables don't want to be linked.

1. An error message comes up that the database doesn't find the table with the original name.

As these are drop down tables I think I get around it somehow, although if you have a suggestion it would be much appreciated.

2. Another error occurs in the Front End. When I click on the link to the form on the Switchboard a window appears and asks the Parameter value of certain fields that I have previously renamed. How can I make this disappear?

Many thanks,
Susanna

zbird · Nov 7, 2014

Parameter value issue is sorted, but not 'changed table name' issue.

winshent · Nov 7, 2014

If there are spaces in the table names, are there also spaces in the field names ?

It seems like an unnecessary piece of work, as although its not good practise to use spaces in names, your database should work fine with them.

Whereas if you go down this route, you potentially can 'corrupt' the SQL statements within each query if tables/ fields cannot be identified. You may also need to rebuild your relationships between the tables.

I personally would trial splitting out the DB and then testing first.

zbird · Nov 7, 2014

The field names haven't got any spaces. Only one of the tables has but the database has been working fine so far. I am just a bit worried that something might happen in the future and just before I train people to use it I had a look and found this mistake.

essaytee · Nov 8, 2014

All your SQL statements will have to be changed as you've already alluded to, just wondering if you've already done that?

Many years ago I wrote myself a utility to achieve exactly this (directly updating/editing of the Query SQL statements) but alas I can not find it at the moment.

Within VBA it's a matter of cycling through the QueryDef collection. With a bit of coding, filter the SQL component to your hearts content. At this point your filtered list may reveal a dozen queries out of 100's. At this point you can either manually edit those queries directly via usual means or with a bit more coding enter your replacement value and update via VBA.

I recall implementing this by a single form, listbox to hold the queries, listbox to display the fields of the selected query and a text box displaying the SQL for the selected query. Also included a textbox for lookup value (effectively a Like *crtieria*) and a textbox for the replacement value. Of course buttons to do various things (open database, remove filter, save, etc.)

I'll continue to search my drives but I have a feeling it may be at work and as of today I'm on three weeks holidays.

Steve.

Galaxiom · Nov 8, 2014

Use the Total Deep Search in V-tools to find and change every reference in the project.

Don't just let it change everything without confirming or you might get some surprises.

spikepl · Nov 8, 2014

And after you've made the changes, you have expended hours of effort on changing and testing, and at the end of the day you have a DB that does neither more nor less than before.

Since you have a WORKING application, suppress this urge to tidy things up and leave it as is!

winshent · Nov 8, 2014

spikepl said:
And after you've made the changes, you have expended hours of effort on changing and testing, and at the end of the day you have a DB that does neither more nor less than before.

Since you have a WORKING application, suppress this urge to tidy things up and leave it as is!

+1

Your system is working.. there is no need to change it..

Renaming tables (1 Viewer)

zbird

Registered User.

pr2-eugin

Super Moderator

zbird

Registered User.

stopher

AWF VIP

zbird

Registered User.

zbird

Registered User.

zbird

Registered User.

winshent

Registered User.

zbird

Registered User.

essaytee

Need a good one-liner.

Galaxiom

Super Moderator

spikepl

Eledittingent Beliped

winshent

Registered User.

Similar threads

Users who are viewing this thread