Excel VBA - SQL Invalid Object Name

Question

_{Friday, April 25, 2014 8:34 PM}

I'm running a small piece of SQL code within Excel VBA and am getting "Invalid object name 'LoanSummary'" error.  I know for a fact that LoanSummary is a valid name of a table within the database.  What could be wrong???  I've written similar code for different tables and no problem.  I've tried adding MyDB.dbo to the table name, but no luck. Any thoughts would be greatly appreciated.

BELOW IS MY CODE:

Sub GetDataTest()

'Declare variables'
Set objMyConn = New ADODB.Connection
Set objMyRecordset = New ADODB.Recordset
Dim strSQL As String

MyLoan = 123456

'Open Connection'
objMyConn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=FMC\reports;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=SECONDARYTS;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=MyDB"
objMyConn.Open

'Set and Excecute SQL Command'
strSQL = "SELECT LoanSummary.[LoanNumber] FROM LoanSummary WHERE LoanSummary.[LoanNumber]=LTRIM(STR(" & MyLoan & "));"

'Open Recordset'
Set objMyRecordset.ActiveConnection = objMyConn
objMyRecordset.Open strSQL   '< ERROR HERE!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

'if EOF = true, then the loan wasn't found
If objMyRecordset.EOF = True Then
    objMyConn.Close
    Set objMyRecordset = Nothing
    Set objMyConn = Nothing
    Exit Sub
End If
'Copy Data to Excel'
ActiveSheet.Range("A1").CopyFromRecordset (objMyRecordset)

objMyConn.Close
Set objMyRecordset = Nothing
Set objMyConn = Nothing

End Sub

All replies (3)

_{Monday, April 28, 2014 4:24 PM ✅Answered}

Got it!  My SQL command string required a "EMDBUSER." before the table name.

_{Monday, April 28, 2014 3:08 AM}

Hi Kurgan,

Base on the description, you got an error when you retrieve the data from database. I suggest you add a break point at the error line to see the real SQL string. Does this issue occur when you query it in SQL database directly?

Or you can modify the table name to see whether this issue occur. If yes, I suggest you post it to SQL Server DataBase Engine or Transact-SQL to get more effective response.

Best regards

Fei

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

_{Monday, April 28, 2014 4:03 PM}

Thanks for the reply, Fei.  Unfortunately, I don't have any SQL Server tools to test this.  I'm confined to write the code in Excel for now.  I know I have proper access to the server because I'm able to query all the tables within it.  I do, however, still get that error when I query on other tables within the server.  I'm fairly certain my SQL string is ok because I've used the same string with tables on other servers.