Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
has anyone used access as a front end with myob as backend
- Thread starter Juddy58
- Start date
- Thread starter
- #3
Sorry, myob stands for "Mind Your Own Business", it is an accounting program that is used very often by businesses in my country "Australia", i didn't even consider that it is probabily not a common program overseas.
Still if any one has had any experience in doing this, any advice would be greatly appreciated. Thanks!
Still if any one has had any experience in doing this, any advice would be greatly appreciated. Thanks!
My wife runs a business developing Access and is qualified as an MYOB CC (Certified Consultant) Im sure she would be interested in your need to link an access DB and MYOB using ODBC she is not a member of tek-tips But can be contacted at help@iassist.com.au
- Thread starter
- #5
thanks for the reply kiana, I have actually figured out how to do this using an odbc driver.
A client of mine claims they have reached the limit with what they can achieve in myob. and have been asking me whether access can be used to manipulate the data in a way in which myob can't. They have been asking me about this as they dont want to purchase a new (expensive) software package.
A client of mine claims they have reached the limit with what they can achieve in myob. and have been asking me whether access can be used to manipulate the data in a way in which myob can't. They have been asking me about this as they dont want to purchase a new (expensive) software package.
I am using Access as a front end and MYOB as the data. I see that you worked out how to connect using the MYOB odbc.
All seems to be going well for me except that some tables and queries are EXTREMELY slow... are you having the same problem?
EMG
All seems to be going well for me except that some tables and queries are EXTREMELY slow... are you having the same problem?
EMG
- Thread starter
- #7
In the end i ended up developing a seperate program, I managed to get their so called 'ODBC' driver working however it only allowed me access to three myob tables. I could only import data into access, i couldn't write out to myob. I got in contact with myob and was told that i would have to purchase a key for their odbc, around AU$250 so i could access all the myob tables. And if i wanted to write back to myob i would have to purchase their their developer program which was over AU$1000, in the end i couldn't justify the cost.
What steps did you do to access the myob tables if you dont mind me asking?
What steps did you do to access the myob tables if you dont mind me asking?
It's true that the key cost $250. This is the key that activates the driver which is part of the $1000 SDK kit.
Of -course I bet they didn't tell you about the $14.95 MYOB book that you can buy at "any good magazine shop" by Next Handbooks has the needed drivers (but no explanations about what goes on inside MYOB of course).
Still that's not as bad as Quicken. Free in the States but over $450 here downunder.
Signed Disgruntled & ripped off-ed programmer.
Of -course I bet they didn't tell you about the $14.95 MYOB book that you can buy at "any good magazine shop" by Next Handbooks has the needed drivers (but no explanations about what goes on inside MYOB of course).
Still that's not as bad as Quicken. Free in the States but over $450 here downunder.
Signed Disgruntled & ripped off-ed programmer.
The company I am creating this for registered the MYOB ODBC Direct DSN - the $250. After that you have to call MYOB so they give you an unlock code before you can access all the tables. The unlock code will work for any ODBC DSN created.
So I called MYOB got the unlock code, created the MYOB ODBC DSN -you will have to install the MYOB ODBC Direct Driver from the MYOB Install Package. Used the unlock code via the ODBC32 Connection properties for the MYOB ODBC DSN when creating the DSN.
After installing/unlocking and creating the DSN I was able to LINK all the tables through Access choosing an ODBC type database rather then a file database. I choose my MYOB ODBC that I just created and WALAH... I was able to access all MYOB tables.
The only problem I am having is that it is extremely SLOW running queries and tables. Takes from 3 - 7 minutes joining 2 - 3 tables. I have found some Microsoft Access documentation that I am about to read... if anyone is in need....
I think it's a scam personally. First you buy a $800+ program, THEN you have to register the ODBC $250 then you have to call and wait on hold forever to get the an unlock code. Oh and if you try to get support you will have to fork out another $299+ for the year.
I didn't need to write back to MYOB so this solution is working for me so far.
EMG
So I called MYOB got the unlock code, created the MYOB ODBC DSN -you will have to install the MYOB ODBC Direct Driver from the MYOB Install Package. Used the unlock code via the ODBC32 Connection properties for the MYOB ODBC DSN when creating the DSN.
After installing/unlocking and creating the DSN I was able to LINK all the tables through Access choosing an ODBC type database rather then a file database. I choose my MYOB ODBC that I just created and WALAH... I was able to access all MYOB tables.
The only problem I am having is that it is extremely SLOW running queries and tables. Takes from 3 - 7 minutes joining 2 - 3 tables. I have found some Microsoft Access documentation that I am about to read... if anyone is in need....
I think it's a scam personally. First you buy a $800+ program, THEN you have to register the ODBC $250 then you have to call and wait on hold forever to get the an unlock code. Oh and if you try to get support you will have to fork out another $299+ for the year.
I didn't need to write back to MYOB so this solution is working for me so far.
EMG
EMG and others,
I am very interested in this thread as I a developer who works specifically with MYOB. I am also an MYOB Certified Consultant.
From what I understand, the ODBC driver was released as an extension of the MYOB product. That is, it allows you to get more out of the product.
Let's face it, MYOB was written for hundreds of thousands of businesses in Australia and not all businesses will get what they need out of it. Thats is who the ODBC driver and the developer pack are intended for.
$259 for the ability to write an unlimited number of reports in whichever ODBC compliant product (Access, Crystal Reports etc) you like doesn;t seem like a rip off to me.
$1000 (or thereabouts) for the ability to interface any database with MYOB, from websites, to complex SQLServer databases is also not too bad, given that most companies who require such tools are fairly large.
In relation to performance issues of the ODBC driver, I have found that certain circumstances have a substantial impact on speed:
1) accessing the datafile whilst other people are logged in
2) using Excel's MS Query
3) inefficiently designed queries
4) etc etc
Tip: I have found that it is actually quicker to take a snapshot of the database (in Access), and then run the queries off the snapshot. Infinitely quicker than querying the live database.
Hope I haven't rambled on too much
Rael
I am very interested in this thread as I a developer who works specifically with MYOB. I am also an MYOB Certified Consultant.
From what I understand, the ODBC driver was released as an extension of the MYOB product. That is, it allows you to get more out of the product.
Let's face it, MYOB was written for hundreds of thousands of businesses in Australia and not all businesses will get what they need out of it. Thats is who the ODBC driver and the developer pack are intended for.
$259 for the ability to write an unlimited number of reports in whichever ODBC compliant product (Access, Crystal Reports etc) you like doesn;t seem like a rip off to me.
$1000 (or thereabouts) for the ability to interface any database with MYOB, from websites, to complex SQLServer databases is also not too bad, given that most companies who require such tools are fairly large.
In relation to performance issues of the ODBC driver, I have found that certain circumstances have a substantial impact on speed:
1) accessing the datafile whilst other people are logged in
2) using Excel's MS Query
3) inefficiently designed queries
4) etc etc
Tip: I have found that it is actually quicker to take a snapshot of the database (in Access), and then run the queries off the snapshot. Infinitely quicker than querying the live database.
Hope I haven't rambled on too much
Rael
ian0001 seems to have a tip here that would really help me out.... Are you still there? I am in Canada and am suffering in that an application that I have been developing to interface to MYOB has become a DUD since Intuit who owns MYOB Canadian rights has decided to kill the MYOB product in Canada. My partially completed application needs to access MYOB tables now but I can not get the ODBC unlock code no matter how much money I can pay. ian0001 mentions in earlier post that the Mag. "Next Handbooks" supplied a complete ODBC driver at some point. I would be very interested in trying this alternative driver. Does anyone know how I can order a back issue of that Mag? I have been to the Next Media website and can't see anything useful. Any help would be appreciated. I would at least like to try the application that I have built over the past year.
bumskis,
I have a copy of the book with me and it DOES NOT include the odbc driver. The book was quite contentious in Australia when it was released because it contained a number of tools that people had already paid good money for, but I can assure you that it definitely did not contain a complete ODBC driver. This might be different in Canada.
As an MYOB developer, I am able to get hold of 7-day free ODBC trials but I think these only work with the Australian version of MYOB.
Let me know if you think there is anything I can do to assist.
Rael
I have a copy of the book with me and it DOES NOT include the odbc driver. The book was quite contentious in Australia when it was released because it contained a number of tools that people had already paid good money for, but I can assure you that it definitely did not contain a complete ODBC driver. This might be different in Canada.
As an MYOB developer, I am able to get hold of 7-day free ODBC trials but I think these only work with the Australian version of MYOB.
Let me know if you think there is anything I can do to assist.
Rael
Rael,
I am still having slowness problems connecting through the ODBC. I have kept my queries as effecient as possible. But I am still haviung problems. I can't even access a simple table without having to wait forever.
How do you take a snapshot of the MYOB database as you suggested?
PS... sorry about the rant... you made me come to my senses, besides everyone has got to make money right?
EMG
I am still having slowness problems connecting through the ODBC. I have kept my queries as effecient as possible. But I am still haviung problems. I can't even access a simple table without having to wait forever.
How do you take a snapshot of the MYOB database as you suggested?
PS... sorry about the rant... you made me come to my senses, besides everyone has got to make money right?
EMG
EMC,
Instead of running the queries directly off the MYOB database, try copying the relevant tables into MS Access and then running queries off these tables. Performance should improve dramatically. If the improvement is suitable, you can then look at ways of automating the MYOB table import process.
Rael
Instead of running the queries directly off the MYOB database, try copying the relevant tables into MS Access and then running queries off these tables. Performance should improve dramatically. If the improvement is suitable, you can then look at ways of automating the MYOB table import process.
Rael
Thanks Rael,
That's not gonna work becuase it takes a LONG time just to import the data into the access.
I don't understand why it takes so long. These are standard queries and reports that I am creating, and even single tables that I am accessing. I am so confused on why it runs so slowly.
Emma
That's not gonna work becuase it takes a LONG time just to import the data into the access.
I don't understand why it takes so long. These are standard queries and reports that I am creating, and even single tables that I am accessing. I am so confused on why it runs so slowly.
Emma
Are you copying the data across the network? If so, try doing it locally and see if there is any improvement.
Is anyone using MYOB whilst you are doing the query? Try doing it with noone logged in.
Which tables are you using? Perhaps there are smaller tables with the required data?
Rael
rael@lucidgroup.net
Is anyone using MYOB whilst you are doing the query? Try doing it with noone logged in.
Which tables are you using? Perhaps there are smaller tables with the required data?
Rael
rael@lucidgroup.net
I've done a lot of reporting for a client using MYOB ODBC, and the first thing I found was that the sample file (Clearwtr.dat or .pls) lets you access the whole set of tables, for free...
and the second thing was that performance at the client's site was really bad; I asked MYOB if setting indices on the linked tables would help, and they said no, but I went ahead and did it anyway, so a query that used to take 40 seconds now takes less than 10 on their large file. So try this tip, and see if you get the performance increase. Just add an index to what looks like the primary key, usually the first one in the table, and do that for each table you are using in the query. Post some feedback.
Regards
Mike
and the second thing was that performance at the client's site was really bad; I asked MYOB if setting indices on the linked tables would help, and they said no, but I went ahead and did it anyway, so a query that used to take 40 seconds now takes less than 10 on their large file. So try this tip, and see if you get the performance increase. Just add an index to what looks like the primary key, usually the first one in the table, and do that for each table you are using in the query. Post some feedback.
Regards
Mike
I've done a lot of reporting for a client using MYOB ODBC and Microsoft Access, and the first thing I found was that the sample file (Clearwtr.dat or .pls) lets you access the whole set of tables, for free...
and the second thing was that performance at the client's site was really bad; I asked MYOB if setting indices on the linked tables would help, and they said no, but I went ahead and did it anyway, so a query that used to take 40 seconds now takes less than 10 on their large file. So try this tip, and see if you get the performance increase. Just add an index to what looks like the primary key, usually the first one in the table, and do that for each table you are using in the query. Post some feedback.
Regards
Mike
and the second thing was that performance at the client's site was really bad; I asked MYOB if setting indices on the linked tables would help, and they said no, but I went ahead and did it anyway, so a query that used to take 40 seconds now takes less than 10 on their large file. So try this tip, and see if you get the performance increase. Just add an index to what looks like the primary key, usually the first one in the table, and do that for each table you are using in the query. Post some feedback.
Regards
Mike
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question