>Using vba code behind a form in the FRONT-end, I want to:

>1. Open the back end, copy a table it contains and have the copy reside,
>UNLINKED, in the back-end database.

>>==================================================================
>>I have a front end and back end data base app.

>>Using vba code behind a form in the FRONT-end, I want to:

>>1. Open the back end, copy a table it contains and have the copy reside,
>>UNLINKED, in the back-end database.

>Use OpenDatabase to create a database object for the Back
>End db.  Note: the path to the BE db is part of the Connect
>property of any linked table in the FE db.

>>2. Open the newly created table and change the Primary Key column from an
>>AutoNumber field type to a Number, Long type with the PrimaryKey left in
>>place.

>Create the new table and fields using the CreateTableDef and
>CreateField methods.  Alternatively, and probably easier, is
>to Execute a Create Table query.

>After the table and fields are established, Execute an
>Insert Into query to copy the data.

>>3. Link the newly created table to the front-end database.

>After all of the above is done, then create a new TableDef
>in the FE db and set its Connect property to link it.

>>This is too large a question for a detailed answer.  See
>>below for some suggestions.

>>>==================================================================
>>>I have a front end and back end data base app.

>>>Using vba code behind a form in the FRONT-end, I want to:

>>>1. Open the back end, copy a table it contains and have the copy reside,
>>>UNLINKED, in the back-end database.

>>Use OpenDatabase to create a database object for the Back
>>End db.  Note: the path to the BE db is part of the Connect
>>property of any linked table in the FE db.

>>>2. Open the newly created table and change the Primary Key column from an
>>>AutoNumber field type to a Number, Long type with the PrimaryKey left in
>>>place.

>>Create the new table and fields using the CreateTableDef and
>>CreateField methods.  Alternatively, and probably easier, is
>>to Execute a Create Table query.

>>After the table and fields are established, Execute an
>>Insert Into query to copy the data.

>>>3. Link the newly created table to the front-end database.

>>After all of the above is done, then create a new TableDef
>>in the FE db and set its Connect property to link it.

>Thanks Marshall.

>This will work.  I was hoping for a way to avoid all that code.  All I need
>to do is open the back end db and then COPY an existing table and have it
>reside UNLINKED in the back end. I have tried the following code:

>DoCmd.CopyObject "C:\AppDir\BackEnd.mdb", "tblExistingTable", acTable,
>"tblNewTable"

>This does the job, but for unknown reasons, it leaves a link applied to the
>copied table and the link it reveals is to the very back end db in which it
>resides!   When I try to modify the table FROM THE BACK END, Access tells
>me it's linked and cannot be modified!   I am baffled by this behavior.

>Is it possible that I need to replace "C:\AppDir\BackEnd.mdb", with an
>object variable that points to the back end open in a workspace?

>If I can locate some code that will just copy the table and leave it
>unlinked in the back end, I can then do anything to it.

>>On Tue, 09 Jul 2002 09:55:34 -0500, Marshall Barton

>>>This is too large a question for a detailed answer.  See
>>>below for some suggestions.

>>>>==================================================================
>>>>I have a front end and back end data base app.

>>>>Using vba code behind a form in the FRONT-end, I want to:

>>>>1. Open the back end, copy a table it contains and have the copy reside,
>>>>UNLINKED, in the back-end database.

>>>Use OpenDatabase to create a database object for the Back
>>>End db.  Note: the path to the BE db is part of the Connect
>>>property of any linked table in the FE db.

>>>>2. Open the newly created table and change the Primary Key column from an
>>>>AutoNumber field type to a Number, Long type with the PrimaryKey left in
>>>>place.

>>>Create the new table and fields using the CreateTableDef and
>>>CreateField methods.  Alternatively, and probably easier, is
>>>to Execute a Create Table query.

>>>After the table and fields are established, Execute an
>>>Insert Into query to copy the data.

>>>>3. Link the newly created table to the front-end database.

>>>After all of the above is done, then create a new TableDef
>>>in the FE db and set its Connect property to link it.

>>Thanks Marshall.

>>This will work.  I was hoping for a way to avoid all that code.  All I need
>>to do is open the back end db and then COPY an existing table and have it
>>reside UNLINKED in the back end. I have tried the following code:

>>DoCmd.CopyObject "C:\AppDir\BackEnd.mdb", "tblExistingTable", acTable,
>>"tblNewTable"

>>This does the job, but for unknown reasons, it leaves a link applied to the
>>copied table and the link it reveals is to the very back end db in which it
>>resides!   When I try to modify the table FROM THE BACK END, Access tells
>>me it's linked and cannot be modified!   I am baffled by this behavior.

>>Is it possible that I need to replace "C:\AppDir\BackEnd.mdb", with an
>>object variable that points to the back end open in a workspace?

>>If I can locate some code that will just copy the table and leave it
>>unlinked in the back end, I can then do anything to it.

>Yes, you could do anything to it, but what you would need to
>do uses the same kind of operations, but is more involved
>than what I suggested.

>Anyway, you can copy the table using SQL using an IN clause.
>(AFAIK, CopyObject only works in the current db.)

>db.Execute "SELECT * INTO tablename IN ""path""" _
>            & " FROM tablename IN ""path""", dbFailOnError

>But I think you're going the long way around trying to
>change the PK field to a long type after copying instead of
>creating the table with the final field type and then
>appending the records.

> >This is too large a question for a detailed answer.  See
> >below for some suggestions.

> >>==================================================================
> >>I have a front end and back end data base app.

> >>Using vba code behind a form in the FRONT-end, I want to:

> >>1. Open the back end, copy a table it contains and have the copy reside,
> >>UNLINKED, in the back-end database.

> >Use OpenDatabase to create a database object for the Back
> >End db.  Note: the path to the BE db is part of the Connect
> >property of any linked table in the FE db.

> >>2. Open the newly created table and change the Primary Key column from
an
> >>AutoNumber field type to a Number, Long type with the PrimaryKey left in
> >>place.

> >Create the new table and fields using the CreateTableDef and
> >CreateField methods.  Alternatively, and probably easier, is
> >to Execute a Create Table query.

> >After the table and fields are established, Execute an
> >Insert Into query to copy the data.

> >>3. Link the newly created table to the front-end database.

> >After all of the above is done, then create a new TableDef
> >in the FE db and set its Connect property to link it.

> Thanks Marshall.

> This will work.  I was hoping for a way to avoid all that code.  All I
need
> to do is open the back end db and then COPY an existing table and have it
> reside UNLINKED in the back end. I have tried the following code:

> DoCmd.CopyObject "C:\AppDir\BackEnd.mdb", "tblExistingTable", acTable,
> "tblNewTable"

> This does the job, but for unknown reasons, it leaves a link applied to
the
> copied table and the link it reveals is to the very back end db in which
it
> resides!   When I try to modify the table FROM THE BACK END, Access tells
> me it's linked and cannot be modified!   I am baffled by this behavior.

> Is it possible that I need to replace "C:\AppDir\BackEnd.mdb", with an
> object variable that points to the back end open in a workspace?

> If I can locate some code that will just copy the table and leave it
> unlinked in the back end, I can then do anything to it.

> Any further suggestions?

>I prepared a long and involved answer to your question last night and my PC
>froze on me before I could send it :(

>Anyway, Marsh is absolutely right.  The problems here are:

>1. CopyObject and TransferDatabase only work for source objects in
>CurrentDb.

>2. If you CopyObject or TransferDatabase a *linked* table from CurrentDb
>(your front-end) to another DB (your back-end), what you get is just that -
>a *linked* table, with a connection to the real table in the same DB. (This
>is the behaviour you have already noted.)

>3. You cannot change the data type of a field once it is created.  You need
>to (a) create a new field with a temporary name, (b) run an update query to
>copy the data from the old field to the new one, (c) delete the old field
>and rename the new one, (d) recreate any indexes.

>4. Even a make table (Select into) query will not replicate indexes, so this
>would have to be done manually.

>So, the steps are:

>1. Create an empty table in the back-end

>2. Run an append (Insert into) query to populate it.

>3. Link the new table.

>Steps 2  and 3 are straightforward.  For step 1, you can either create the
>table from scratch (CreateTableDef) and then enumerate all the fields and
>indexes in the source table, creating fields with the same names and
>properties in the new table (changing the Autonumber field of course!).
>Alternatively, if this operation is always going to be performed on the same
>table, create an empty "boilerplate" table in your frontend with all the
>required field and index properties, and then copy it to the backend with
>CopyObject.