Error-2147217900, Syntax error in FROM clause 
Author Message
 Error-2147217900, Syntax error in FROM clause

Hi
I wonder what is wrong with the code below that works with Access 97 but
gives the error -2147217900 on .Open  method when Access 2000 used?

----------------------------------------------------------------------------
---------
  Dim rsTemp As ADODB.Recordset

  Set rsTemp = New ADODB.Recordset
  Set objTransCodes = New TransCodes
  With rsTemp
    .Open "Transaction", cnDB, adOpenForwardOnly, adLockReadOnly, adCmdTable
    ' dumping all records from Transaction tbl
    Do While Not .EOF
       objTransCodes.Add !Trans_Transno, ..., ........
       .MoveNext
    Loop
    .Close
  End With
  Set rsTemp = Nothing
----------------------------------------------------------------------------
--------
To open connection I am using OLEDB connection string like

 strProvider= "PROVIDER=Microsoft.Jet.OLEDB.4.0;  "  for Access 2000
 strDSN  = "C:\BlaBla\xxxDB.mdb"

 Public cnDBapp As ADODB.Connection
 Set cnDB = New ADODB.Connection
  With cnDB
    .Provider = strProvider
    .Mode = adModeShareDenyNone
    .Open "Data Source=" & strDSN     ' <<<
  End With

without any problem
----------------------------------------------------------------------------
--------
Any suggestions??
TIA
Andrew



Mon, 05 Sep 2005 14:02:10 GMT  
 Error-2147217900, Syntax error in FROM clause
Oops!
I wanted to say that it works with Access97 having
PROVIDER=Microsoft.Jet.OLEDB.3.51;
but it does not work when I am using PROVIDER=Microsoft.Jet.OLEDB.4.0; with
Access 2000
or Access 97.
TIA


Quote:
> Hi
> I wonder what is wrong with the code below that works with Access 97 but
> gives the error -2147217900 on .Open  method when Access 2000 used?

> --------------------------------------------------------------------------
--
> ---------
>   Dim rsTemp As ADODB.Recordset

>   Set rsTemp = New ADODB.Recordset
>   Set objTransCodes = New TransCodes
>   With rsTemp
>     .Open "Transaction", cnDB, adOpenForwardOnly, adLockReadOnly,
adCmdTable
>     ' dumping all records from Transaction tbl
>     Do While Not .EOF
>        objTransCodes.Add !Trans_Transno, ..., ........
>        .MoveNext
>     Loop
>     .Close
>   End With
>   Set rsTemp = Nothing
> --------------------------------------------------------------------------
--
> --------
> To open connection I am using OLEDB connection string like

>  strProvider= "PROVIDER=Microsoft.Jet.OLEDB.4.0;  "  for Access 2000
>  strDSN  = "C:\BlaBla\xxxDB.mdb"

>  Public cnDBapp As ADODB.Connection
>  Set cnDB = New ADODB.Connection
>   With cnDB
>     .Provider = strProvider
>     .Mode = adModeShareDenyNone
>     .Open "Data Source=" & strDSN     ' <<<
>   End With

> without any problem
> --------------------------------------------------------------------------
--
> --------
> Any suggestions??
> TIA
> Andrew



Mon, 05 Sep 2005 14:22:54 GMT  
 Error-2147217900, Syntax error in FROM clause
I think I've got the answer.
It looks like 'transaction' is a reserved word .
The table should not be named as Transaction.
Thanks
Andrew


Quote:
> Hi
> I wonder what is wrong with the code below that works with Access 97 but
> gives the error -2147217900 on .Open  method when Access 2000 used?

> --------------------------------------------------------------------------
--
> ---------
>   Dim rsTemp As ADODB.Recordset

>   Set rsTemp = New ADODB.Recordset
>   Set objTransCodes = New TransCodes
>   With rsTemp
>     .Open "Transaction", cnDB, adOpenForwardOnly, adLockReadOnly,
adCmdTable
>     ' dumping all records from Transaction tbl
>     Do While Not .EOF
>        objTransCodes.Add !Trans_Transno, ..., ........
>        .MoveNext
>     Loop
>     .Close
>   End With
>   Set rsTemp = Nothing
> --------------------------------------------------------------------------
--
> --------
> To open connection I am using OLEDB connection string like

>  strProvider= "PROVIDER=Microsoft.Jet.OLEDB.4.0;  "  for Access 2000
>  strDSN  = "C:\BlaBla\xxxDB.mdb"

>  Public cnDBapp As ADODB.Connection
>  Set cnDB = New ADODB.Connection
>   With cnDB
>     .Provider = strProvider
>     .Mode = adModeShareDenyNone
>     .Open "Data Source=" & strDSN     ' <<<
>   End With

> without any problem
> --------------------------------------------------------------------------
--
> --------
> Any suggestions??
> TIA
> Andrew



Tue, 06 Sep 2005 05:28:43 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. SQL Syntax error in ORDER BY clause.(-2147217900)

2. -2147217900 Error: Syntax error in INSERT INTO statement

3. Run-time error -2147217900 (80040e14) "Syntax error in FROM clause"

4. error -2147217900 instead of error -2147217873 from SQLserver

5. Error 3131 Syntax error in FROM clause - using SQL accessing external ODBC problem

6. Error 3131 Syntax error in FROM clause - using SQL accessing external ODBC problem

7. runtime error -2147217900

8. Runtime Error 2147217900 ODBC with Access

9. 2147217900 error message

10. Run-time error -2147217900

11. Error -2147217900

12. Error -2147217900

 

 
Powered by phpBB® Forum Software