Need help with SQL statement

[sunmorgus]

I am trying to use an ADO connection to pull a couple of records from some SQL tables and populate a few fields on my form. I am getting an error in my sql statement that says "Invalid object name dbo_IMINVBIN_SQL". The query runs fine when run directly from the access, but not from code. Here is my code:

Private Sub Form_Load()
'On Error GoTo HandleErrors
 
Dim adMacolaConn As ADODB.Connection
Dim adMacolaRS As ADODB.Recordset
Dim MacolaSQL As String
 
Set adMacolaConn = New ADODB.Connection
adMacolaConn.Open "Provider=SQLOLEDB.1;" & _
                    "Persist Security Info=False;User ID=sa;Initial Catalog=data_01;Data Source=DATALUX_SQL"
 
MacolaSQL = "SELECT [dbo_IMINVLOC_SQL].[loc], [dbo_IMINVLOC_SQL].[item_no], [dbo_IMINVBIN_SQL].[bin_no], [dbo_IMITMIDX_SQL].[search_desc]" & _
            "FROM ([dbo_IMINVBIN_SQL] RIGHT JOIN dbo_IMINVLOC_SQL ON [dbo_IMINVBIN_SQL].[item_no] = [dbo_IMINVLOC_SQL].[item_no]) LEFT JOIN dbo_IMITMIDX_SQL ON [dbo_IMINVLOC_SQL].[item_no] = [dbo_IMITMIDX_SQL].[item_no]" & _
            "WHERE ((([dbo_IMINVLOC_SQL].[loc])='dlx')) AND [dbo_IMINVLOC_SQL].[item_no] = " & Me.Part_Number & ";"
 
Set adMacolaRS = New ADODB.Recordset
With adMacolaRS
    .CursorLocation = adUseServer
End With
adMacolaRS.Open MacolaSQL, adMacolaConn
 
With adMacolaRS
    Me.txtDescription.SetFocus
    Me.txtDescription.Value = Nz(.Fields("search_desc"), "")
    Me.txtLocation.SetFocus
    Me.txtLocation.Value = Nz(.Fields("loc"), "")
End With
 
err_exit:
    Exit Sub
HandleErrors:
    Resume err_exit
 
End Sub

Could someone help me figure this out?

 

[CharlesCook]

I think

FROM ([dbo_IMINVBIN_SQL] RIGHT JOIN

Should Be

FROM dbo_IMINVBIN_SQL RIGHT JOIN (

CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining

 

[sunmorgus]

That I think got me a little farther, now I get an "Incorrect syntax on near the keywork 'ON'".

 

[RoyVidar]

I think if you do a

[tt]debug.print MacolaSQL[/tt]

then check the result in the immediate pane (ctrl+g), you'll get a surprise or two - you systematically lack a space in each concatenation

[tt]"SELECT ..., [dbo_IMITMIDX_SQL].[search_desc]FROM ([dbo_IMINVBIN_SQL] RIGHT JOIN ... = [dbo_IMITMIDX_SQL].[item_no]WHERE ...[/tt]

My trick, is to always place one space at the end of each line, i e

[tt]MacolaSQL = "SELECT [dbo_IMINVLOC_SQL].[loc], [dbo_IMINVLOC_SQL].[item_no], [dbo_IMINVBIN_SQL].[bin_no], [dbo_IMITMIDX_SQL].[search_desc] " & _[/tt]

But another guess at what might be making some of the "confusement" here, is that you're using the name of linked tables (linked tables within Access) on a connection to the SQL server, where you would probably have more luck using T-SQL/SQL server syntax - just a throw together, using owner prefix and alias...

[tt]MacolaSQL = "SELECT l.loc, l.item_no, b.bin_no, m.search_desc " & _
"FROM dbo.IMINVBIN_SQL b RIGHT JOIN dbo.IMINVLOC_SQL l ON " & _
"i.item_no = l.item_no LEFT JOIN dbo_IMITMIDX_SQL m ON " & _
"i.item_no = m.item_no " & _
"WHERE l.loc='dlx' AND l.item_no = " & Me.Part_Number[/tt]

Roy-Vidar