Connect to SQL Navision Via SQL Driver ??

brucem

Have you seen this before..

I have a machine with Access 97 and Visual studio 6 on it.

I am trying to conenct to the navision SQL database on a separate server.

I have created a system DSN using the SQL ODBC driver to connect to the database.

In visual interdev 6.0 ( serv pack 4) i can create a data connection and show all the navision tables, open them.

In Access 97 using the same DSN i can connect to the tables to view them but when i attempt to link them i get an error table not found.

The error message for the cronus uK$.item table becomes cronus uK$_item ( dot replaced by underscore).

Have you seen this before. Bear in mind this is the same SQL DSN using an SQL driver not a navision ODBC connection ?

It would appear that Access is attempting to translate the table name, but VI6 does not ???

Why should you be able to connect to SQL server via one source and not another ??

Bruce

brucem

Now this is weird !!

I have been investigating trying to figure out what is going on here. It appears that Access 97 will not accept table attachments via the SQL data driver if the table contains a field that has a . in it, whereas Visual interdwv will !!!

My guess is that Access 97 and MS query 97 interrogates the fields, finds a dot (.) and then kicks in with translation converting the (.) to and underscore. It then converts the table name using the same translation and replaces the (.) in the table name with an underscore also !!

This of course doesn't help but might explain it

The way around this might be to use Views where the field names have been translated to remove the (.)

Bruce

mfabian

I think your problem has neither to do with Navision SQL nor with Access 97. Try the following:

In Control Panel --> ODBC32 --> Select the DNS in question and click on configuration.
In the ODBC Setup dialog you find the Option button lower right corner.
There you find a listbox with possible options for "Identifiers". Most likely (that's default) this option is set to "All Except Dot". That's what causes your problems. Change the "Identifiers" Option to "All Characters" and your problems should be solved.

---

Marcus Fabian
m.fabian@thenet.ch
+41 79 439 78 72

[This message has been edited by fabian (edited 29-09-2000).]

brucem

Marcus,

Thanks for the reply, but this is not the problem. I am NOT using the Navision ODBC DLL i am using a SQL server connection in the same way as i would if connecting to any normal SQL database. There is therefore no option to change the DSN setting to ALL except .. or ALL Characters.

I have tried modifying the settings in the SQL DSN, but the default works best.

Bruce

jacobm

We have same problem with Access 2000.

I works fine when you open or link a table which has no fields with "."

Have anyone a solution for this?

Jacob

brucem

Jacob,

The problem appears in Acess 97 and 2000 when trying to create a MDB / MDE.

I have jsut got it to work in 2000 by creating a ADP ( Access Data Prject) this I beleieve uses OLE DB direct on the SQL datasource and misses out JET 3.5 / 4 completely.

My guess is that Jet is where the problem lies. I am waiting for Microsoft to get back to me ( so don't hold your breath).

If you can work with ADP then go that route. For me it's a pain as most of the work in the 97 MDB that talks to Nav 2.1 is DAO so it would be a lot of work to get to where i am now in an ADP !!

Bruce

brucem

This gets stranger !!!

I have tried getting data from The SQL database via Excel / Ms Query and depending on how you do this it can work occasionally.

If you ask Excel to get a dataset via Query and select a table with dots (.) in the table name Query also complains.

If whils in query after accepting the syntax error near "," error and go to edit the query manually, you can add the table and then pick up the columns. This returns a data set to Excel !!!

However when you ask excel to edit the query source then surprise surprise it responds with the error again..


Bruce

brucem

This gets even weirder !!!


I have just spoke to Navision UK who's technical support team say rename all the fields with dot's in them to _ underscores.

There is also a supposed to be a FAQ on this that the Resellers have and an object to convert all these fields to underscores.

Will keep you posted when (if) i get the info.

Bruce

brucem

Latest news from microsoft technical support on this is that it's an Access design feature (ha-ha). Visual Interdev cab do it because it uses "different technology" ( ho-ho)

So looks like either create a number of views on the database with aliased table / field names or find out more about this FAQ / object...

Another reason for moving to ADO / VB ( oops should have posted that in the other forum!!)

Bruce

brucem

Hi,

The latest news is that i have received from Navision a coplue of fobs which are supposed to do table name rechanges, it does however mention nothing about code / reports etc...

Will try them out and let you know what happens

Bruce

ZenZagg

In the connect string, you can specify the Identifier, the keyword is IT. You can use it like:

mdlNavisionCN.connectstring = "ODBC;CSF=Yes;DRIVER={C/ODBC 32 bit};SName=NavServer;NType=tcp;CN=Consolidated Engineering;UID=User;PWD=Pass;OPT=Text;QTYesNo=Yes;QT=60;IT=a-z,A-Z,0-9,_;RO=No;CC=Yes;BE=yes;"

You can read the C/ODBC reference on your Navision product CD to learn about what the other keywords do. I hope this has helped!

FYI: I use this DSN-less connection method in conjunction with a GlobalDSN.ini file on a network server which provides the values to plug into the connect string, so I have a central config file for all my database connections (even SQL server) in my VB apps. This way, I do not have to configure DSN's on each client, just have the VB applications parse the GlobalDSN.ini file and gather connection properties from there. Perhaps I will post the source code for this method somewhere...