Adding Fields/Tables (Macola 7.6 P.SQL)

[rustylee]

Recently we purchased the Flexibility module and a whole new world has opened up. It has caused me to go back over some of the things Macola has not been able to provide as a standard package.

I'm sure one thing that all users of any ERP system come across is "where do I put this piece of information?" Macola does have user fields for such things. However, these can get used up fast in a shop like ours.

I am considering adding some tables to the Macola database. But before I do, I would like to know if there are any pitfalls to doing such a thing.

 

[dgillz]

There are huge pitfalls to doing this in a P.SQL environment.

You could use a tool such as Smithware DDF Builder to add new field to an existing table, or to add new tables to the Macola DDFs, however as soon as a new Macola service pack comes along, the upgrade will change your DDFs and yuo will have to redo all the work.

For people that have these types of issues, the real answer is to upgrade to Macola SQL. SQL by its nature gives you the ability to add additional tables without the problems inherent in a Btrieve/Pervasive environment.

As an example, I just recently I added a table called "Item_Picture" to a Macola SQL database so the user could actually store a .JPG of the item in a SQL database, and then access the picture in a crystal report. This table will stay there forever, even throught Macola upgrades, unless the user does some manual process to get rid of it.

If you would like to know more about Macola Progression SQL please let me know. Software Training and Support for Macola, Crystal Reports and Goldmine
714-348-0964
dgilsdorf@mchsi.com

 

[vbajock]

Rusty, I have been programming Flexibility for the past three years, both MSSQL and PSQL. You can take the approach you described, but you will have to get deeply into Pervasive's SDK and their implementation of OLEDB. They are not a real friendly company when it comes to free tech support, so it costs bucks, in addition to the potential pitfalls Don described. If you are a programmer with a lot of time and you are working at an advanced level, you can make it work. Myself, being a self-employeed programmer, I have to get apps rolled out quick, and they also have to be robust enough to survive upgrades. Otherwise, I have to stand under a bridge with a cardboard sign and ask people for money. Since I don't like being poor, I use Access databases that are ODBC-linked into the PSQL database, and create additional tables in Access that share the same key structure as the related Macola tables, and I then add a reference to ADO 2.x in Macola's VBA (Flexibility) to make the Access databases available within a Macola context, via ADO connections . Using Macola's Screen designer in conjuction with these tools , it is not very difficult to put data fields up on the Macola screen, collect data, etc, and store them in an Access (or SQL server) database. In addition, you can also use Office objects, which use MDB's as the native format, to send emails from with Macola, create Excell Spreadsheets, print Access reports, etc, and make your boss think you are a friggin genius.

MS Sql is a superior environment, but IT budgets are very tight right now, and anyone who tells the boss that he needs a bunch of money for an upgrade is going to have a tough time selling it. I've been doing a lot of PSQL jobs lately because the bosses would rather pay a couple of grand for an app that solves an immediate problem than spend a bunch on an upgrade, and Flexibilty allows me to deliver and avoid that trip to the I-45 bridge.

 

[rustylee]

Excellent advice from both of you. The P.SQL environment for Macola does hinder things a bit, and going to some other database format for storing supplementary data would be the best bet.

I like vbajock's idea if planting table links into an Access database. I use ADO and ODBC connections to access data now. I am doing this to make porting over to MS SQL much simpler. I hope to only have to change the connection string variable in our VBA code. Since I use basic SQL statements to access data, I won't be bound by any provider's object constraints. Later, when the upgrade happens I can import that supplementary Access tables right into MS SQL.