Update UDF From VB

[TallOne]

Hi,

I'm developing on MAS 200 3.71. We are reading data with the ProvideX ODBC driver. I would love to update using ODBC but ProvideX and Sage will not provide me with such a driver. Sage support did say that there are Master Developers doing it though! Any MD here that can help me do this? I'm using a VB application that loops through the read only recordset inserting each SO into our order entry system. Here's my problem. After the record has been successfully imported into our custom system, I need a way to update a user defined field in MAS SO_SalesOrderHeader table called Exported. I'd like to fire off a cn.Execute(SQLgoesHERE)

Update SO_SalesOrderHeader Set Exported = 1 Where SalesOrderNo = 'mynumber'

I would love to use vb to do this but as we know, the ODBC Driver is read only. I can create a xls or csv file containing a list of the SO#'s but Sage Support indicated that VI is not really designed to update a user defined field based upon a Sales Order. This just doesn't sound right. Does anyone have any comments or suggestions?

Thanks In Advance,
Jerry

 

[BigLouie]

In MAS the ODBC driver is READ ONLY. While ProvideX does have a read-write driver it will cause 100% data corruption 100% of the time. It is not designed or intended for use with MAS.

In short what you want to do is not possible with VB. Your best option would be to have a master developer write a program in ProvideX that writes to your application and then sets the value in the UDF.

 

[TallOne]

BigLouie,

Thanks for the quick reply. I was hoping to get something besides hire someone else to do it. I need to develop this myself. I'll continue to explore MAS VI and any other means possible. I'll post my resolution later.

 

[Blorf]

I have done this using vba.

My app was trying to change the fiscal year in GL, which, according to Mas techies, is sketchy at best using any utilities within Mas 200, and usually fails. They actually suggested I create new companies, if you can beleive that.

My solution involved about 2 days with a hex editor studying the structures, then some vba code (Thats what I had available, MS access) to open as binary, search for key things, and update. Worked like a charm, but what a waste of time for something that should be simple.

ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

 

[TallOne]

Integrating with MAS has been an experience I would not wish upon anyone. Although it's more advance than some packages, in no way does it compare to the intuitiveness of Gre@t Pl@ins, not to mention the easy vb scripting language that provides ammunition to accomplish just about anything ya want. In MAS, you have to learn the proprietary language. No wonder there is only about 125 MD(so I hear)! What a monopoly! To accomplish my mission, I had to create a DTS package that reads all the data each time from MAS using a ODBC data source and compares to what I already have. I created a series of packages chained together fired off by a job that's set on a schedule. To update the user-defined fields, for simplicity I created a view in SQL Server for the source and used an odbc data source, set that up in the tables and all tested ok. I created a sales order import, removed the fields I wasn't importing, made sure the fields on the data tab were in the correct order, unchecked Header info included in detail, in the records tab I added a record type of HeaderRecords and selected <> for the relation value(i have no reason why I had to do this!) It worked without it for a while but later stopped until I added it!, I then added validation for the fields order_number file type validation with the field SO1$(1,7) and the UDF_Fields I added string validation for the checkboxes of YN. Removed only the validation fields that it screamed about and so far all is working as expected in production. Unfortunately, I would not recommend MAS to anyone. Our client constantly battles unknown inconsistencies and anomalies that can't be explained. Frequent restarting of the service and fear of migration that would disrupt production. They are considering another package and you can imagine what I suggested! All this being said, I can't speak for the SQL Server version as I haven't had experience with it. But I have to believe it's easier for developing. I will end on a positive note, ProvideX appears to be rip roaring fast with the DTS package.
Good Luck All
TallOne

 

[SeattleJohn]

Too bad I didn't read this earlier but there is a package you can buy that allows direct reading and writing to MAS tables. You can check it out by calling the folks at PVX (

http://www.pvx.com).

It does NOT cause data corruption unless you use it incorrectly. I have used it for writing to MAS.

Since you're having 'anomalies' I would suggest that your integration isn't working properly. Personally I would have found an MD or someone highly knowledgable regarding MAS to do at least part of the integration. Many MDs could have easily written something that imports into SO (or likely just have it off the shelf) or someone like Big Louie could have written proper VI jobs.

John

 

[BigLouie]

The problem with IT and programmers trying back door ways to write to an accounting programs database, not just MAS but any accounting program is that few understand such things as GAAP rules and the new accounting rules put in place after Enron. You can just change things. What are you going to say to auditors when they ask for a paper trail, "there isn't one, I just changed things".

 

[BigLouie]

You can just change things" I meant, "You cannot just change things.

 

[TallOne]

Hey guys!
I do humbly appreciate the feedback more than you know. You guys are really the only contact I have outside of the help files, save paying big bucks for MD's and expending my 5 support calls per year to Sage support that I get with my channel partner license. Not to mention that most Sage support techs tend to be only slightly more knowledgeable than myself(for my questions anyways). I protect those support incidents with due dilligence. I did contact providex and they told me to contact sage! I had a gut feeling there was a read/write driver. Maybe I wasn't specific enough as it was during the beginning of my journey. And after my experience, I can imagine how much data corruption can happen with it. I can understand your concerns BigLouie! I'm a recovering accountant myself who used to work for tyc0. Obviously, I wasn't that far up the ladder....whew! <--smiles real big. I continue to develop with MAS. Although for this particular client my tasks are complete, my goal is to develop customer, AR, vendor and AP integrations to widen our range of packages we integrate into. Can any of you guys export a sample job for each and send it to me(at least ar and ap)???? Por Favor! Unlike GP, I can't seem to find examples which seem to help me out tremendously! I guess I tend to be more favorable towards GP because I've already completed these integrations into it with good understanding and enjoy the competence unlike with MAS....but I'm getting there! Anyways....thanks in advance.

TallOne

 

[BigLouie]

The one concern a lot of people have with GP is that Microsoft is losing money big time with that whole department. What happens if Microsoft decides to dump it to save money?

 

[TallOne]

IMHO, sometimes you have to lose in one area to succeed as a whole. The M$ product lines in this arena seem to be widening and getting deeper with newer versions appearing. I suspect if they are losing money it's because they are spending tons on development. The whole GP package has enjoyed improvement since M$ bought them. In my opinion, this staple product is a must just as the office package. And what's best is they all integrate together using like technologies. Unfortunately for other packages, it creates a comptetive advantage. Got a couple sample ar,ap VI jobs you can send me?

 

[BigLouie]

TallOne, you can contact me at bglouie@sbcglobal.net I know VI very well, even help resellers with it although I am an end user.