Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 161
  • Last Modified:

[Urgent] Howto use tables from different databases ?


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
marconovaro
Asked:
marconovaro
  • 3
  • 3
1 Solution
 
rdrunnerCommented:
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
 
marconovaroAuthor Commented:

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
 
rdrunnerCommented:
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
Independent Software Vendors: 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!

 
phildaleyCommented:
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
 
rdrunnerCommented:
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
 
marconovaroAuthor Commented:

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
 
marconovaroAuthor Commented:

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!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now