[RESOLVED] Syntax error in FROM Clause

I have an Access 2003 database which has a link to table dbo_StandingSettelmentInstructionSearch

I am using ADO to access the data.

The below works fine.

Code:

---

  SQLStr = "dbo_StandingSettlementInstructionSearch where ChangeState = 9"
    Set TheConn = CurrentProject.Connection
    Set TheRS = New ADODB.Recordset
    TheRS.Open SQLStr, TheConn, adOpenKeyset, adLockPessimistic, adCmdTable

---

However when I change the SQLStr string to

Code:

---

"select oid, Country from dbo_StandingSettlementInstructionSearch where ChangeState = 9"

---

I get an error message saying

Run-Time Error '-2147217900 (80040e14)'
Syntax Error in the FROM Clause.

Can anyone offer any insight as to why this might be.

Please notem in case it might be to do with the table name length, I have
created a Query to reduce the table name but still got the same error.

Try changing the underscore (dbo_) to a period (dbo.)

did you change the command type?

Code:

---

TheRS.Open SQLStr, TheConn, adOpenKeyset, adLockPessimistic, adCmdTable

---

When you changed it to a select, it no longer becomes a table... but text..

Code:

---

TheRS.Open SQLStr, TheConn, adOpenKeyset, adLockPessimistic, adCmdText

---

-tg

Hi Gary,

The Underscore is actually a part of the Table Name

Hi techgnome,

I changed the command type to adCmdText and it worked.

Many Thanks.