Hi All, since a few days we have our Navision MS Sql DB Server und now we try to connect a MS Access database over ODBC with our Navision DB an link some tables but on every field name with . or spaces etc this operation fails. Has someone a solution for this problem? cu stefan rohr

Hi, Stefan, you have to use “Pass-Through” queries instead of ODBC tables linking. In this case MS Access will change table field names to a proper format. So, make a few steps described as follows: 1. Create a new “Select”-type query. 2. Switch it “SQL Specific”-type to “Pass-Through”. 3. Define the “SQL Connect Str” like “ODBC;DSN=SQLSource;UID=sa;PWD=somepass;DATABASE=Navision_DB”. 4. Write a single SQL statement like “Select * from dbo.[MyCompany$Customer]” 5. Save and run the query defined. Now, you can use this query as a read-only table in your MS Access database. Good luck. Regards, Yuri Pokusaev IBS, Senior Consultant NCPS, NCSD +7(095)987-8080

Hi Yuri, thanks for your reply, it works fine. On what position in the sql statement must i place the SQL Connect string? cu stefan

Stefan, SQL Connect string is not a part of SQL statement. It’s to be defined at the Query properties window in “SQL Connect Str” labeled textbox. You can enter the Query properties window by “[View] → [Properties]” MS Access menu commands or by right-click on a query window header. Regards, Yuri Pokusaev IBS, Senior Consultant NCPS, NCSD +7(095)987-8080

Hi Yuri, thanks again but how can I Link a table with a name for example dbo.H. Lehmann GmbH$Sachposten? When I run the Pass Trought Query, I get an error No. 208 “ungültiger Objektname” in german. Have you any idea how I can workaround this problem? cu stefan

Dear Stefan, You do not have to link a table to your MS Access database directly. It will cause errors. You have to develop pass-through query as shown above. The p-t query will represent your table as a dynamic recordset. In your example, the SQL statement to be defined as:

 Select * from [dbo].[H. Lehmann GmbH$Sachposten]

You can name it as “Sachposten” inside of MS Access and use it by “Sachposten” reference in your future queries or VBA. Regards, Yuri Pokusaev IBS, Senior Consultant NCPS, NCSD +7(095)987-8080