Query to combine tables from different databases

[jbehrne]

Hi all,

I am trying to join three tables from two different sql server 7 databases. I can connect to two of the tables (they are in the same database). However, the third table 'Chart' is in another database on the same server. Whenever I run the connection function below an error is displayed that states 'Invalid Object Name Chart'.

Does anyone have any idea on how to accomplish this using an Oledb connection?

Dim searchstring As String 'Variable to hold the search 
Dim dbconnection As String 'connection to the sql server
Dim Conn As OleDb.OleDbConnection 'connection to sql srvr
Dim objDA As OleDbDataAdapter  'data adapter for sql server
Dim objDS As New DataSet        'dataset returned from srvr

'build sql string & connection info
dbconnection = "Provider=SQLOLEDB.1;server=" & ServerName & ";packet size=4900;integrated security=SSPI;initial catalog=MSrvce;persist security info=False"

searchstring = "SELECT [Total]-[Upcharge] AS PostChrg, tblWeeklyDataImport.*, Chart.Description, PS.Internat, PS.UD, [00000] AS ProjNum FROM tblWeeklyDataImport LEFT JOIN PS ON tblWeeklyDataImport.JobName = PS.ClassCode INNER JOIN Chart ON tblWeeklyDataImport.AccountCode = Chart.AccountNumber WHERE Left(tblWeeklyDataImport.AccountCode,2) <> '" & "MO" & "' And Left(tblWeeklyDataImport.AccountCode,2) <> '" & "AR" & "'"

 Try
     Conn = New OleDbConnection(dbconnection)
     'Initialize the OleDbDataAdapter 
     objDA = New OleDbDataAdapter(searchstring, Conn)
     objDA.Fill(objDS, "tblWeeklyDataImport")

     Me.DataGridView1.DataSource = objDS.Tables(0)
     Call SetIsNullStringsToEmpty(objDS)

     'Catch all errors and execptions here
     Catch Excep As System.Exception
            MessageBox.Show(Excep.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
      objDS.Dispose()
End Try

objDS.Dispose()

End Function

If at first you don't succeed, call in an airstrike. - Murphy's Laws of Combat Operations

 

[RiverGuy]

Yes, use 3 part naming convention for Chart. Databasename.Schema.Chart