?
Solved

[Urgent] Howto use tables from different databases ?

Posted on 2003-03-14
7
Medium Priority
?
159 Views
Last Modified: 2010-05-01

I have the following situation. I have an existing program (which I cannot change) that stores information on an Access database table (I'll call this database the "original" database). Once a day, this program backs up the received data into a "backup" database, that has a table with identical structure to the original one.

Now, I'm been asked to implement a program that lets the user see those information (i.e. both the one on the "original" table and the one on the "backup" one).  This is because this information are events received from an HW device, and the user shall be able to see not only "today" events, but also the ones in past days.

How to do this?

The actual problem is that I need a way to do a "join" based on the backup table _and_ on some tables that resides on the original database. Is there any way to do this?

I would like to avoid creating a new table that is the union of both, since I've got many events, and it would take some time to do this...

Any suggestion on the best practice to do this?
Thanks in advance.
M.


0
Comment
Question by:marconovaro
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 11

Accepted Solution

by:
rdrunner earned 2000 total points
ID: 8136731
Hello!

You can do the following

Assuming: Same structure in backup and Main DB , Table you want is tblInfo

Goto the backup DB and goto FILE->External DATA -> Link Table

Now pick the table that holds todays information and create a link to it in the backup DB and call it tblInfo_lnk)

Now create a new view to join those 2 tabels

Select * from tblinfo
Union all
Select * from tblInfo_LNK

Save the View and access it when you want to display todays and yesterdays data simultaniously


Hope this helps
0
 
LVL 6

Author Comment

by:marconovaro
ID: 8137114

Hi rdrunner,

thanks for the fast reply.
Actually, my problem is that i cannot modify the Access database, since it's a program that is not under my control.

Is there any way to do this completely programmatically?
I mean: what I really need is a way to have a "table" (via a query, or somewhat) that allows me to use all the "events" from today and also from the past days as they where in a single table. This is  what your code acutally does, but I would like a VB code to do that.

Thanks again.
M
0
 
LVL 11

Expert Comment

by:rdrunner
ID: 8137162
Thats what you get when you do a select like this

Select * from tblinfo
Union all
Select * from tblInfo_LNK

Another option would be to create a 3rd DB and only put the 2 linked tabels in there (from main and backup).... That allows you to form that querry in your new DB....


0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Expert Comment

by:phildaley
ID: 8137236
Why not just use two database connections and recordsets? Select info required from original table in one recordset and use another recordset to get info from backup table.

You can then iterate through both recordsets, adding the data to a listview/grid or whatever to display.
0
 
LVL 11

Expert Comment

by:rdrunner
ID: 8137288
I hate itterrating ;)

Also if you do it the way i discribed you can use a control to which you can feed an ADO Recordset. So you dont need to itterate a 2nd time to display your data...

Yes, i know that i am lazy...
0
 
LVL 6

Author Comment

by:marconovaro
ID: 8137334

No, rdrunner, I like your suggestion. But my problem is I cannot modify the database to create the linked table...

:-(

phildaley: thanks for the help, but I'd need to do a join on some tables that are only on the original database.

M
0
 
LVL 6

Author Comment

by:marconovaro
ID: 8426671

Ok, at last I'll accept rdrunner's solution.
It's not exactly what I need, but it points me in the right direction.

Thanks.
M
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

741 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question