Copy all tables from one database to another

**Tom van Stiphout** · Nov 12 '05, 08:55 PM

Re: Copy all tables from one database to another

On Wed, 25 Feb 2004 15:37:43 +1100, "dixie" <dixiec@dogmail .com>
wrote:

Not sure why you're not just making a copy of the database. But that
aside, you can walk the TableDefs collection to find the names of all
tables.

-Tom.

[color=blue]
>If I wanted to be able to copy all of the tables in an existing database
>called Original.mde to another database called New.mde from a button click
>event in Original.mde, is there an easy way of coding it without naming
>every single table in Original.mde. I am looking for a way of updating a
>database on site without needing to access the database window and import
>the existing tables into a new empty database.
>
>dixie
>[/color]

**Bradley** · Nov 12 '05, 08:55 PM

Re: Copy all tables from one database to another

Loop through the TableDefs collection and export each table... ?
--
Bradley
Software Developer www.hrsystems.com.au
A Christian Response www.pastornet.net.au/response

"dixie" <dixiec@dogmail .com> wrote in message
news:zFV_b.174$ ud3.2855@nnrp1. ozemail.com.au. ..[color=blue]
> If I wanted to be able to copy all of the tables in an existing database
> called Original.mde to another database called New.mde from a button click
> event in Original.mde, is there an easy way of coding it without naming
> every single table in Original.mde. I am looking for a way of updating a
> database on site without needing to access the database window and import
> the existing tables into a new empty database.
>
> dixie
>
>[/color]

**Lyle Fairfield** · Nov 12 '05, 08:55 PM

Re: Copy all tables from one database to another

"dixie" <dixiec@dogmail .com> wrote in news:zFV_b.174$ ud3.2855
@nnrp1.ozemail. com.au:
[color=blue]
> If I wanted to be able to copy all of the tables in an existing database
> called Original.mde to another database called New.mde from a button click
> event in Original.mde, is there an easy way of coding it without naming
> every single table in Original.mde. I am looking for a way of updating a
> database on site without needing to access the database window and import
> the existing tables into a new empty database.[/color]

SaveAsText 6, "", FileName

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

**dixie** · Nov 12 '05, 08:56 PM

Re: Copy all tables from one database to another

You couldn't give me an example of this I suppose.

dixie

"Bradley" <bradley@REMOVE THIScomcen.com. au> wrote in message
news:403c6123@n exus.comcen.com .au...[color=blue]
> Loop through the TableDefs collection and export each table... ?
> --
> Bradley
> Software Developer www.hrsystems.com.au
> A Christian Response www.pastornet.net.au/response
>
> "dixie" <dixiec@dogmail .com> wrote in message
> news:zFV_b.174$ ud3.2855@nnrp1. ozemail.com.au. ..[color=green]
> > If I wanted to be able to copy all of the tables in an existing database
> > called Original.mde to another database called New.mde from a button[/color][/color]
click[color=blue][color=green]
> > event in Original.mde, is there an easy way of coding it without naming
> > every single table in Original.mde. I am looking for a way of updating[/color][/color]
a[color=blue][color=green]
> > database on site without needing to access the database window and[/color][/color]
import[color=blue][color=green]
> > the existing tables into a new empty database.
> >
> > dixie
> >
> >[/color]
>
>[/color]

**Bradley** · Nov 12 '05, 08:56 PM

Re: Copy all tables from one database to another

This is off the top of my head so I hope it works ok :)

dim myDB as TableDef
set myTD = CurrentDB.Table Defs
for i = 0 to (myTD.Count - 1)
InTable = myTD(i).Name
OutTable = myTD(i).Name
OutDB = "c:\out.mdb "
docmd.copyobjec t OutDB, InTable, acTable, OutTable
next

--
Bradley
Software Developer www.hrsystems.com.au
A Christian Response www.pastornet.net.au/response

"dixie" <dixiec@dogmail .com> wrote in message
news:un7%b.387$ ud3.9409@nnrp1. ozemail.com.au. ..[color=blue]
> You couldn't give me an example of this I suppose.
>
> dixie
>
> "Bradley" <bradley@REMOVE THIScomcen.com. au> wrote in message
> news:403c6123@n exus.comcen.com .au...[color=green]
> > Loop through the TableDefs collection and export each table... ?
> > --
> > Bradley
> > Software Developer www.hrsystems.com.au
> > A Christian Response www.pastornet.net.au/response
> >
> > "dixie" <dixiec@dogmail .com> wrote in message
> > news:zFV_b.174$ ud3.2855@nnrp1. ozemail.com.au. ..[color=darkred]
> > > If I wanted to be able to copy all of the tables in an existing[/color][/color][/color]
database[color=blue][color=green][color=darkred]
> > > called Original.mde to another database called New.mde from a button[/color][/color]
> click[color=green][color=darkred]
> > > event in Original.mde, is there an easy way of coding it without[/color][/color][/color]
naming[color=blue][color=green][color=darkred]
> > > every single table in Original.mde. I am looking for a way of[/color][/color][/color]
updating[color=blue]
> a[color=green][color=darkred]
> > > database on site without needing to access the database window and[/color][/color]
> import[color=green][color=darkred]
> > > the existing tables into a new empty database.
> > >
> > > dixie
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

**dixie** · Nov 12 '05, 08:56 PM

Re: Copy all tables from one database to another

OK, thanks, I understand that. Am I Correct in presuming that this will not
bring the relationships with the tables? If not, is there a simple way of
doing it?

dixie

"Bradley" <bradley@REMOVE THIScomcen.com. au> wrote in message
news:s18%b.7627 9$Wa.73628@news-server.bigpond. net.au...[color=blue]
> This is off the top of my head so I hope it works ok :)
>
> dim myDB as TableDef
> set myTD = CurrentDB.Table Defs
> for i = 0 to (myTD.Count - 1)
> InTable = myTD(i).Name
> OutTable = myTD(i).Name
> OutDB = "c:\out.mdb "
> docmd.copyobjec t OutDB, InTable, acTable, OutTable
> next
>
> --
> Bradley
> Software Developer www.hrsystems.com.au
> A Christian Response www.pastornet.net.au/response
>
>
> "dixie" <dixiec@dogmail .com> wrote in message
> news:un7%b.387$ ud3.9409@nnrp1. ozemail.com.au. ..[color=green]
> > You couldn't give me an example of this I suppose.
> >
> > dixie
> >
> > "Bradley" <bradley@REMOVE THIScomcen.com. au> wrote in message
> > news:403c6123@n exus.comcen.com .au...[color=darkred]
> > > Loop through the TableDefs collection and export each table... ?
> > > --
> > > Bradley
> > > Software Developer www.hrsystems.com.au
> > > A Christian Response www.pastornet.net.au/response
> > >
> > > "dixie" <dixiec@dogmail .com> wrote in message
> > > news:zFV_b.174$ ud3.2855@nnrp1. ozemail.com.au. ..
> > > > If I wanted to be able to copy all of the tables in an existing[/color][/color]
> database[color=green][color=darkred]
> > > > called Original.mde to another database called New.mde from a button[/color]
> > click[color=darkred]
> > > > event in Original.mde, is there an easy way of coding it without[/color][/color]
> naming[color=green][color=darkred]
> > > > every single table in Original.mde. I am looking for a way of[/color][/color]
> updating[color=green]
> > a[color=darkred]
> > > > database on site without needing to access the database window and[/color]
> > import[color=darkred]
> > > > the existing tables into a new empty database.
> > > >
> > > > dixie
> > > >
> > > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

**Lyle Fairfield** · Nov 12 '05, 08:56 PM

Re: Copy all tables from one database to another

"dixie" <dixiec@dogmail .com> wrote in
news:Dia%b.53$b a4.1088@nnrp1.o zemail.com.au:
[color=blue]
> OK, thanks, I understand that. Am I Correct in presuming that this will
> not bring the relationships with the tables? If not, is there a simple
> way of doing it?[/color]

sigh ...

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

**Pavel Romashkin** · Nov 12 '05, 08:56 PM

Re: Copy all tables from one database to another

Wouldn't it be easier to just copy the entire file? It would preserve
the relationships.. .

Pavel

dixie wrote:[color=blue]
>
> OK, thanks, I understand that. Am I Correct in presuming that this will not
> bring the relationships with the tables? If not, is there a simple way of
> doing it?
>
> dixie
>
> "Bradley" <bradley@REMOVE THIScomcen.com. au> wrote in message
> news:s18%b.7627 9$Wa.73628@news-server.bigpond. net.au...[color=green]
> > This is off the top of my head so I hope it works ok :)
> >
> > dim myDB as TableDef
> > set myTD = CurrentDB.Table Defs
> > for i = 0 to (myTD.Count - 1)
> > InTable = myTD(i).Name
> > OutTable = myTD(i).Name
> > OutDB = "c:\out.mdb "
> > docmd.copyobjec t OutDB, InTable, acTable, OutTable
> > next
> >
> > --
> > Bradley
> > Software Developer www.hrsystems.com.au
> > A Christian Response www.pastornet.net.au/response
> >
> >
> > "dixie" <dixiec@dogmail .com> wrote in message
> > news:un7%b.387$ ud3.9409@nnrp1. ozemail.com.au. ..[color=darkred]
> > > You couldn't give me an example of this I suppose.
> > >
> > > dixie
> > >
> > > "Bradley" <bradley@REMOVE THIScomcen.com. au> wrote in message
> > > news:403c6123@n exus.comcen.com .au...
> > > > Loop through the TableDefs collection and export each table... ?
> > > > --
> > > > Bradley
> > > > Software Developer www.hrsystems.com.au
> > > > A Christian Response www.pastornet.net.au/response
> > > >
> > > > "dixie" <dixiec@dogmail .com> wrote in message
> > > > news:zFV_b.174$ ud3.2855@nnrp1. ozemail.com.au. ..
> > > > > If I wanted to be able to copy all of the tables in an existing[/color]
> > database[color=darkred]
> > > > > called Original.mde to another database called New.mde from a button
> > > click
> > > > > event in Original.mde, is there an easy way of coding it without[/color]
> > naming[color=darkred]
> > > > > every single table in Original.mde. I am looking for a way of[/color]
> > updating[color=darkred]
> > > a
> > > > > database on site without needing to access the database window and
> > > import
> > > > > the existing tables into a new empty database.
> > > > >
> > > > > dixie
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >[/color]
> >
> >[/color][/color]

**dixie** · Nov 12 '05, 08:57 PM

Re: Copy all tables from one database to another

The rest of the database would have been updated (as in a new version with
some fix or new feature), so it is not practical to just copy the file.
What I want to do is to import the old tables into the new database, getting
their relationships too. I know I can do this with a manual File --> Get
external data --> import, but I am trying to do it without the user needing
to go to the database window.

dixie

"Pavel Romashkin" <pavel_romashki n@hotmail.com> wrote in message
news:403D35AB.C 6ABB13E@hotmail .com...[color=blue]
> Wouldn't it be easier to just copy the entire file? It would preserve
> the relationships.. .
>
> Pavel
>
> dixie wrote:[color=green]
> >
> > OK, thanks, I understand that. Am I Correct in presuming that this will[/color][/color]
not[color=blue][color=green]
> > bring the relationships with the tables? If not, is there a simple way[/color][/color]
of[color=blue][color=green]
> > doing it?
> >
> > dixie
> >
> > "Bradley" <bradley@REMOVE THIScomcen.com. au> wrote in message
> > news:s18%b.7627 9$Wa.73628@news-server.bigpond. net.au...[color=darkred]
> > > This is off the top of my head so I hope it works ok :)
> > >
> > > dim myDB as TableDef
> > > set myTD = CurrentDB.Table Defs
> > > for i = 0 to (myTD.Count - 1)
> > > InTable = myTD(i).Name
> > > OutTable = myTD(i).Name
> > > OutDB = "c:\out.mdb "
> > > docmd.copyobjec t OutDB, InTable, acTable, OutTable
> > > next
> > >
> > > --
> > > Bradley
> > > Software Developer www.hrsystems.com.au
> > > A Christian Response www.pastornet.net.au/response
> > >
> > >
> > > "dixie" <dixiec@dogmail .com> wrote in message
> > > news:un7%b.387$ ud3.9409@nnrp1. ozemail.com.au. ..
> > > > You couldn't give me an example of this I suppose.
> > > >
> > > > dixie
> > > >
> > > > "Bradley" <bradley@REMOVE THIScomcen.com. au> wrote in message
> > > > news:403c6123@n exus.comcen.com .au...
> > > > > Loop through the TableDefs collection and export each table... ?
> > > > > --
> > > > > Bradley
> > > > > Software Developer www.hrsystems.com.au
> > > > > A Christian Response www.pastornet.net.au/response
> > > > >
> > > > > "dixie" <dixiec@dogmail .com> wrote in message
> > > > > news:zFV_b.174$ ud3.2855@nnrp1. ozemail.com.au. ..
> > > > > > If I wanted to be able to copy all of the tables in an existing
> > > database
> > > > > > called Original.mde to another database called New.mde from a[/color][/color][/color]
button[color=blue][color=green][color=darkred]
> > > > click
> > > > > > event in Original.mde, is there an easy way of coding it without
> > > naming
> > > > > > every single table in Original.mde. I am looking for a way of
> > > updating
> > > > a
> > > > > > database on site without needing to access the database window[/color][/color][/color]
and[color=blue][color=green][color=darkred]
> > > > import
> > > > > > the existing tables into a new empty database.
> > > > > >
> > > > > > dixie
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >[/color][/color][/color]

**dixie** · Nov 12 '05, 08:57 PM

Re: Copy all tables from one database to another

I can create each relationship in code separately, but I seem to remember
that if I, say, ask for Attributes = dbRelationUpdat eCascade +
dbRelationDelet eCascade and that relationship fails because someone has
managed to delete records such that the referential integrity is destroyed,
then no relationship is formed at all. In this case, I would need a simple
relationship without referential integrity to be created. But, because the
database is not with me, but out on a site somewhere, I have no way of
checking if the referential integrity failed or not. Is there a simple way
of in code, creating a normal relationship if referential integrity fails?

dixie

"dixie" <dixiec@dogmail .com> wrote in message
news:q2d%b.141$ ba4.2296@nnrp1. ozemail.com.au. ..[color=blue]
> The rest of the database would have been updated (as in a new version with
> some fix or new feature), so it is not practical to just copy the file.
> What I want to do is to import the old tables into the new database,[/color]
getting[color=blue]
> their relationships too. I know I can do this with a manual File --> Get
> external data --> import, but I am trying to do it without the user[/color]
needing[color=blue]
> to go to the database window.
>
> dixie
>
> "Pavel Romashkin" <pavel_romashki n@hotmail.com> wrote in message
> news:403D35AB.C 6ABB13E@hotmail .com...[color=green]
> > Wouldn't it be easier to just copy the entire file? It would preserve
> > the relationships.. .
> >
> > Pavel
> >
> > dixie wrote:[color=darkred]
> > >
> > > OK, thanks, I understand that. Am I Correct in presuming that this[/color][/color][/color]
will[color=blue]
> not[color=green][color=darkred]
> > > bring the relationships with the tables? If not, is there a simple[/color][/color][/color]
way[color=blue]
> of[color=green][color=darkred]
> > > doing it?
> > >
> > > dixie
> > >
> > > "Bradley" <bradley@REMOVE THIScomcen.com. au> wrote in message
> > > news:s18%b.7627 9$Wa.73628@news-server.bigpond. net.au...
> > > > This is off the top of my head so I hope it works ok :)
> > > >
> > > > dim myDB as TableDef
> > > > set myTD = CurrentDB.Table Defs
> > > > for i = 0 to (myTD.Count - 1)
> > > > InTable = myTD(i).Name
> > > > OutTable = myTD(i).Name
> > > > OutDB = "c:\out.mdb "
> > > > docmd.copyobjec t OutDB, InTable, acTable, OutTable
> > > > next
> > > >
> > > > --
> > > > Bradley
> > > > Software Developer www.hrsystems.com.au
> > > > A Christian Response www.pastornet.net.au/response
> > > >
> > > >
> > > > "dixie" <dixiec@dogmail .com> wrote in message
> > > > news:un7%b.387$ ud3.9409@nnrp1. ozemail.com.au. ..
> > > > > You couldn't give me an example of this I suppose.
> > > > >
> > > > > dixie
> > > > >
> > > > > "Bradley" <bradley@REMOVE THIScomcen.com. au> wrote in message
> > > > > news:403c6123@n exus.comcen.com .au...
> > > > > > Loop through the TableDefs collection and export each table... ?
> > > > > > --
> > > > > > Bradley
> > > > > > Software Developer www.hrsystems.com.au
> > > > > > A Christian Response www.pastornet.net.au/response
> > > > > >
> > > > > > "dixie" <dixiec@dogmail .com> wrote in message
> > > > > > news:zFV_b.174$ ud3.2855@nnrp1. ozemail.com.au. ..
> > > > > > > If I wanted to be able to copy all of the tables in an[/color][/color][/color]
existing[color=blue][color=green][color=darkred]
> > > > database
> > > > > > > called Original.mde to another database called New.mde from a[/color][/color]
> button[color=green][color=darkred]
> > > > > click
> > > > > > > event in Original.mde, is there an easy way of coding it[/color][/color][/color]
without[color=blue][color=green][color=darkred]
> > > > naming
> > > > > > > every single table in Original.mde. I am looking for a way of
> > > > updating
> > > > > a
> > > > > > > database on site without needing to access the database window[/color][/color]
> and[color=green][color=darkred]
> > > > > import
> > > > > > > the existing tables into a new empty database.
> > > > > > >
> > > > > > > dixie
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >[/color][/color]
>
>[/color]

**krisa** · Nov 12 '05, 08:57 PM

Re: Copy all tables from one database to another

It sounds as if you don't have a split frontend-backend database. If
you did, there would never be any "new features", etc. in the
back-end, and all you have to do is provide the users with a new
front-end.

Regards,
Krisa

Lyle Fairfield <MissingAddress @Invalid.Com> wrote in message news:<Xns949A50 45141DDFFDBA@13 0.133.1.4>...[color=blue]
> "dixie" <dixiec@dogmail .com> wrote in news:zFV_b.174$ ud3.2855
> @nnrp1.ozemail. com.au:
>[color=green]
> > If I wanted to be able to copy all of the tables in an existing database
> > called Original.mde to another database called New.mde from a button click
> > event in Original.mde, is there an easy way of coding it without naming
> > every single table in Original.mde. I am looking for a way of updating a
> > database on site without needing to access the database window and import
> > the existing tables into a new empty database.[/color]
>
> SaveAsText 6, "", FileName[/color]

Copy all tables from one database to another

Copy all tables from one database to another

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment