[RESOLVED] [2.0] Attaching to several databases

[Beast777]

I have a database that has all personnel records. I have another database that has records that relate to those personnel and in access I would add links to the databases and then use a sql join to select the data.

In VS2005, how do I accomplish the same thing? If I drop a sqldataadapter on the formm, it can only connect to one database. Do I need to populate a dataset with one table from one database and then then do the same from the other database and then do some database relations and then access the dataset? IF so, does anyone have any examples of such a monster?

[wild_bill]

You may not need to create seperate connections, check out linked servers:
http://www.databasejournal.com/featu...le.php/3085211

[Beast777]

Actually its the same server, just different databases.

[corwin_ranger]

If all the databases are on one server (I'm assuming SQL here), then you can refer to them inside your SQL statement by using the fully qualified name. For example, if I have an SQL server that has DB1, DB2 and DB3 I could do something like the following:

SELECT

1.Col1, 1.Col2, 2.Col2, 3.Col5

FROM DB1.dbo.TableX AS 1

JOIN DB2.dbo.TableY AS 2 on
1.Col1 = 2.Col1

JOIN DB3.dbo.TableZ AS 3 on
1.Col1 = 3.Col1

WHERE....

If you're talking about multiple platforms (SQL, Access, etc...), then you should examine WildBill's suggestion about Linked Servers. You can use the same basic syntax as I've shown, only you'll need to add the linked server to the table's fully qualified name. In addition you may have to be concerned about performance depending on the nature of your queries.

I might also recommend using Stored Procedures to contain all your SQL logic, which will, among other things, keep you from having to build strings of complex inline SQL code.

Cheers,

Steve

[Beast777]

Here's an example of one of my queries:

SELECT PersData.Rank, PersData.Service_Number, PersData.Last_Name, PersData.Initials, Doc_Forms.Pass_to, Doc_Forms.Date, Doc_Forms.[728#]
FROM Doc_Forms INNER JOIN PersData ON Doc_Forms.Service_Number = PersData.Service_Number
WHERE (((Doc_Forms.Completed)=0) AND ((Doc_Forms.Pers_File)>0))
ORDER BY PersData.Last_Name, Doc_Forms.Date
WITH OWNERACCESS OPTION;

My connection is to either the PersData or the Doc_forms database. IF I drop a sqldataadapter on the form, I can only choose one of them. Then when I enter the select above, and lets say I chose the Doc_Forms as my connectionstring, then it says it does not know what Persdata is. Where am I going wrong?

[Beast777]

Actually I see what I needed to do.



SELECT DB2.Rank, DB2.Service_Number, DB2.Last_Name, DB2.Initials, DB1.Pass_to, DB1.Date, DB1.[728#]
FROM DocCtrl.dbo.Doc_Forms DB1 INNER JOIN TrainingManagementDB.dbo.PersData DB2 ON DB1.Service_Number = DB2.Service_Number
WHERE (((DB1.Completed)=0) AND ((DB1.Pers_File)>0))
ORDER BY DB2.Last_Name, DB1.Date


Thanks

[corwin_ranger]

Are these two different databases or two different tables in the same database? From the appearance of your query, it looks more like you are referencing two different tables in the same database.

A good test would be to copy your query into SQL Query Analyzer (SQL 2000) or SQL Management Studio (SQL 2005) and run it directly against your database without any VB code to make it run. That will indicate to you if the query is valid to begin with.

When you reference two tables in the same database in a query the syntax for a particular field usually looks like this:

Owner.TableName.FieldName OR
TableName.FieldName (since Owner is optional).

If you have tables in different databases, the syntax is usually:

DatabaseName.Owner.TableName.FieldName OR
DatabaseName.TableName.FieldName (again Owner is optional, though I recommend including it).

Hope this helps,

Steve