VB, ODBC, ADO and Navision

AL9000
AL9000 Member Posts: 5
edited 2000-08-11 in Navision Financials
In a VB application using Data Environment I can access my Navision ODBC connection. When trying to access the Navision ODBC connection by programming with ADO, I am getting the error…

[Navision Software a/s][Navision Financials ODBC Driver]Expected lexical element not found: <identifier>

My Code in the Class is as follows (Standard Text is the table)

Private Function GetRecordset(strConnectString) As ADODB.Recordset
Dim strSource As String

strSource = "SELECT * FROM ''Standard Text''"

Set conData = CreateObject("ADODB.Connection")
Set rsData = CreateObject("ADODB.Recordset")

conData.CursorLocation = adUseClient
conData.ConnectionString = strConnectString
conData.Open
rsData.CursorType = adOpenForwardOnly
rsData.CursorLocation = adUseClient
rsData.LockType = adLockReadOnly
rsData.Source = strSource
rsData.ActiveConnection = conData
rsData.Open
Set GetRecordset = rsData
End Function

A Finney
CASI
Springfield IL
afinney@casi400.com
A Finney
CASI
Springfield IL
afinney@casi400.com

Comments

  • mfabian
    mfabian Member Posts: 187
    The problem (which is well known to me) has nothing to do with your VB code but with the setup of the ODBC-Datasource:
    the "Expected lexical element not found: " referres to fieldnames in Navision which contain a Dot. Therefore make sure that if you set-up the DSN as:
    Start --> settings --> control panel --> ODBC32 --> Add
    You enter the ODBC Setup for the datasource. You will find an Option-Button there which will open the "ODBC Options" dialogue.
    In Field "Identifiers" you should select "All Characters" instead of the default "all except dots".

    This should do the trick.

    By the way, if you are in the Options dialogue anyway change
    "Option Field Type" to Integer


    Marcus

    Marcus Fabian
    m.fabian@thenet.ch
    +41 79 439 78 72
    With best regards from Switzerland

    Marcus Fabian
  • AL9000
    AL9000 Member Posts: 5
    The whole error message did not get posted originally
    [Navision Software a/s][Navision Financials ODBC Driver]Expected lexical element not found: <identifier>

    I did has you suggested and I still have the problem.

    When I change the source string from
    strSource = "SELECT * FROM ''Standard Text''"
    to
    strSource = "Standard Text"

    [Navision Software a/s][Navision Financials ODBC Driver]Expected lexical element not found: <keyword>
    A Finney
    CASI
    Springfield IL
    afinney@casi400.com
    A Finney
    CASI
    Springfield IL
    afinney@casi400.com
  • AL9000
    AL9000 Member Posts: 5
    That did the trick.
    I just had to reinstall the ODBC driver and set it up initially that way. (Inherited machine)
    A Finney
    CASI
    Springfield IL
    afinney@casi400.com
    A Finney
    CASI
    Springfield IL
    afinney@casi400.com
  • BarryW
    BarryW Member Posts: 5
    I have my DSN configured to use All Characters for the IDENTIFIERS option.....I'm not getting any lexical errors....queries seem to work fine. But, when using ADO, I'll occasionally get an ODBC driver error which reads:
    [Navision Software a/s][Navsion ODBC Driver]ISAM error

    This error doesn't always occur... it seems random in nature....sometimes in the middle of a transaction. Typically, if I stop the program from within the development environment and then restart it, I'll get this error. Sometimes, if I try to restart 2-3 more times, it'll finally work. But usually, I have to close and restart VB. I've tried making sure all connections are properly closed and dereferenced, but that doesn't seem to work either....I still get the error.

    So I emailed Navision tech support with the problem. They replied that their ODBC driver does not support ADO.....only DAO could be used reliably with their ODBC driver. However, since I didn't want to use DAO (my thinking: Been there; done that. I want to move forward with Microsoft...not backwards).

    So I'm testing out NFSQL...which has it's own set of problems.

    Any thoughts/comments/experiences would be much appreciated!!
  • AL9000
    AL9000 Member Posts: 5
    I get the ISAM error if the ADO Connection has not been successfully closed.
    A Finney
    CASI
    Springfield IL
    afinney@casi400.com
    A Finney
    CASI
    Springfield IL
    afinney@casi400.com

Categories