Design Question -> Copying a table in another database, the relinking it.

[mark_aok]

Hi all,

I have a situation where I have a split database. At the back end, I
need to
- create a new table (I will call it newTable) with the exact fields,
and relationships as another table (let's call it oldTable, and I need
to copy everything EXCEPT the data).

- Then I need to delete oldTable, and rename newTable 'oldTable'

The problem is, that all of this happens at the back end, and I'm
having some problems making this work.

I've tried using CreateTableDef, and this creates the
definitions...but it never manages to actually create a table (that I
can see).

I've also tried the following SQL,
SELECT * INTO newTable FROM oldTable Where 0 = 1;

The 0=1 will never occur, thus I will have a new table with the proper
definitions, but no data...which is what I want. But this creates a
table at the front end :( I need a table at the back end which is
linked...

Anyone have any ideas?

Mark

[jahoobob via AccessMonster.com]

Copy oldTable. Paste. When you paste you will be given three options. One
will allow you to copy the structure only.
Easier though to open the oldTable, press Ctrl+a and then press delete.
Voila! All the data is gone and you have a blank oldTable.

mark_aok wrote:

>Hi all,

I have a situation where I have a split database. At the back end, I
need to
- create a new table (I will call it newTable) with the exact fields,
and relationships as another table (let's call it oldTable, and I need
to copy everything EXCEPT the data).

- Then I need to delete oldTable, and rename newTable 'oldTable'

The problem is, that all of this happens at the back end, and I'm
having some problems making this work.

I've tried using CreateTableDef, and this creates the
definitions...but it never manages to actually create a table (that I
can see).

I've also tried the following SQL,
SELECT * INTO newTable FROM oldTable Where 0 = 1;

The 0=1 will never occur, thus I will have a new table with the proper
definitions, but no data...which is what I want. But this creates a
table at the front end :( I need a table at the back end which is
linked...

Anyone have any ideas?

Mark

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200707/1

[mark_aok]

On Jul 13, 11:10 am, "jahoobob via AccessMonster.com" <u12179@uwe>
wrote:

Copy oldTable. Paste. When you paste you will be given three options. One
will allow you to copy the structure only.
Easier though to open the oldTable, press Ctrl+a and then press delete.
Voila! All the data is gone and you have a blank oldTable.

mark_aok wrote:

Hi all,

I have a situation where I have a split database. At the back end, I
need to
- create a new table (I will call it newTable) with the exact fields,
and relationships as another table (let's call it oldTable, and I need
to copy everything EXCEPT the data).

- Then I need to delete oldTable, and rename newTable 'oldTable'

The problem is, that all of this happens at the back end, and I'm
having some problems making this work.

I've tried using CreateTableDef, and this creates the
definitions...but it never manages to actually create a table (that I
can see).

I've also tried the following SQL,
SELECT * INTO newTable FROM oldTable Where 0 = 1;

The 0=1 will never occur, thus I will have a new table with the proper
definitions, but no data...which is what I want. But this creates a
table at the front end :( I need a table at the back end which is
linked...

Anyone have any ideas?

Mark

--
Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/2007...

I'm really sorry, I forgot to mention that I need to do this using
VBA.

So far I have code to copy the table definitions, but the problem is
copying the relationships. I know how to do this manually, and I
could easily tell my users how to do this, but it would be MUCH more
conveniant, if there was a way to copy a table's relationships into
the new table. This is the part that's killing me right now.

Mark

[Chris L.]

As jahoobob said, why don't you just delete each and every record on
oldTable?

The final result will be the same as if you created a new empty table,
dropped the old full table and renamed the new empty table to "old",
won't it?
On Jul 13, 12:39 pm, mark_aok <[e-mail]:

On Jul 13, 11:10 am, "jahoobob via AccessMonster.com" <u12179@uwe>
wrote:

Copy oldTable. Paste. When you paste you will be given three options. One
will allow you to copy the structure only.
Easier though to open the oldTable, press Ctrl+a and then press delete.
Voila! All the data is gone and you have a blank oldTable.

mark_aok wrote:

>Hi all,

>I have a situation where I have a split database. At the back end, I
>need to
>- create a new table (I will call it newTable) with the exact fields,
>and relationships as another table (let's call it oldTable, and I need
>to copy everything EXCEPT the data).

>- Then I need to delete oldTable, and rename newTable 'oldTable'

>The problem is, that all of this happens at the back end, and I'm
>having some problems making this work.

>I've tried using CreateTableDef, and this creates the
>definitions...but it never manages to actually create a table (that I
>can see).

>I've also tried the following SQL,
>SELECT * INTO newTable FROM oldTable Where 0 = 1;

>The 0=1 will never occur, thus I will have a new table with the proper
>definitions, but no data...which is what I want. But this creates a
>table at the front end :( I need a table at the back end which is
>linked...

>Anyone have any ideas?

>Mark

--
Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/2007...

I'm really sorry, I forgot to mention that I need to do this using
VBA.

So far I have code to copy the table definitions, but the problem is
copying the relationships. I know how to do this manually, and I
could easily tell my users how to do this, but it would be MUCH more
conveniant, if there was a way to copy a table's relationships into
the new table. This is the part that's killing me right now.

Mark- Hide quoted text -

- Show quoted text -

[John]

I have a situation where I have a split database. At the back end, I

need to
- create a new table (I will call it newTable) with the exact fields,
and relationships as another table (let's call it oldTable, and I need
to copy everything EXCEPT the data).

This doesn't sound logical as multiple users may need to perform this same
action at the same time, which is impossible this way. What exactly is it
that you're trying to achieve? There may be a better solution.

john
"mark_aok" <ma******@hotmail.comschreef in bericht
news:11**********************@q75g2000hsh.googlegr oups.com...

Hi all,

I have a situation where I have a split database. At the back end, I
need to
- create a new table (I will call it newTable) with the exact fields,
and relationships as another table (let's call it oldTable, and I need
to copy everything EXCEPT the data).

- Then I need to delete oldTable, and rename newTable 'oldTable'

The problem is, that all of this happens at the back end, and I'm
having some problems making this work.

I've tried using CreateTableDef, and this creates the
definitions...but it never manages to actually create a table (that I
can see).

I've also tried the following SQL,
SELECT * INTO newTable FROM oldTable Where 0 = 1;

The 0=1 will never occur, thus I will have a new table with the proper
definitions, but no data...which is what I want. But this creates a
table at the front end :( I need a table at the back end which is
linked...

Anyone have any ideas?

Mark

[mark_aok]

On Jul 13, 1:25 pm, "John" <[e-mail]:

I have a situation where I have a split database. At the back end, I
need to
- create a new table (I will call it newTable) with the exact fields,
and relationships as another table (let's call it oldTable, and I need
to copy everything EXCEPT the data).

This doesn't sound logical as multiple users may need to perform this same
action at the same time, which is impossible this way. What exactly is it
that you're trying to achieve? There may be a better solution.

john

"mark_aok" <[e-mail] in berichtnews:11**********************@q75g2000hsh.g ooglegroups.com...

Hi all,

I have a situation where I have a split database. At the back end, I
need to
- create a new table (I will call it newTable) with the exact fields,
and relationships as another table (let's call it oldTable, and I need
to copy everything EXCEPT the data).

- Then I need to delete oldTable, and rename newTable 'oldTable'

The problem is, that all of this happens at the back end, and I'm
having some problems making this work.

I've tried using CreateTableDef, and this creates the
definitions...but it never manages to actually create a table (that I
can see).

I've also tried the following SQL,
SELECT * INTO newTable FROM oldTable Where 0 = 1;

The 0=1 will never occur, thus I will have a new table with the proper
definitions, but no data...which is what I want. But this creates a
table at the front end :( I need a table at the back end which is
linked...

Anyone have any ideas?

Mark

Hey, the purpose of this is to uncorrupted the database. I have
written code which takes the contents of a table, and copies the non-
corrupted data to a new table. But to do so, I needed to copy the
relationships, and the definition of the table first.

Thanks for everyone's input, but I think I'm on the right track now.
It turns out DAO has everything needed
DAO.relationship, and Database.Relationships contain pretty much
everything I need. Thanks for all the input though,

Mark