VB, ODBC, ADO and Navision

AL9000 · 2000-08-10

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

mfabian · 2000-08-10

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

AL9000 · 2000-08-10

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

AL9000 · 2000-08-11

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

BarryW · 2000-08-11

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 · 2000-08-11

I get the ISAM error if the ADO Connection has not been successfully closed.

A Finney
CASI
Springfield IL
afinney@casi400.com

Howdy, Stranger!

Quick Links

Categories

VB, ODBC, ADO and Navision

Comments