A2k: Controlling a back-end MDB from its FRONT-end MDB 
Author Message
 A2k: Controlling a back-end MDB from its FRONT-end MDB

==================================================================
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.

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.

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

Any suggestions or code snippets?

All responses appreciated.



Sat, 25 Dec 2004 08:44:40 GMT  
 A2k: Controlling a back-end MDB from its FRONT-end MDB
This is too large a question for a detailed answer.  See
below for some suggestions.

Quote:

>==================================================================
>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.

Quote:
>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.

Quote:
>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.

--
Marsh
MVP [MS Access]



Sat, 25 Dec 2004 22:55:34 GMT  
 A2k: Controlling a back-end MDB from its FRONT-end MDB
On Tue, 09 Jul 2002 09:55:34 -0500, Marshall Barton

Quote:

>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?



Sun, 26 Dec 2004 01:27:51 GMT  
 A2k: Controlling a back-end MDB from its FRONT-end MDB

Quote:

>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.

--
Marsh
MVP [MS Access]



Sun, 26 Dec 2004 04:05:47 GMT  
 A2k: Controlling a back-end MDB from its FRONT-end MDB
On Tue, 09 Jul 2002 15:05:47 -0500, Marshall Barton

Quote:


>>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.

Appending the records is not required.  I failed to mention that the goal
of this operation is to create an EMPTY copy of the source table, then link
it to the front end.  The front end will already have a form designed to
work with the new table.  This is my attempt to update the back end without
having to distribute a NEW copy of it along with an app that will transfer
all the records in ALL of the back end tables from the user's old back end
mdb.  That's WAY too sloppy and fraught with opportunities for user error.
It would be a support nightmare.

If I use your approach, will I be able to alter the new table as I've
described?



Sun, 26 Dec 2004 05:40:21 GMT  
 A2k: Controlling a back-end MDB from its FRONT-end MDB
Hi Toto

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.

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.

Please post new questions or followups to newsgroup.


Quote:
> 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.

> Any further suggestions?



Sun, 26 Dec 2004 05:46:15 GMT  
 A2k: Controlling a back-end MDB from its FRONT-end MDB
Thanks Graham! You're the man!

On Wed, 10 Jul 2002 09:46:15 +1200, "Graham Mandeno"

Quote:

>Hi Toto

>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.



Mon, 27 Dec 2004 00:24:13 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. A2K Updating Back End with new fields from Front End

2. need help updating a local front end mdb from an mdb on an ftp site

3. Compact the back-end data from the front-end

4. Corrupted back end corrputs all front ends!

5. Splitting db in front end and back end

6. Compact back-end front-end

7. Trying to Create a communicator for VB.net Front End and SQL 2000 back end

8. VB Front end dll loses connection to back end SQL server db after an hour

9. Front-End & Back-End

10. Packaging a VB front end/Access back end application

11. Access Back End, VB Front End -- Why?

12. Access Back End, VB Front End -- Why?

 

 
Powered by phpBB® Forum Software