Microsoft Access: Copy Control/Object from one form to another programmatically

I am using Microsoft Access 2010 on a Windows 7 OS.  I would like to programmatically copy an object, specifically and unbound object frame containing a Graph, from one form to another.

The reason I would like to do this is because over time the Graph becomes corrupted and swells in size.  I would like to delete it and copy an uncorrupted Graph from a form that isn't used.  This is an application that is distributed out to lots of users.  I would like this Graph replacement to happen each time the user opens up the application, or at the User's choosing by pressing the appropriate button.

I see where I can Create and Delete Objects and Controls, but I need to do a Copy.

Any ideas would be much appreciated.
koughdurAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
The easiest method would be to have a master of the complete form. Then, at launch of the application, delete the operational form and copy the master form assigning it the name of the operational form.

/gustav
0
Dale FyeCommented:
another useful method would be to simply replace the users copy of the FrontEnd with a version that sits on your network, every time they attempt to launch the application.  You could do this with a batch file or a launcher app.

This ensures your users get the latest version of the application every time they start it up, and ensures they always start out with a clean graph.
0
BitsqueezerCommented:
Hi,

you should keep in mind that operations like this needs a full version of Access. If the users have only the Runtime version then design changes in objects are not possible so any code like this would not work.

In opposite to that the solution which Dale has mentioned above is possible with the Runtime version.

Cheers,

Christian
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PatHartmanCommented:
Even if you don't distribute an .accde/.accdr right now (or use the runtime), you might want to be able to.  Therefore, making design changes in VBA is poor practice since it limits your options.  Dale's suggestion to have the users open a fresh copy each time will solve your problem.

This is the .bat file that I use.  The shortcut on the user's desktop points to this batch file on the server.  The batch file
1. Makes a directory on the local PC.  This allows the .bat file to be used by a new user.
2. Deletes the current local copy
3. Copies the server version (always use UNC notation so you don't have to rely on consistent drive mapping)
4.  Runs the copied file

Steps 1 and 2 can error out but the batch file ignores the errors and continues.
md c:\ClientMgt
del c:\ClientMgt\AOAClientManagement.accdr
copy \\newfiscal\Afox\AFox\AccessApplications\ClientMgt\AOAClientManagement.accdr c:\ClientMgt
c:\ClientMgt\AOAClientManagement.accdr

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
koughdurAuthor Commented:
Thanks for all the useful ideas.  We have a few users that want to have direct access to some of the tables for querying purposes, so some users will always have .accdb versions of the app.

Is there a way for VBA to ask whether the user has opened a .accdb or .accde file?  Is it as simple as getting the full path of the app and checking the last character?
0
koughdurAuthor Commented:
Thanks for the great suggestions.

Currentdb.name tells me whether the current app is an accde or accdb.  That way I can allow the user to swap forms at Runtime if necessary.

Pat: Thanks especially for the BAT file.
0
PatHartmanCommented:
Rather than distributing as .accde, I recommend renaming to .accdr.  The .accdr extension tells Access to pretend to be the runtime engine.  This will prevent people from opening anything in this file in design view.  While this is not real security (anyone who knows the trick will simply rename the file), it does give the less knowledgeable at least a small hurdle to cross.

I would NEVER allow users to have design access to the production copy of the FE.  What happens if you need to distribute an update?  Your version will not have the individual user's changes and you will either need to incorporate them or the user will loose them.

If you want to allow users to create queries and reports of their own, create a special database for just that purpose.  Start by linking all the tables.  Then create queries for each table or common join.  In these queries include a dummy 1-row table and do not draw any join lines.  This will make the query a Cartesian product and will prevent updating data.  You want to avoid at all costs allowing users to accidentally or deliberately update data outside of your forms.  Once you have created the queries and "views", hide all the tables so that the user only sees the views.  Again, this is not real security since a knowledgeable user would be able to change the queries to remove the table that was making them not updatable but it will prevent accidents.  If you want to be really nice, you can create tables and forms that the users can use to log their queries and reports and present themselves a nicer interface to run things.  Give this template app to the users who want to do their own querying and reporting so you can keep them out of the main app.

If your BE is SQL Server, you can actually make this foolproof by making views instead of linking tables.  Just make the view non-updateable and link to the views rather than to the tables.
0
BitsqueezerCommented:
Hi,

maybe you got that wrong but it is not enough to deploy an accdb or accdr or accde to make the difference.
If you have VBA code in your database which wants to make changes to the database design it is in no case possible if the user has the Access runtime installed.

If the user has the full version and you deploy an accdb or a renamed accdr he can access the code and the design which is a high risk to destroy your backend data or to circumvent security settings you made in the frontend. So at least you should ALWAYS ONLY deploy the accde which only contains the compiled VBA code so it is not possible for anyone to access it (including yourself, so always keep your accdb for you). It is also not possible to change the design of objects like forms or reports.
In opposite to that it is possible to open tables and queries with an accde so you cannot secure these objects with Access. That means of course: Don't let users work with the Access full version. If a user wants to create queries he can use Excel where you also have a query editor but you can only download data and not edit it (you would need VBA programming to do that).

The better idea is, like Pat said, to use a SQL Server (or other database server) as backend where you can create views where the user has only read-only permissions so he can create his own queries based on these views but he cannot change any data. Moreover you can restrict the access to the columns which the user should see, so for example if you have a user table which contains a salary column you can restrict the access to the other columns because the view would not contain the salary column.

Next possibility would be to use the Reporting Services of SQL Server where all the data is copied to the Reporting database. The admin can prepare the queries here, all the data is read-only and copied automatically on a regular basis. The user can get the Business Intelligence Studio to create own queries based on the prepared ones and depending on his permissions. In result good-looking reports could be created, also charts etc. and also the reports can have dynamic parameters which can be changed when the report is opened. All that with a simple browser and also with the possiblity to automatically send needed reports by mail for example.

So there are a lot of better solutions than giving users access to a full version of Access and even more worse to a source version of the frontend (means: accdb).

Cheers,

Christian
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.