Access Navision Tables via SQL ODBC

BGI

When you want to access pe. the item table stored in a SQL Navision via SQLODBC to a access database you probably have problems with the the field Item no. because a . is not an allowed character.

Also some customers of us have problems with the table names because they contain a $.

I tried different options but none seems to do the trick except of creating views for every table, witch i don't want to do !

It seems that navision has some kind of work arround for this.
Anybody knows what this workaround is and has tried it succesfully (pe with business objects or even msaccess) ??

Any help would be appreciatd !

Rgds
Benny Giebens

kraxmo

You are stuck. The only work around I know is for C++ developers and MS Query users who do not bring the data back into Excel.

You have to create views as this is an MS Office apps bug dealing with the Microsoft way to reference SQL server (aka server.database.owner.table.column syntax). Throwing in extra periods (even with [No.] in brackets) only messes things up. The old native C/ODBC driver at least had a workaround in the Options, Identifiers=a-z,A-Z,0-9,_

Try writing a VBA routine in Access (or VB) that will automatically read the table definition (via ADOX), generate and execute SQL CREATE VIEW statements for general consumption.

kraxmo
:roll: