MSSQL ODBC

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 ypokusaev@yahoo.com +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 ypokusaev@yahoo.com +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 ypokusaev@yahoo.com +7(095)987-8080