Linking to multiple databases in View via ODBC

[ralphnoble]

Greetings. I'm running VFPWin v6 and have properly connected to my Sybase 10 databases. I have three databases, and can bring each up into a single view. Problem is I need to either:

1. Bring all three databases up into a single view.

2. Open all three databases up into separate views, but link them so that I can query the data.

Can someone please educate me how I can do either? Thanks.

Ralph Noble
raplh_noble@hotmail.com

 

[DEDMOD]

I'm surprised nobody has answered. It sounds pretty simple to me, assuming you have no problems with the ODBC connections for any of the databases. You can, I believe create local views treating the remote views just like a local table.

Thus if you have a table 'customers in database 'contacts' and a table 'orders' in database 'sales', you can create ODBC views for the customers table and another one for the orders table and then create a view from the two of them joining the two of them on 'customer ID' assuming it's the same in both databases.

You will have to create three separate Sybase connections since you're using three different databases, but that shouldn't be a problem. Let me know how it goes.

Dave Dardinger

 

[TomasDill]

Hi!

In addition you can use the indexes to speed up the work of the last view. Open 3 views with NODATA option. Index them by INDEX command creating index tags on the key fields and fields you will use for filtering data. Then use REQUERY for these views. Then open the joined view based on these 3 views.
Vlad Grynchyshyn
vgryn@softserve.lviv.ua

http://www.softservecom.com

The professional level of programmer could be determined by level of stupidity of his/her bugs

 

[fluteplr]

That solution will work, if you have a relatively small number of records in the tables you are going to pull entirely to the client side. If you are working with say a couple of million records in the tables you are going to have a big problem.

I do not know if Sybase supports server side views that access data in multiple databases. If they do I would create a view in one database that joins the tables on the server side and use that as the basis of my VFP view.

I have used this solution very successfully in Sql Server 7 and 2000.