SQL Syntax error

[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!

 

[Douglas J. Steele]

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

 

[Lito]

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?

 

[Douglas J. Steele]

The error would appear to be related to your actual SQL statement. How about
you post it, rather than just pseuo-code?

 

[Lito]

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"

 

[Douglas J. Steele]

Sorry, I can't see what's wrong with that, assuming that the ReviewInput is
a table in the current database.

 

[Robert Morley]

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"

 

[Lito]

Thank you Rob (and Douglas too). That was exactly the problem.