Archive one table and make a new one with the same name. (1 Viewer)

Thales750

Formerly Jsanders
Local time
Today, 06:58
Joined
Dec 20, 2007
Messages
2,061
Have had no joy fixing that busted table.
We downloaded it into an Access back end and made it work for the last two weeks. The clients needs it to be back in the SQL Server because they have a whole Chemical plant that uses that data and for their reporting software.

I imported a blank table a copy of the original that was migrated back to the Access Backend.
ran a query to add all the current records and now the new table works

The problem is now I need to archive the old table and create a new one with the same name even after I changed the named to an archived style name . From an Access point of view it doesn't mater, but apparently they have a rather complicated linking arrangement and they don't want to mess with it, so the onus is on me.

Does SQL Server hold these names forever?

Any ideas?

I think I'll make a copy of the data and try deleting the table and then renaming the new one.
 

Isaac

Lifelong Learner
Local time
Today, 03:58
Joined
Mar 14, 2017
Messages
8,738
I'm confused on what your question is.

You want to rename a linked table in Access? Just right click and rename it.
You want to rename a table on SQL Server? I have used SSMS object designer to do that in the past, of course you could also select into ... then drop the original one.

Or is your question something else ??
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:58
Joined
Feb 19, 2002
Messages
42,981
I asked several questions in your other thread and you never answered them. You just threw up your hands and started a new thread.

If you've been updating the data off line, the two tables are now out of sync and since the original table belongs to relationships, that makes the whole thing worse. Taking the whole database off line would have been a better solution. But now you have to deal with dropping the relationships and removing the old table and then putting the new table back in the database.

I don't understand why your DBA is not helping with this. This is most certainly not the job of an Access developer who doesn't know enough about SQL server to determine if a table has a unique index.

In Access, deleting a linked table doesn't do anything to the physical table. It just removes the link. Once you delete the original table, you can link to the new one. Renaming the original table in the server to some archive name and then rename the new version to the original name. Fix all the relationships so they link to the new table rather than the old.

Then go back to your Access FE and relink.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:58
Joined
May 7, 2009
Messages
19,175
instead of Re-creating same table, just Archive (copy) its record to a History table (in SQL server or access).
then delete the table's record.
 

Thales750

Formerly Jsanders
Local time
Today, 06:58
Joined
Dec 20, 2007
Messages
2,061
It seemed like there was a delay in the propagation of the original name change, in spite of refreshing the table twice.


Thanks y'all.
 

Users who are viewing this thread

Top Bottom