A2k: Controlling a back-end MDB from its FRONT-end MDB
Author |
Message |
Tot #1 / 7
|
 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 |
|
 |
Marshall Barto #2 / 7
|
 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 |
|
 |
Tot #3 / 7
|
 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 |
|
 |
Marshall Barto #4 / 7
|
 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 |
|
 |
Tot #5 / 7
|
 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 |
|
 |
Graham Manden #6 / 7
|
 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 |
|
 |
Tot #7 / 7
|
 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 |
|
|
|