Question How to convert one mdb to another differently structured mdb (1 Viewer)

Hi all,

First of all, I'm a newbie at programming in MS Access and VB but can and want to learn.

I want to write a (possibly MS Access) program that takes one (external) MS Access database, checks if the needed tables/fields are present and then converts it to another (external) MS Access database with (of course) a different structure.

How should I attack this? Should this be a form (most likely a wizard) with macros/modules/etc. behind it? How do I start (I find that the most difficult )
Its not supposed to be a fool proof program, its only used to demonstrate that the data conversion behind it works. If we can proof that, we will implement the logic in another program.

Hope anybody can help me! Thanks in advance,

GrandiJoos

When you talk about a different structure what do you really mean?

What is the object of the exercise?
Does the data in the source mdb need to migrated to the tartget mdb?
Are you taking about selective copying and pasting of table and/or fields?

More info please.

David

Hi David,

First of all, thanks for your reply.

The source database is a selection of tables and fields from an application and we need to transform this data to another structure for use in an analysis tool. So, in essence, we will be selecting data from the source database, maybe convert it in some way (split or merge fields, perform calculations etc.) and store it in another database with a predefined structure.

The source database should not be changed (although it is a copy of the real data of course) and (some of) the data needs to be transferred and converted to the other database.

The conversion itself can be done without user interaction, as long as the predefined tables and fields are present.

Hope this clarifies my problem, thanks for helping in advance!

GrandiJoos

Because the data in the source mdb is going to be read only and the target database is going to manipulate the data then why bother with the migration when you can simply create a blank database and link the desired tables and set up queries to manipulate the data accordingly. This way the data will always be accurate as it is reading the actual data and not a snapshot or the data.

If you need any extra tables in your tartget mdb then simply create them in there.

David

If it was possible to link to queries in a different database, there might be a value in creating a 'bridge' db containing linked versions of the original tables, plus queries to manipulate them into the desired format - it would keep all the maintenance of the translation process in one place.

But it isn't possible to link to queries in a different database - which would mean running some kind of update process on a scheduled basis, which is bound to go wrong sometime.

I think DCrake is on the money here - create the linked tables and queries in the target db - you could decide on a naming convention such as prefixing them all with 'dbtranslate', or something like that - to make them all easy to find.

Thanks David and Mike!

I forgot to mention that the source data is a snapshot (a certain month for instance). This data needs to be converted to a special MS Access database structure to be read by an external application for off-line analysis. This analysis is only performed on an incidental basis (a few times a year and for different source systems).

If I understand you correctly it is not possibly to create a 'database' that reads from a source database and directly fills an external destination database? As I said, once the destination database is filled it does not need to be updated. It will only be the source of further analysis.

I think you both suggest to create a macro that links to a certain database, creates the predefined structure in the current database and then performs a number of queries to fill the tables and fields.
I was thinking about a 'MS Access application' that would create a separate mdb file which stored the data. That way the conversion could be performed multiple times (as I said, for different months or from different instances of the system) and the source data, conversion code and resulting data would be clearly separated. Since the main part is database related I thought of implementing it in MS Access instead of .Net C# f.i.

To recap:
I thought: source.mdb -> logic.mdb (no storage) -> destination.mdb
You suggest: source.mdb -> logicAndData.mdb

Especially since it is only an intermediary (proof-of-concept) solution I think I will follow your macro approach.
Is a macro then the best way to fill the database? (And where can I find what the difference between a macro, module, etc. is?)

Thanks so very much David and Mike for clearing my mind (its a clouded day )

GrandiJoos

Lets talk examples:

BackEnd.MDB is the source of the live data, it contains tables only
FrontEnd.Mdb is the user interface which has links to the BackEnd.mdb This contains all objects such as forms, queries, reports, modules, etc

Analaysis.mdb is a database that contains table structures, queries and any functions that may be needed for analysis purposes.

The frontEnd.mdb has links to the Analysis.mdb but the names of these tables have a different prefix to the matching tables in the BackEnd.mdb. this enables the front end to distinguish between the two source tables.

The object of the exercise it to get data from the BackEnd.mdb into the Analysis.mdb.

So how do we do that. In the FrontEnd.mdb we create a set of delete/append/update queries that copy data from the backend table to the analysis table. These queries can be filtered by dates. Because there are both contained in the tabledefs in the front end we do not need to refer to them explicitly, only by name.

Let's start.

First we delete the contents of linked table TblAnalStats via a delete query
Next we run an append query appending records from the linked TblStats into the TblAnalStats Where Period = whatever period you designate.

We repeat this process for all the tables that have data in them that need migrating to the analysis.mdb.

We can then run a compact and repair on the analysis mdb to get rid of the bloated records. Viola a clean analysis mdb with the relevant data in it.

If we then open up the analysis.mdb and view any select queries we may have in there you will be able to see the results. Unless I am clearly of the mark I don't think that this could be done any easier.

David

I shall try to clarify further.
The MS Access source and destination databases are only intermediary databases:
(italic is beyond my control, bold is what I need to do)

ERP system --(magic)-> source.mdb --(my logic)-> destination.mdb --(existing conversion tool)-> analysis tool

I'm working (as a student assistant) in a company that performs ('one time') data analysis. Therefore, we do not have access to the originating system and (in this case) receive a MS Access database with tables and fields. There is already a tool that can convert a MS Access database with certain tables and fields to the propriety format of the analysis tool. I only need to get it in that specific format to be able to perform the next step.

In my ideal situation the 'my logic' part is neither in the source.mdb or destination.mdb file. So both source.mdb and destination.mdb should only contain tables, not queries etc. That way I can rerun the logic part with different in- and output databases.

If your scenario would be the case then your set-up would be correct, you would need to fetch up-to-date data from the source system.

My question is how can I create a 'tool' in MS Access that only 'pumps' data from one database to another while converting it. Where do I start? What do I make? A single form (which would ideally be a wizard). Where is the logic performed? In a macro behind the buttons of the form?
If I know where to start I can look through help files to get it done but starting is the most difficult for unfamiliar programming environments.

I'm really sorry if I'm still not making sense.

Thanks for your efforts so far!!!!!!!!

GrandiJoos

Ok. My earlier post talked about having a front end with two back ends.

Lets say we have done all the code that does what you want it to do. You need to be able to fire this off. Right. So our little wizard needs to perform different steps to achieve the end result.

Step 1:
Where is the location of the mdb that contains the data you want to move?
Use CommonDialog Control or FSO to address source mdb

Step 2:
Where is the location of the mdb that will be the destination
Use CommonDialog Control or FSO to address destination mdb

Step 3:
Use vba to link the tables in step 1 to the wizard mdb
Look up samples on Relinking tables

Step 4:
Use vba to link the tables in step 2 to the wizard mdb

Step 5:
Run the necessary queries to move data from 1 to 2

Step 6:
Drop connections to 1 and 2

So your wizare will have source and target text boxes and buttons
and a button that will fire off a collection of queries
using the DoCmd.OpenQuery "..."

David

Hi David,

Thanks for the general guideline, this will help me a lot!!!

Just one little question (only need to answer if so): is there a special way to create a 'wizard style' form or is it just a series of independent forms called after one another (formStep1 calls formStep2 after actions are done etc.) (I know, really newbie question )

Thanks again for the great help and effort,

GrandiJoos

PS I'm using MS Access 2007 if it helps