SQL Syntax error

  • Thread starter Thread starter Lito
  • Start date Start date
L

Lito

I am new to Access and SQL. I am trying to open a recordset, initially
showing all records but eventually I will also need to apply filters in a
WHERE clause.

I am getting a "Syntax error in FROM clause" message, and can't figure out
what I am doing wrong. I'd appreciate very much any comments. Below is the
code:

Dim CurConn As New ADODB.Connection
Dim C As New ADODB.Recordset
Dim CurDB As Object
Dim sql As String

sql = "SELECT * FROM TableName"

Set CurDB = CurrentDb
Set CurConn = New ADODB.Connection

With CurConn
.Provider = "Microsoft.jet.OLEDB.4.0"
.ConnectionString = "data source= " & CurDB.NAME
.Open
End With

Set C = New ADODB.Recordset
C.CursorType = adOpenDynamic
C.LockType = adLockOptimistic
C.Open sql, CurConn, , , adCmdTable

BTW, I am using Access 2000.
Thanks!
 
Do you actually have a table named "TableName"?

Incidentally, you can replace the entire section

Set CurDB = CurrentDb
Set CurConn = New ADODB.Connection

With CurConn
.Provider = "Microsoft.jet.OLEDB.4.0"
.ConnectionString = "data source= " & CurDB.NAME
.Open
End With

by using CurrentProject.AccessConnection instead of CurConn
 
Thank you for your reply. The actual table name is InputReview. I wrote
<TableName> for the post, but should have been in angle brackets.

Is the SQL syntax error related to the code correction you suggested?
 
The error would appear to be related to your actual SQL statement. How about
you post it, rather than just pseuo-code?
 
The statement is in the sql variable, which I then use to try to open the
dataset.
Below is the actual code:

Dim C As New ADODB.Recordset
Dim CurConn As New ADODB.Connection

Dim CurDB As Object
Dim sql As String

Set CurDB = CurrentDb
Set CurConn = New ADODB.Connection

With CurConn
.Provider = "Microsoft.jet.OLEDB.4.0"
.ConnectionString = "data source= " & CurDB.NAME
.Open
End With

Set C = New ADODB.Recordset
C.CursorType = adOpenDynamic
C.LockType = adLockOptimistic

sql = "SELECT * " & _
"FROM ReviewInput"

C.Open sql, CurConn, , , adCmdTable 'Msg.: "Syntax error in FROM
clause"
 
Sorry, I can't see what's wrong with that, assuming that the ReviewInput is
a table in the current database.
 
This may be a double-post, but my connection flaked out as I was posting
earlier, and I don't see it, so here it is (possibly again)...

The problem is that you're using a SQL query with the adCmdTable option.
Either use simply "ReviewInput" as your source, or change the adCmdTable to
adCmdText.


Rob
The statement is in the sql variable, which I then use to try to open the
dataset.
Below is the actual code:

Dim C As New ADODB.Recordset
Dim CurConn As New ADODB.Connection

Dim CurDB As Object
Dim sql As String

Set CurDB = CurrentDb
Set CurConn = New ADODB.Connection

With CurConn
.Provider = "Microsoft.jet.OLEDB.4.0"
.ConnectionString = "data source= " & CurDB.NAME
.Open
End With

Set C = New ADODB.Recordset
C.CursorType = adOpenDynamic
C.LockType = adLockOptimistic

sql = "SELECT * " & _
"FROM ReviewInput"

C.Open sql, CurConn, , , adCmdTable 'Msg.: "Syntax error in FROM
clause"
Click to expand...
 
Back
Top