G'day Mike,
Thanks for your reply - this is what I have copied from the PDF on
connections from MYOB (the string keywords were in a table):
Connecting to a company file that has not been defined as an ODBC data
source
Using the DRIVER keyword set with the value DRIVER={MYOB_ODBC_AU7} you can
connect to a company file without the need to set up an ODBC User data
source for the file. When connecting to a company file for reading purposes
without using an ODBC User data source, the following keywords must be set:
Type=MYOB; UID=<user ID>; PWD or PASSWORD=<password>; DATABASE=<path and
name of company file>; HOST_EXE_PATH=<path and name of MYOB application>; A
number of optional keyword values are available. See 'Read connection string
keywords and supported values' on page 4 for more information.
An example connection string
An example connection string is shown below: " Driver={MYOB_ODBC_AU7};
TYPE=MYOB; UID=Administrator; PWD= MyPassword; DATABASE=C:\Premier\Test.myo;
HOST_EXE_PATH=C:\Premier\MYOB.exe; NETWORK_PROTOCOL=NONET;
DRIVER_COMPLETION=DRIVER_NOPROMPT; ";
Read connection string keywords and supported values
Described in the table as Keyword Attribute value description Values Example
DATABASE Explicit path, including the filename where the MYOB company file
is located. Value is the location of company file
DATABASE=C:\Premier\Test.myo;
DRIVER Name of the driver. This value must always equal "DRIVER
={MYOB_ODBC_AU7}". If DRIVER keyword is set, DATABASE keyword must also be
set. {MYOB_ODBC_AU7} DRIVER={MYOB_ODBC_AU7};
DRIVER_COMPLETION DRIVER_NOPROMPT - Do not prompt the user for additional
connection information. Instead an error will be returned. DRIVER_PROMPT -
Display the dialog box prompting the user for additional connection
information. The default value is DRIVER_NOPROMPT DRIVER_NOPROMPT
DRIVER_PROMPT DRIVER_COMPLETION=DRIVER_NOPROMPT;
DSN Use only if a DSN has been set up for a data source. If a DSN is set,
do not use the DATABASE keyword, as it will override the DSN value. Value is
the DSN name. DSN=<DSN name>;
HOST_EXE_PATH Explicit path to the location of the MYOB executable. This
executable must be compatible with the company file (that is, same country
code and same version number). Note: Should the application already be
running, it must be using the same company file as that specified in the
DATABASE keyword. Value is the location of MYOB executable
HOST_EXE_PATH=C:\Premier\MYOBP.exe;
NETWORK_PROTOCOL Network Protocol to be used while using the MYOB
application. Setting the ACCESS_TYPE to READ_WRITE will force the
NETWORK_PROTOCOL to NONET NONET NETBIOS TCPIP NETWORK_PROTOCOL=NONET;
PWD or Password The password corresponding to the DSN set up for the user
ID (UID), or an empty string if there is no password. Note: If this value is
not NULL, it will override the password entered when setting up a DSN. Value
is the password as set up in the MYOB application PWD=MyPassword; or PWD;
SQL_LOGIN_TIMEOUT Connection timeout in seconds. This value cannot be less
than 10 seconds as the MYOB application needs a significant amount of time
to initialise its Java libraries when it launches.
TYPE Set this value to "MYOB". MYOB TYPE=MYOB; UID A user ID. Note: If this
value is not NULL, it will override the username entered in the DSN. Value
is the user ID set up in the MYOB company file UID=Administrator;
Regards
Clive Williams
CRW Services
53 Gibson Street
Silverdale NSW 2752
Mobile: 0418 657 833
A H: (02) 4774 0868
email: [EMAIL PROTECTED]
MYOB Certified Consultant
Ostendo Consulting Partner
MYOB Accredited Trainer
Cert IV Workplace Training & Assessment
> -----Original Message-----
> From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of MikeB
> Sent: Tuesday, 13 May 2008 02:39
> To: RBASE-L Mailing List
> Subject: [RBASE-L] - Re: ODBC problems V8 connecting to MYOB continuing
>
> Clive,
>
> Please post again the proper commands as you are trying them. From
> what is
> listed below, you are combining two connect strings into one. One for
> Sql
> Server and One for MYOB. Just post what you think is the connect
> string
> without any description and let's start from there.
>
> <
>
>
> I am, despite Mike Byerley's kind help, still unable to connect to a
> MYOB
> datafile (only tried on Australian version). I have established a
> System
> DSN for the MYOB sample file clearwtr.myo and can use that to extract
> data
> to both Excel and Access 2007 with no difficulty - there are no
> restrictions
> on the MYOB ODBC drivers and the clearwtr test file. Using RRBYW14 I
> have
> gone to Utilities>Connect SQL Data Source, clicked on the Machine Data
> Source tab and then on the appropriate system DSN. When I go to
> Utilities>Attach SQL Database Tables I am presented with the Attach
> Table(s)
> dialog box. Selecting a table and then clicking Attach does nothing -
> the
> dialog window remains open. I am running R:Base V8.0.17.30509 on a
> Toshiba
> Tecra with 2GB of RAM and an Intel Centrino duo processor under Windows
> Vista Business. It doesn't matter if I have UAC on or off.
>
>
>
> I tried Mike's suggestion on a DSN less connection and when I try that
> using
> a test database with just 1 table defined using:-
>
>
>
> --ConnectCRWS
>
> --Test connection string for DSNless connection to clearwtr.myo
>
> --Last updated by Clive Williams 12 May 08
>
>
>
> CLEAR VAR vConnect
>
> SET VAR vConnect TEXT = +
> SCONNECT ';driver={SQL
> Server};server=corpseadb0d;uid=my_user_name;pwd=my_pw;database=JohnDoe;
> '
>
> 'SCONNECT '';DRIVER={MYOB_ODBC_AU7}; TYPE=MYOB; UID=Administrator'
>
> SET VAR vConnect = +
>
> (.vConnect + '; PWD; DATABASE=C:\AllWork\Premier11\Clearwtr.myo')
>
> SET VAR vConnect = +
>
> (.vConnect + '; HOST_EXE_PATH=C:\Premier11\MYOBP.exe;
> NETWORK_PROTOCOL=NONET')
>
> SET VAR vConnect = +
>
> (.vConnect + '; DRIVER_COMPLETION=DRIVER_NOPROMPT')
>
> SET VAR vConnect = +
>
> (.vConnect + '; SQL_LOGIN_TIMEOUT=30;')
>
> &vConnect
>
> RETURN
>
>
>
> Running this command file at the R> prompt results in the message:-
>
> -ERROR- Error message from server: [MYOB ODBC] Connection exception -
> Connection does not exist (2957)
>
> The commands are taken from Mike's advice and the MYOB ODBC
> documentation.
>
>
>
> The MYOB file referred to is the sample one that is supplied with MYOB
> and
> all tables are accessible via ODBC and not restricted as are non-
> registered
> files. I also have my own MTOB file that is registered for ODBC and can
> extract the same data to both Excel & Access.
>
>
>
> Can anyone suggest how I can establish a connection that enables me to
> extract the same data I can easily extract via the M$ products. I did
> note
> that when a table was extracted to Access the table name showed as
> MYOB_Customers although it showed as MYOB.Customers on the Attach
> Tables
> screen.
>
>
>
> The project is for one of the largest freight companies in Australia
> who are
> looking at the extraction of MYOB invoices from clients datafiles and
> importing the data with additional formatting into a SAP database.
> Quite
> sensibly, they have a prohibition on Access within their organisation
> (the
> only time I have used it is to see if I could do the data extraction to
> that
> system), so extracting to Access then going from Access to R:Base is
> not an
> option.
>
>
>
> Regards
>
>
>
> Clive Williams
>
>
>
> CRW Services
>
> 53 Gibson Street
>
> Silverdale NSW 2752
>
>
>
> Mobile: 0418 657 833
>
> A H: (02) 4774 0868
>
> email: [EMAIL PROTECTED]
>
>
>
> MYOB Certified Consultant
>
> Ostendo Consulting Partner
>
> MYOB Accredited Trainer
>
> Cert IV Workplace Training & Assessment
>
>
>
>
> __________ Information from ESET Smart Security, version of virus
> signature database 3092 (20080512) __________
>
> The message was checked by ESET Smart Security.
>
> http://www.eset.com
