Access 2010 Extract Data From Back End DB

Hi Experts,
I have an Application with the code in the Front End and the data in a Back End DB.  i.e. NEW-DB  The tables in this database are all defined in the application.

I also have old data (Back End DB without code).  i.e. OLD-DB.  The tables in the NEW-DB and OLD-DB may have the same name but with a different structure.

I want to run code in the Front End Application to extract the data from the OLD-DB, massage it and write it to tables in the NEW-DB.

How do I access the tables in the OLD-DB?  What is the syntax for Access VB?

Bob C.
Bob CollisonSystem ArchitectAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

I would link to the old db.  Then you can use queries that select data from the old tables, massage it, and append it to the new tables.  You can delete the links when the process is done.
Bob CollisonSystem ArchitectAuthor Commented:
Hi Pat,
The question is that without the OLD-DB tables being uniquely defined in the Application Front End (since they have the same name but different structure in the NEW-DB), how do I programmatically 'read' the tables in the OLD-DB.

A sample of my current syntax for reading a dataset is below.  Is there something similar for reading a database that is not defined in the application.

Dim PARM_PERIOD_KEY_nnn As String
Dim DB_nnn As Database
Set DB_nnn = CurrentDb
Dim RS_00_PERIOD_MSTR_nnn As DAO.Recordset
Dim CMD_00_PERIOD_MSTR_nnn As String
                           "FROM 00_PERIOD_MSTR " & _
                           "WHERE PERIOD_KEY = " & Chr$(34) & PARM_PERIOD_KEY_nnn & Chr$(34)
On Error Resume Next
Set RS_00_PERIOD_MSTR_nnn = DB_nnn.OpenRecordset([CMD_00_PERIOD_MSTR_nnn], dbOpenDynaset)
End If
Set RS_00_PERIOD_MSTR_nnn = Nothing
Set DB_nnn = Nothing

Bob C.
Why do they have to have names that duplicate the new names?  Just because the names are the same in the BE doesn't mean that the names have to be the same in the FE.  When you link to the old BE, Access will suffix the names with a number to avoid duplication.  You can rename them to the more obvious - Old_tblA, Old_tblB, etc.
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Bob CollisonSystem ArchitectAuthor Commented:
Hi Pat,

The reason they are the same is that they are just different versions of the structures.  This process is part of the implementation of a new version of the Application.  An example of a Table Name is MEMBER_MSTR.  I don't want to have to change all the code each time there is a new release just to have unique names.

If I don't understand your second sentence regarding re-naming.

I had a feeling that there would be a method to open more than one physical data base.  This is an area that I don't know much about.  I believe that the database opened in the code above is implied to be the 'CurrentProject.Connection'.  If this is the case, is there a way to be explicit thereby making it possible to access more than one physical database at a time?

Bob C.
We are talking about a conversion, not an on-going process.  Once the data from the Old tables is ported to the new tables, the old tables are deleted since they are no longer needed.  They only need to both be attached during the conversion itself.  When you link a table in Access, the name of the table in the source database may or may not be the same as its name in the target database.  Linking doesn't change any column names, it simply allows you to change the table name.  Changing the table name allows you to reference both the old and the new tables during the conversion process and doesn't have anything to do with the normal operation of the application.  When you are working with a query, each column name is prefixed by its table name so even though the column names were identical, the references were unique because one was tblA.ClientName and the other was Old_tblA.ClientName.  As you can see, this doesn't change from v1-v2 to v2-v3.  Both the source and target fields have the same names.

If you have to convert v2 to v3, then the v2 tables become the "old" tables when you link them, but your conversion code would be different even if the column names remained the same since the code you wrote worked for converting v1 to v2 and now you have to go from v2 to v3 so worrying about table names at this point is moot since you would have to change the queries and code anyway.  But even though the point is moot, there is still no additional change required.  For the v2 to v3 conversion the v3 tables inherit the existing names and when you link to the old BE, the v2 tables now become the "old" versions with the Old_ suffix.

I have one application that is sold to the public so I had to solve this problem.  My choice was to create a separate database that handled the conversion.  So I had a v1-v2 database and a v2-v3 database, etc.  In my case, I made some decisions early on and they were that we would never delete client data and that translated to never deleting tables or columns.  So the conversion app linked to the production database of the correct version and made a backup as its first step (you can't trust this to the client).  Then it ran a series of DDL queries that modified the existing tables to add columns, populate them with defaults and then go back and update the table again to make the column required.  It added/removed indexes.  It added new empty tables.  It added/removed relationships.  The upshot of this was that the conversion code never actually touched the client data.  All it did was change the structure of the BE from v1 to v2.  It then updated the version table so the application when it opened could ensure that if you were running a v2 FE that it was linked to a v2 BE.
Bob CollisonSystem ArchitectAuthor Commented:
Hi Pat,

I sort of understand what you are saying but I really don't have it in my head.

Do I have to have the Old and New Table Structure in the Front End DB as two separate tables.  Must they match to the structure in two Back End DBs?  If not, I don't understand how to reference each one independently.

I have also been trying to research this and have come across the following syntax which I believe could be used to open a reference to a Database other than 'CurrentDB'.  i.e a second data base.  Do you know if this would work?

Set database = workspace.OpenDatabase (dbname, options, read-only, connect)

Bob C.
Gustav BrockCIOCommented:
Using a separate Workspace will force you to run everything in code. It can be done, but you would have to set up the manual process as described by Pat first to find out the details of the code.

Thus, of course, the coding renders meaningless except for the sport.

Link all tables and create an append query for each table to transfer.

Your coding will be reduced to write loop that executes all the queries one by one.

Bob CollisonSystem ArchitectAuthor Commented:
Hi Gustav,

Thanks for your comments.

I prefer to use VBA code rather than Access Queries as I find it much easier to transform the data when moving it from one version of the database to another.

I don't understand what you are referring to by the following:
"It can be done, but you would have to set up the manual process as described by Pat first to find out the details of the code.

Bob C.
Gustav BrockCIOCommented:
I prefer code too, but there is so much trial and error in this, that you will need to build queries to get it right - speaking of heavy experience.

When having done that, your queries are ready, and it doesn't make sense to transform these to code - except for the fun, and I guess you have better things to do.

Queries are always faster than code loops so if you can do it in a query, that will be your best option.

I have to admit to being confused by your confusion.  You have an application that you have created that uses linked tables so you know how they work.  Have you tried my suggestion?  Take a new db and link to the tables in the new BE and then link to the tables in the old BE.  Did Access suffix the "old" names for you?  Create a query that uses two versions of the same table.  Access won't have any problem with the "duplicate" column names.

As long as you are only changing data, then using linked tables works just fine.    If you are also making schema changes, then it's a little more difficult.  When you write the code (I only use DAO since that is native to Access and therefore technically more efficient), you will most likely need two database objects.  One that is set to CurrentDB() and one that is set to the name of the database you want to affect.  I'll search for some code that runs in one database and modifies tables in a different database.

You now want to upgrade that application to use a modified schema.  My first suggestion is
1. Don't try to do this in the line of business FE.  Create a separate application that ONLY performs the conversion.
Bob CollisonSystem ArchitectAuthor Commented:
Hi Pat,
I haven't tried you solution yet.  I too have standardized on DAO Code as this was suggested previously by EE Staff.

I do know how to link tables both via the Link Table function from the Menu Bar and via code but I don't how to use the Link Table function from the Menu Bar to link  tables to a second BE while keeping the links to the first.  I think I now understand that this can only be done through code.  Am I correct?

One of the reasons for the way I am approaching this is because of schema changes.  Yes, I was planning on having two BE DB Objects (one for the Current Schema and a second for the New Schema).  I was also planning on using a 'Conversion FE' separate from the 'Business FE' that will reference the two BE DBs.  I will create a copy of the Business BE prior to and to be used by the conversion.  When the conversion is complete I would backup the original Business BE, replace it with the Converted BE, install the New Business FE and link the New Business FE to the New Business (converted data) BE.  I use InstallShield for installation.

The thing I am missing most to do this is the syntax for referencing the a second BE Database.  i.e.  What you are going to try and find.

PS:  I am retired and was a System Architect with Canada's largest bank.  I have also been a DBA on various Main Frames DBMSs with various companies in various industries.

Bob C.
No.  I think you are thinking of the linked tables manager.  That tool refreshes or moves EXISTING links.  It does not link additional tables.  You would use Get External Data to pull in the tables from the other database.  So to start with a clean, empty database,
1. Get External Data, choose the tables from dbA
2. Get External Data a second time, choose the tables from dbB

Now you have tables from separate BE's and can use them to join and update each other.

Note:  The linked tables manager is not able to refresh the links from different sources at once.  So, if you need to refresh dbA tables, you need to select ONLY dbA tables from the LTM.  Then if you need to refresh dbB tables, select ONLY dbB tables from the LTM.  If you inadvertently select tables from multiple BE's, the LTM will prompt for a new location for every single table you chose.
Bob CollisonSystem ArchitectAuthor Commented:
Hi Pat,

Thanks for the information regarding the use of Get External Data which is a manual intervention solution.

I would still like to get the 'Code Solution' that you are still? researching.

Bob C.
Gustav BrockCIOCommented:
It is nothing more than open the two databases:

    Dim db1 As DAO.Database
    Dim db2 As DAO.Database
    Set db1 = OpenDatabase("d:\path\db1.accdb")
    Set db2 = OpenDatabase("d:\path\db2.accdb")

then open recordsets as you need, and create functions that read and append records between recordsets.

Linking the tables is a one-time event.  Once they are linked, you would refresh the links with code.   Although in practice you probably want the user to choose both the source and target BE databases.  Hard-coding paths is always a poor solution especially if the app runs in an environment over which you have no control.  Never will a shared BE be located on the client's C: drive.  It will always be on a network drive and in many companies, mapping specific drives can cause a problem so you really need flexibility in locating the BE's you want to work with.

As I said earlier, I do this in "shrink wrapped" apps with no manual intervention except to start the process and prompt for the source/target databases.

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
Bob CollisonSystem ArchitectAuthor Commented:
Thanks Experts for all of the insight / suggestions / solutions.  My primary objective was to 1) Determine a Strategy, 2) Obtain the syntax.  I now have both.
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.