Excel VBA and C/ODBC 3.70

slapin

Hi,

Hi I have juste developped a great excel 2003 worksheet with VBA witch gets information form an MBS Navision 3.70 French through C/ODBC

It works great, far more eficient than MS Query. (90s than 20 minutes)

However got this pain, that it only works on my developper computer.
I try to install it on same computer but user computer but I cannot make it work:
WinXP SP2 + MBS 3.70 Client + C/ODBC + Office 2003 SP1.

The macro stops on a message in french (sorry) "Erreur d'exécution 3706, impossible de trouver le fournisseur. Il est peut etre mal installé" and then "Erreur d'excution -2147220999 (8000401f9): La méthode Open de l'objet _Connection a échoué".
I try to translate:"Execution Error 3706, Impossible to find the provider. I may be bad installed" and then "Execution Error -2147220999 (8000401f9): The methode Open of object _Connection failed".

The first message does not help me, C/ODBC is correctly installed, because MSQuery works find for the same provider on those workstation.
The second is obvious, Excel hangs on the cnx.Open instruction.

Dim cnx As ADODB.Connection
Dim rst As ADODB.Recordset
Set cnx = New ADODB.Connection
Set rst = New ADODB.Recordset

cnx.ConnectionString = "DRIVER={C/ODBC 32 bit};CSF=Yes;SName=db-server;NType=tcp;UID=query;PWD=whatever;CN=GOODBOY;SERVER=N;IT=a-Z,A-Z,0-9,_;ML=1036"
cnx.Open

Got an idea?
Thanks for your help.

fb

Try adding 'PPath=...;' to your connection string.

Ex: PPath=C:\Program Files\Microsoft Business Solutions-Navision\Client;

FYI: Here's a list of the C/ODBC connection string options, taken from 'w1w1codbc.pdf':

• CSF=[Yes|No]; Specifies whether the driver operates in a client/server environment or stand-alone.
• NType=[tcp|netb]; The name of the network protocol.
• SName=xxx; The name of the server host computer.
• Database=xxx; The name(s) of the database file(s).
• CN=xxx; The company to open.
• OPT=[Text|Integer]; Specifies how the contents of an option field are transferred.
• UID=xxx; The user ID for logging in.
• PWD=xxx; The password for the user ID (UID).
• PPath=xxx; The name of the folder where the program files are located.
• TP=xxx; The name of the folder that will be used to store temporary files.
• QTYesNo=[Yes|No]; Enables or disables query time-out:
• QT=nnn; The number of seconds to elapse before time-out.
• IT=[All Characters|All Except Space|a-z,A-Z,0-9,_|All Except DOT]; Specify the way identifiers are returned to an external application:
• RO=[Yes|No]; Specifies whether access should be read-only.
• CS=nnn; The cache size in KB.
• CC=[Yes|No]; Specifies whether the commit cache should be used.
• BE=[Yes|No]; Specifies whether BLOB fields should be visible from ODBC.
• CD=[Yes|No]; Specifies whether the connection supports closing date.
• ML=nnnn; Specifies a language other than the global language.