Ms-access vs PureBasic

[AndrewM]

I have gone back to ms-access for application development. Interesting ideas picked up in this forum have helped me create a coding style in ms-access that helped me overcome many of the limitations that I was having.

Ms-access can do OOP but if there is a bug, it is very bad at breaking on the line that caused the bug. In contrast when there is a bug in procedural code, ms-access can break at the right spot. I am reluctant to use OOP in ms-access for this reason. In this respect PB lack of OOP is not an issue. However if in ms-access you use types or structs (C# term), it is possible to simulate the data holding qualities of an object, whilst leaving the code procedural. This works and I have a large and fast application based on this idea.

Converting to PB was stymied but a few issues listed below:
[*]Less support for creating or validating SQL statements - in ms-access I can use the query designer to make queries or to reverse engineer SQL statements generated in my code. - it is easier to let the computer find the errors than find them by examining the code.
[*]ComboBoxes in ms-access can hold really long lists, such as fish species of the world (~40 K Species). Typing in a fish name is assisted by auto-complete. PureBasic Combos don't seem to be made for such long lists.
[*]Continuous form support. A continuous form is made of rows which can have text boxes, combo boxes, photos and almost any other control. They are useful for catalogs with a few rows of text and a picture. Making them in PB might be hard. I don't think Visual studio does them either.
[*]Recordsets - I work with recordsets all the time. I suppose that arrays do the same in PB but it is not as smooth. I can clone a forms recordset and then count the records or do some editing on the cloned recordset. This is fery convenient.
[*]Examples of database applications are not big or deep enough in PB
[*]Examples of html production via PB code seem to be rare or non-existant. As I completely refuse to use standard access reports - they are awful, I output all my results in html via hand crafted VBA routines.


In PBs favour.
The arrays in PB are amazing and outshine anything that ms-access has to offer.
Coding in PB is marginally faster and the IDE matches if ... end if statements. If ms-access it can be quite hard to find a missing end if.
Help file is the best around.

Wishlist.

I wish that PB had support for the embedded firebird database engine. PB + firebird could compete with ms-access. Firebird works in both embedded mode and server mode, whereas SQLite is only embedded and PostGres is really only a database server.

Better framework for getting data into and out of applications from a database, ie. databinding.

[Jagermeister]

Every developer has their particular needs, I suppose. I stick with PB for the fact that if I develop with Access, my users/clients/customers and I would need to own Access on each machine. The version would have to be modern enough to use ACCDB/E as well. You may hit the wall hard (i did) after spending your time developing a form on 64bit Access and it doesn't work with 32bit or vice versa http://social.msdn.microsoft.com/Forums ... =accessdev, and there isn't a simple Save As option for that. I also have clients with older XP systems and low RAM situations which would drag once the Access db hit ~2GB. Not to mention the access and permissions baggage sharing brings. That and Windows' behavior of automatically deleting shortcuts if a drive mapping is lost. Fun times.

I have researched how Firebird would be an awesome addition but until that's available, I'm gathering all of the third-party Access apps used at my clients' institutions and converting them to PB/Postgres for campus-wide apps, PB/SQLite for standalone apps on USB sticks, and small bridge utilities to pull from one to the other. I rarely pay for beer these days

[uwekel]

I have used Access for a long period of time (i started with version 1.1) for all kind of databases, and i have learned to hate it for a couple of reasons:

- Forms have no layout management on resize
- System font changes can destroy the form layout
- Database is limited to 2GB and will corrupt when you reach the limit
- No refactoring features (e.g. you even cannot find all used references to a query)
- Databases corrupt very often when multiple users are logged in
- Poor memory management (e.g. deleted records will not be freed, large amount of memory allocated for every new record)
- Hundreds of bugs in the Access frontend

Nowadays i use the Access database as backend container only, and operate with ODBC through VB.NET applications.

Best regards
Uwe

[captain_skank]

Funnily enough I used to do a great deal off access coding then migrated over to PB and find PB much better / flexible.

I use mariadb for the sql server so i have to use ODBC, but it works fine.

I had to code a few of my own tools to make life easier but, hey, thats coding.

I agree about limitations in combo boxes, but it was pretty easy to write my own - i needed multiple columns and hidden columns and autocomplete.

My only real gripe is that PB just doesn't have native currency funtions or a mask for the string gadget, and no native mariadb/mysql driver/commandset

I wouldsay my ERP app is much better than my access developed app, compiles to a way smaller size and in 4 years the database hasn't corrupted or crashed so I count that as a win

[AndrewM]

Thanks everyone,

I am not doubting anyone and have had similar experiences. I have never gotten on well with Visual Studio so have not gone in that direction - PB was like fresh air in comparison. So how could the PB users deepen the pool of knowledge re making great database applications?

Does anyone have anything that is as complex as say an accounting program written in PureBasic? (My application has about 50 tables and 50 forms)

Does the compile cycle still happen quickly with 20 K lines of code? (Access can flip from Design Mode to Run Mode and back in a couple of seconds) - (Does debugging work will on larger apps?)

So what is the equivalent of a recordset in PureBasic (an array of structs?)

How does one move through it (sounds obvious but someone might have a new answer)?

Do people use Jasper Reports or do as I do and write PHP-style reports in Basic, that output html?

How do you make a combo box that can manage thousands of names?

I imagine that if the forms designer improved in the next version, then hopefully much more will be written on working with databases.

PB is still my preference for making small apps.



Cheers,

Andrew M

[citystate]

regarding 'recordsets', there are several ways you can store and access them, for example

Code: Select all

Structure myrecordset
  customer_id.i
  customer_name$
  opening_balance.d
  additional_details$
EndStructure

;recordset as an array
Dim record_array.myrecordset(5000)

;recordset as a linked list
NewList record_list.myrecordset()

;recordset as a hashmap
NewMap record_map.myrecordset()

AddMapElement(record_map(),"test record")
record_map("test record")\customer_id = 1
record_map("test record")\customer_name = "joe bloggs"
record_map("test record")\opening_balance = 500.55

AddElement(record_list())
record_list()\customer_id = 223
record_list()\customer_name = "adam adma"
record_list()\opening_balance = 1000000
record_list()\additional_details = "he's a millionaire!"

record_array(0)\customer_id = 1234567
record_array(0)\customer_name = "Peter Parker"
record_array(0)\opening_balance = 0.02
record_array(0)\additional_details = "spiderman"

For i = 0 To 4999
  ;cycle through the array referencing the element by index value
  ;do stuff with record_array(i)
Next i

ForEach record_list()
  ;cycle through each list element with a foreach loop
  ;do stuff to the current element of record_list()
Next

ForEach record_map()
  ;either cycle through each map element with a foreach loop
  ;do stuff to the current element of record_map()
Next
;OR select the map element directly with its key
;do stuff to record_map("test record")

[the.weavster]

Does anyone have anything that is as complex as say an accounting program written in PureBasic? (My application has about 50 tables and 50 forms)

I've written a complete multi-company, multi-user, multi-currency (exchange rates are retrieved in real time, EGOL journals happen automatically) business system that doesn't just include financials but also sales and purchase order processing (including scheduled orders), stock control, manufacturing control (including unlimited multi-level bills of materials), production planning, import and export to Barclays and HBOS banking software, online VAT and ECSL submissions, and more... It has > 100 tables (Firebird), dozens of views and hundreds of forms. I have to confess it's written in another cross-platform basic but that was because at the time I wrote the system, even though I thought the PureBasic compiler was superior, the PureBasic IDE and Form Designer were quite primitive (no anchoring of widgets in the form designer, no autocomplete in the IDE, etc...) and so would reduce my productivity. If I were starting out now I might be more inclined to use PureBasic because the compiler's still the best and the Form Designer and IDE are perfectly capable now whilst the IDE of the alternative I did use has degenerated into a bloated kludge. I have little doubt PureBasic could do the job.

Do people use Jasper Reports or do as I do and write PHP-style reports in Basic, that output html?

I use lighttpd and an RPC server I've created in PHP. Each report is a PHP script that uses PHPExcel to create the output file (although the library is called PHPExcel it can output all sorts of other file formats including *.pdf). The server also has LibreOffice installed and I have another PHP script that can use its command line interface for when reports should be sent straight to a network printer rather than returned to the client. PureBasic's lack of a built in HTTP request command and no JSON support would obviously mean a bit more fiddling about here.

How do you make a combo box that can manage thousands of names?

Instead of a label + combo box I use a button + read-only text field, the button opens a search window which has a grid and an incremental locator that by default works with the first column of the grid or otherwise whichever column the user clicks on the heading of. As the user types in the locator the selection in the grid scrolls down, once the record they're interested in is highlighted they can just press enter. The same window is used for every pick list in the application. The columns displayed in the grid are determined by the SQL statement that fills it. My current version returns a JSON object of the record(s) selected (again that would have to be tweaked for a PureBasic version) so the form that called the search window can make use of any of the returned fields it wants to. (@Fred - JSON commands are a great way of creating structures on the fly . An asset to any programming language).

I find this arrangement quick in use too; you tab to the button, press enter, the search window appears, you start to typing your searching criteria, you see the item you want become highlighted in the grid, you press enter, you tab to the next field and so on...

I have used Access in the past but never again... far too flakey.

Another option you might want to consider is Lazarus + Free Pascal, that provides some very nice objects for working with databases.

[AndrewM]

@ the.weavster.
Almost every professional developer says that ms-access is flaky. However when you have been using it for 20 years, one develops an instinct for tiptoeing around the land mines.
I did take a look as Lazarus, it on this machine but the IDE is ugly compared (crikey - the screen font was not anti-aliased) to PB and anything MS and the compiler is much slower. It is good to know that people are using PB or recommending it in your case for complex apps.

I wonder why PB is not used more often for business applications?

[AndrewM]

Just a guess, PB would not have a concept of data-bound forms would it? Code would need to be written for feeding data into each control and then back into a database if a user changed a record? Bound forms are a lot less work but unbound forms have more possibility.

[Zach]

PB is a small language. True it is very powerful, but even still I believe it has taken quite some time to get it to the point where it is today.
I imagine the 2 biggest reasons PB is not used by many professionals / business are:

1. It has BASIC in its name. Ignorance is bliss, but assumptions are forever.
2. PB is made by primarily by one guy. There is no marketing campaign or major advertising.

I know I am not alone in wishing PB were more popular and used by a lot of knowledgeable people. We'd have a lot easier time with things like wrappers for popular frameworks and other utilities, and I'm willing to bet we'd have a pretty decent sized "Std Lib" type community with all sorts of cool code modules available for use.. But alas, it is not so There is always the future...


On the other hand, I don't think it is quite fair to compare PB to MS-Access as you have done. They were built with two different goals in mind IMHO. PureBasic is a general purpose programming language, and Access was designed specifically to work around and handle Database related things; it is highly specialized in that regard.

Apples & Oranges my both be fruit, but its still comparing Apples to Oranges.

[the.weavster]

Just a guess, PB would not have a concept of data-bound forms would it? Code would need to be written for feeding data into each control and then back into a database if a user changed a record?

No, it doesn't have data-bound widgets. It does require more work reading the data in and writing it back out (including checking if the records being edited have changed in the database in a material way in the mean time before writing back out) but I still prefer it. I suspect the endless corruptions I experienced with Access in the past was in some way linked to its data-bound widgets.

I guess if you're just using Access as a front end to MS SQL Server maybe it's more robust (is it?) but then you're not only in for Access Developer Edition you're also in for Windows Server licenses and MS SQL Server licenses. Although, given my experience of Windows Server, I'm not sure I'd be much more enthusiastic about that arrangement. This conversation is bringing back quite a lot of bad memories , I'm glad it's all in the past

Firebird or PostgreSQL can be deployed on an Ubuntu server, it's all very robust and completely free.

[AndrewM]

Using SQL-Server as a back end probably comes at a cost. Two things that I can think of are having to use surrogate keys on all tables, which can kill performance in some cases, and that some of the data bound controls do not work, when ms-access is set to use ansi compatible SQL/SQL Server SQL. That was enough to put me off. I am not a SQL Server I have played with SharpDevelop (= open source visual studio), which I like but it does not support an open source database.

I like the records to structs idea as it means that I can validate the data in a struct before sending it back to the database.