Does any body has a solution for Microsoft Access to work in Navision. Access does that take any decimal symbol and the field, however, Navision field under the table does accept decimal symbol. Can anyone help me please.
What exactly are you refering to? Who do you want Attain to work with Access? Are you requesting a version that runs directly on an Access database? Or? Best regards, Erik P. Ernst, webmaster
We are using Navision version 3.10. The problem that we are having is when using Microsoft Access to create a link table from our own SQL server to Navision SQL table we can not create a link because Navision SQL (table) contains special character such as (, /, .). However, in Microsoft Access table we cannot have a period in the field name in Microsoft Access. Is there another way that we can bypass to make it work. Thank you
Hi, The various problems with ‘.’ in column names is a known bug in the Microsoft SQL Server ODBC Driver, which Microsoft are aware of (it is actually a widespread problem, affecting replication and many other areas). Attain introduces a way of converting the ‘.’ character to the ‘_’ character as a database property, from version 3.60 onwards (i.e. it is not in 3.10 that you are using). This works around the ODBC driver bug. I’m afraid you’ll have to wait for 3.60 (W1 is released), or manually remove the ‘.’ from fields whose tables you want to link to from Access.
i ve solved problem with Access. actualy, i have read-only navision “client” created in ms access. You must create view in mssql. and in this view remove all .,/ symbols from fields names. and then link this view in access
This is not a problem with SQL Server or ODBC, but a problem with Navision. Microsoft ODBC and SQL naming conventions, which were published by Microsoft when ODBC was developed by them many years ago, specifically lists periods and slashes as unacceptable characters for field or table names. I have no idea if Navision predates this, but their SQL port certainly came later so I see no excuse why they didn’t eliminate them in their SQL tables (if anyone has an idea, please fill me in) but you would think that buy now they would have some kind of fix out. Using SQL views will work, but you have to build and maintain the view for each table, which can be time consuming, and possibly a skill set that some smaller shops do not have. Also, views are only read only, so you could not use Access’ rather powerfull database updating tools. For example, Navsion has several problems with “find and replace” in OD, and access would be a fine replacement if it worked. For applications that let you access SQL data directly, such as Crystal Reports, you can use their own SQL driver or OLE DB (but OLE DB will work with the periods, but not the slashes, so you can forget about getting to your G/L table). Hope this helps
juliancohen, Sorry you not right on your first point. I have had an issue with Microsoft premier support about the infamous ‘.’ problem and they have accepted that it is a bug in their ODBC driver, in several functions including SQLColAttribute(), and furnished a bug number. This is very easy to reproduce and easily observable to foul up qualified identifiers. It is nothing to do with the list of acceptable characters in identifiers. They are parsing the “.” in quoted identifiers incorrectly, and assuming it is the . qualifier separator that comes between itdentifiers - but it is not. This is why any application using SQLColAttribute() to learn about meta-data and subsequently building a qualified identifer, can build an identifier that will not get swallowed by the driver. This includes MS Query and Access. Having said that, it is always wise to stay clear of punctuation characters that can cause problems when having special meaning - but this list is not standard not is it part of the ODBC specification. Navision Attain 3.6 now allows any character to be converted to an _ to avoid this problem. You are right that this has taken a long time to come about.