Linking your Access Database to An Accounting Program

[Essendon]

Hi,

I am working on a database system that I want to integrate with the MYOB accounting package.

I would love to be able to generate an invoice in Myob from VBA code run from within my database, with all the relevent information for the invoice being sent from my code. Also, when a customer pays for something (i.e. closes the account) I would like to be able to do that from my database.

Question1: Is this possible? Is there anyway that data can be shared with MYOB? (or any other accounting package)

Question2: I was wondering if anyone has ever done this (or at least shared data with an accounting package). If so, could you give me some hints as to what I should be looking at in my research, or where I could go for more info etc.

Thanks in advance,

Peter.

 

[bradcccs]

Peter,

This is definitely possible (depending on your MYOB version). You should read the following for further info.

Note that you can only access a small portion of the information on the standard (or plus) edition of MYOB. If you wish to access all the table structures of MYOB you will have to register for the ODBC developers pack from MYOB.

http://www.myob.com.au/about_myob/developer_components.shtml#datafile

http://www.myob.com.au/about_myob/developer.shtml

Please note that the MYOB datafiles are version specific, and a version upgrade can do interesting things to your external database.

I have had quite good success linking to MYOB datafiles, but have not registered for the dev pack.

Good luck with your MYOB exploits

Brad

*** Edit ***
Just noticed your tag.

Carn the Lions

 

[Essendon]

Thanks m8

Thanks heaps for the reply, it is much appretiated.

I can't wait to have a go at it.

See the Bombers fly up, up
to win the premiership flag
Our boys who play this grand old game
will always......oops I forgot, we lost.

catch ya

Peter

 

[Essendon]

1 more question

Hey Brad.

I just had a look at those urls. I am excited by what I read there.

I was just wondering what you can do with your limited access with the ODBC driver? What sort of things do you do with it?

Also, I am planning on developing this application that integrates with my MYOB for my place of work, with the idea of maybe selling it to other similar businesses in the future. I assume that the Developement enivronment will allow me to develop the application, and then take the developed application and put it on a machine that has a version of MYOB with all the tables unlocked ($259)?

Thanks for your help....

Peter

 

[bradcccs]

Peter,

The three tables that you get access too are really just a tease.

Tables from memory:
Company info: Just the info on the users company - Not much use for manipulation, but may be handy in your case to pull company name, ABN etc for forms / reports

Card info: See below
Card Detail: See below

MYOB uses an array of tables to record the card information.

Card info is the company basics (real basics) eg: Name, type (individual or company) etc.
Card detail records the detail for the cardID. This gives you access to address details (only for primary address for company - I think?)

My use of the above was for a quotation system. The user retrieved address details from the existing MYOB list, and compiled a quotation. The quotation compiled the list of contractors needed for the project (also collated from the MYOB db). It was not really ingenious, but "did the job".

Your post actually inspired me to "crack MYOB open" again. But for some reason, my DSN file will not connect. Haven't got time to mess with it now.

As for the query in regards to the ODBC registration, you raise a valid point. I am not sure as to whether your own registration of the ODBC driver will transfer to your client. Looking at the registration process, I am guessing not. This may be different if you register for the development pack??

Worst case scenario, your client would have to register their own ODBC driver (~$259.00). I am sure that this would be a minimal expense on a specific development that you are proposing, but could be a big negative if you plan to distribute your solution to others on a larger scale.

Ah, so much potential and so many questions.

I suppose if it were too easy, you and I would be out of a job

Happy hunting.

Brad.

***
Confession to make.
I am not a Lions supporter. I just jumped on the bandwagon after the bloodbath on the weekend.
I am actually a Demons supporter (But we will keep that quiet this week)
And NO, I do not drive a Range Rover. (will have to keep tapping keys for a few more years until I can afford the official Melbourne Suporters Vehicle)
***

 

[bradcccs]

I knew I shouldn't have messed with the DSN.

You may want to be careful with the ODBC pack installation.

I have now been forced to re-register (activate) my Office 2002 suite with MS after the MYOB ODBC installation. (Is that normal???)

Try to explain that to MS when I really do need to re-register in the future. I really love filling out stat dec's and explaining why I need to re-register due to something that was not my fault.

Ah, the love of canning MS.

Brad.

 

[Essendon]

I installed the ODBC drivers ok. Sorry to hear about your troubles, I am not sure why that would have happened.

After I installed the drivers and configured it, I created a blank database and went to File->Get External Data->Link Tables selected ODBC Database from the File Type list, and selected my MYOB datafile. This all went ok. I now have the three link tables in my new database. When these tables get updated in MYOB, the updates are reflected in my link tables. I can access all the data.

The problem is that whenever I go to change any of the data in the link tables I always get the following message:

ODBC -- Update on a linked table 'MYOB_Cards' failed.
[ATI][OpenRDA ODBC]dam_verify_query_support(): Functionality is not supported. (#0)

I am really hoping that this doesn't mean that you can only get data and not update/change any of it. Please tell me that this is not true and that I am going about it all wrong. Have you been able to edit any data when you have been trying it out?

Thanks for all your help thus far. It is greatly appretiated.

Peter

P.S. Being a bombers supporter I was so happy that Melbourne lost because it keeps us in the top eight. I hope both the dons and the demons can make the finals though....

 

[Essendon]

Essendon

I forgot to ask,

Did you set up link tables, or did you connect to datafile in code (which I assume is possible). If your answer is the latter, could you give me some hints as to the syntax involved.

Thanks again,

Peter

 

[bradcccs]

Doesn't look good Peter,

I got off my butt and corrected my link problem.

My initial project did not require data updates, but rather just referencing. I actually spent time preventing users writing back to the detail tables (waste of time – I now realise)

From the error you listed, it appears that MYOB base there ODBC driver on Automation Technology Inc’s (ATI) OpenRDA and Data Access Management (DAM) components. Thus if they (MYOB) have not provided write back permissions, I don’t think that it can be done. (Open to other opinions there though. Not 100% on that.)

I have some further reading for you though:
http://d2d.myob.com.au/downloads/ODBCDevPack.PDF
This will answer your questions in regards to who has to register the file for ODBC access (and I don't think you will like it).

Interesting to note that the MYOB ODBC V1.5 driver will give you full access to the ClearWater data file without registering. Could at least enable you to experiment before investment.

My version is only 1.01. Haven’t been able to find 1.5 for download (without expense). I would suggest that 1.5 is a part of MYOB V12+ (I only have access to V11) - I would also be open to obtaining V1.5 if you so happen to come across it.

As for your query on writing via code, this is definitely possible. Two ways (I think). Writing to a linked table as you have already set up. Or linking the ODBC in code (not table) and then writing. I only use linked tables. Again this would only be possible if given permission.

I am interested in your progress. Keep me up to date.

Cheers

Brad.

 

[Essendon]

Hey, thanks for all your time m8.

It doesn't look good does it. I have emailed Both MYOB Australia and MYOB USA asking about whether it I can write to the MYOB datafile in anyway. I am awaiting their replies now.

I must say that I am very disapointed that MYOB ODBC is Read-only. I really think that I could have made a successful product if I could interact with MYOB in realtime. I guess the other option is to have access output data to a standard data file and then manually import it into MYOB or something like that. Not as easy for the users though.

I'm using MYOB Premier V6.02. I have ODBC 2.0 with that. Would that work ok with your configuration?

Thanks again for your help, I'll keep you posted about my progress...

Peter

 

[bradcccs]

Hmmm V2.0 - now that may be handy

Will have to keep my eyes open.

I am very interested to hear what MYOB have to say. Any info would be appreciated.

Brad.

 

[Essendon]

Here was their response

"Dear Peter ,

Thank you for your email regarding MYOB ODBC.

The MYOB ODBC Driver offers Read Only Access to the MYOB datatables. This
level of access is unchanged whether you are an ODBC Driver Customer, or an
ODBC Developer Pack Customer. If you wish to incorporate the changes made
to the data in a 3rd party tool, you can import this informaiton back to
MYOB via the Import function held within the MYOB File Menu.

MYOB ODBC Developer Pack Customers, have an option to download an Auto
Import utility that can streamline the File Import process.

For your information, you can make a DSN connection to the Clearwater
datafile (the sample company installed with the program) where you can gain
free access to all 141 data tables available from MYOB ODBC. With an
unregistered installation of ODBC, as you have already identified, you can
only gain access to three tables if you have connected to your own
datafile. You can review all tables by connecting to the sample company '
Clearwater.'

I have attached some general information on the ODBC Driver and Developer
Packs including information on the File Import function and Auto Import
function

The Developer Pack and ODBC Direct Driver are two separate products. If you
are developing solutions for your customers/clients you may only need to
purchase the MYOB DeveloperPack and your customers will need to purchase an
unlock code from MYOB for their MYOB ODBC Direct Driver. If however you are
developing solution(s) for internal purposes you will need to purchase the
unlock code for MYOB ODBC Direct driver and/or the MYOB DeveloperPack
depending on your needs. If you are unsure of your requirements please
contact MYOB Technical Support and ask to speak to the Connectivity Team
for further assistance.

Please note MYOB ODBC Direct driver will be installed on your computer
when you install MYOB Premier 6, or MYOB Accounting/Plus 12. The MYOB ODBC
Direct Driver is read only. This means you can only read the table
information from the MYOB datafile. To write back to the MYOB Datafile you
must import the required information via the applications file menu. We
have developed tools to assist in this process. listed below, that are
available on the purchase of the MYOB DeveloperPack.

To make the most of MYOB ODBC Direct driver you will need to register it.
However you can view all available tables in the sample company Clearwater.
You will also be able to view 3 tables in your own datafile running MYOB
ODBC Direct driver unregistered. For more information on MYOB ODBC Direct
see the attach PDF Document below.


(See attached file: 2111 ODBC DataSheet.pdf)
(See attached file: 0316 Export Import V11 P5.pdf)

We also have MYOB DeveloperPack. This product is directed towards
developers. The DeveloperPack does not come with a registration for MYOB
ODBC Direct driver. This means if you purchase the MYOB DevelioperPack you
will have the same access to your datafile as an unregistered MYOB ODBC
Direct driver will.

For more information on MYOB DeveloperPack see the attached PDF documents
below

(See attached file: 2112 DevPack DS.pdf)

MYOB DeveloperPack contains tools to help you automate your solutions. The
MYOB Auto import tool allows developer to automate the write back function
to the MYOB datafile. For more information see the attached AutoImport.PDF
file

(See attached file: AutoImport.PDF)

MYOB DeveloperPack continues to offer more resources for developers. The
latest of these due for release in May 2002 is MYOB SDK (Software
Developers Kit). Full details are be found on the BusObjSDK.PDF document.
The SDK is now available to download from the d2d.myob.com.au webnet.

(See attached file: BusObjSDK.PDF)

More information can be found at the MYOB developer website d2d.myob.com.au


If you have any further questions, please do not hesitate to contact us.

Kind regards

MYOB Developer
d2d.myob.com.au

MYOB Developer Community ... building business solutions.
For more info send mail to developer@myob.com.au"

***

If you want the attachments, I could email them to ya.

I think I will get the developer pack and the unlock code. The autoimport sounds pretty good because you can completely automate the import process.

Thanks again for your help. Let me know if you want the attachments.

Cheers,

Peter

 

[bradcccs]

Looks like we were pretty spot on.

Disappointing about the "read only" developer pack.

I normally find MYOB pretty helpful, and it looks like they have been up to their standard here. (Nice to note that MYOB Aust replied )

I would appreciate the attachments as listed (particularly the autoimport pdf).

I can foresee a few problems with batching and importing the created info. But we will leave that for the development stage.

Interested in your progress.

Brad.

 

[Essendon]

Your Email Address

Check your email

 

[bradcccs]

I am eagerly awaiting those attachments.

As an aside, have you worked at all with RetailManager?

Powerful POS lacking the backoffice power. I am interested in exploiting the naked tables involved.

???

Brad.

 

[Essendon]

I have never used RetailManager. Sounds alright though.

I have just registered for the trial version to be sent to me so I can have a look...

I still need that email address to send those attachments m8...

Catch ya,

Peter

 

[bradcccs]

Peter,

Sorry, seem to be having trouble emailing you. I have sent 2 replies to the address attached to this forum. They were not rejected, but you don't seem to be getting them (or blocking them?)


Anyhow, here 'tis: removed

Thanks again

Brad.