On Click Event

[jo15765]

I have a button on my form, and I want the On Click Event () to take the form, table, and query in my current db and export them (completely removing from current db) into a different database.

What would be the VB code needed to make this happen?

 

[spikepl]

You can just as well fess-up and tell what the purpose of this highly imaginative undertaking is

 

[VilaRestal]

You want to copy and remove the form that the button is in?

 

[jo15765]

Sorry, no. On this form it is a generic form, and on it, I input the product specific code into a textbox and then click a few buttons to run some queries. Those queries will then generate some reports, I want to then push my "Done" button and have that "Done" button take all forms/queries/tables associated with the product code that was input on my generic form and move them into a separate database.

Does that make more sense?

 

[Thales750]

Why not just have a duplicate database and then just transfer the data?

 

[VilaRestal]

you don't have tables, forms and queries associated with a specific record surely

 

[jo15765]

There are checkboxes on the form, and depending on what checkbox is checked and what button is pushed, there will be a form/query/table created within the db. I am wanting so that once all work has been completed on the input product code, you can press the "Done" button, and it will export everything that was created during the process to a diff db.

Thales 750 --- I think you are understanding, I have a duplicate db. I am trying to prevent my FE db from getting cluttered, by once the "Done" button is pressed, transferring all of the forms/tables/quereies, but I was trying to get the VB code that would automatically do it, as opposed to having to export all the data manually.

 

[VilaRestal]

But forms, tables and queries aren't 'data' they are structure/design.
All you need to transfer is a record from one table to another

 

[jo15765]

I am wanting to keep the structure/design of those forms/tables/queries, but just have them transfer to a different db. I know I can manually do it, I know how to do it that way, I just thought there was some VB code that could automate the process. Am I wrong in assuming this?

 

[VilaRestal]

The point is, changing the design of any object (table, query, form) in code, especially in response to a particular record, is a really bad idea. Mainly because that makes the database uncompilable and unusable on runtime access. (But also because it smacks of being very unnormalised (especially in the case of changing tables.)

Table design should have all the fields necessary for any possible requirement for any record.
Forms too should have everything needed for any possible record in their recordset and just hide anything not wanted for a particular record
Queries sometimes are designed at run time and need to be stored for later use but just save the SQL of the query in a table.

The 'archive' database would have exactly the same design and you would transfer records from the 'live' to the archive (corresponding tables)

Or perhaps better you would just copy the whole database and then delete the records you don't want.

 

[jo15765]

The queries/forms/tables are solely dependant upon the product code that is input into the textbox, once all of the other checkboxes have been checked and updated, there is no longer a use for these items. It's almost like the database is only a storage location. There is no actual data within any of these files, just templates that are automatically modified to be the product code that was entered. I am wanting to auto export them, so that if by chance they were ever needed to be reviewed then they are still avaliable to review. They are not dependent upon anything else in the main db, or linked to anything else in the main db.

Is there no VB code that can be written for an onclick event that would auto export these items?

 

[VilaRestal]

But I can't understand: If you have code that will 'construct' a table, query and form based on the value of a textbox (but no data is stored so why it would need a table and query I can't imagine when variables would do) and checkboxes are ticked but their values aren't stored but actually you'd like them to be in another database:

Why not store the results in a table and export the records. Tables, Queries and Forms aren't files. You're going to have an horrendously bloated database if you use their structure as a way of storing data. That's what tables are for.

Store what the user does for the product code in a permanent table. Whatever code was used to construct the form (and table and query if really need be) can be in the archive database and can reconstruct those from the same data.

But in answer to your question, I'm not aware of a way of importing objects into remote database via VBA.

 

[jo15765]

There is one set of code (this code) that actually builds the templates (the mappings).

There is another set of code that runs at the end of the cycle that actually takes the data from the table and fills in the mappings.

Then from there reports are generated etc. etc.

It is just done in two seperate steps. Step 1, build the template, Step 2, export the data.

I am working on Step 1, and just trying to figure out a way to automatically export the templates, as opposed to having to manually do it. Trying to cut down on the manual intervention portion.

 

[boblarson]

This strikes me as a kludge for a database that hasn't been designed well and/or hasn't been properly split so that the frontend could simply be replaced. But the fact that this not only includes tables (of which in a properly designed and normalized database it is a RARE thing to have to add tables) but it also is putting the forms and reports in the same file which means that it isn't properly split.

So, pardon me for saying this but this is not good. In fact it is worse than not good, it is scary.

 

[jo15765]

No it is a individual form, individual report, and individual query. All dependent upon what the product code is. And then once the "Done" button is pressed, use VBA to export all information specific to that product code.

Is it possible to write VBA to transfer forms and or queries via VB code in access?

 

[VilaRestal]

I agree - scary is the word.
You're treating Forms and Tables like files on a disk and they are not:
Firstly, there's no folders so every form has to have a unique name and every table does etc.
Secondly, there's a limit to how many objects (tables, queries, forms, reports) can be in a database: 32,768. If each 'file' has a table, query and form then you can only squeeze 10,000 files into the database. And you can bet the database will become horribly slow to open and very prone to corrupting and ridiculously bloated long before then.
Thirdly, and far more restrictively, there can only be 1,000 modules in a database (including any forms with modules). By the sounds of it each of your 'files' includes a form with a module. So that's a limit of 1,000 files, which is not very good. And again a database with 1,000 forms is not going to be quick to open and prone to corruption and terrible bloating.
Don't do it! It would be a nightmare.
If you must create files for the records then do just that - create files in a folder - word documents or spreadsheets or even just text files.
Access does provide tables for storing data though. You could store your 'files' as records in a table.

 

[jo15765]

I agree, it is completely scary. But that is the way it is, status quo henceforth I am working towards a temporary "fix" as I begin to completely redesign the process. I know what I need to do to not have this happen, but can anyone offer a solution or a work-around to meet what I started this thread for?

 

[VilaRestal]

OK, this should do it:
http://www.blueclaw-db.com/transferdatabase-docmd.htm

 

[jo15765]

VilaRestal --- just quick glance that site looks like it covers what I am attempting to accomplish for the time being.

All, thanks for your help, and thanks for ideas as well on how to properly set up the "new" db.

 

[VilaRestal]

You're welcome. Best of luck with it.
It will become unbearable eventually so replacing it with a more 'traditional' design will be doing yourself (and anyone else who may have to manage it) a big favour.