Which is the most prevalent database used by APL+Win?

Discussions and downloads of using APL+Win with databases.

Which is the most prevalent database used by APL+Win?

Postby Ajay Askoolum » July 22nd, 2008, 5:34 pm

Davin Church has made available some core ADO functions for accessing relational databases using ADO.

It is unclear which database apl+win developers are using: is it safe to assume that it is Microsoft Access?
Ajay Askoolum
 
Posts: 884
Joined: February 22nd, 2007, 2:16 am
Location: United Kingdom

Re: Which is the most prevalent database used by APL+Win?

Postby Davin Church » July 22nd, 2008, 6:59 pm

Ajay Askoolum wrote:It is unclear which database apl+win developers are using: is it safe to assume that it is Microsoft Access?

That's not really a safe assumption. Certainly Access is easy to deal with and there are many people that use it. But I've also heard from people using SQL Server / SQL Server Express, Oracle, Paradox, and other more esoteric systems. My personal favorite is Pervasive SQL.
Davin Church
 
Posts: 651
Joined: February 24th, 2007, 1:46 am

ADO & Databases

Postby Garth Hutchinson » July 23rd, 2008, 7:43 am

Mainly MS Access (both 97 and 2000) but also Paradox and dBase; am trying to find a way to compact MS Access from APL -- think I'll have to write a .DLL in C++ or C# --- SEE NOTES BELOW FROM AJAY + MY NOTE
Last edited by Garth Hutchinson on July 24th, 2008, 1:30 pm, edited 1 time in total.
Garth Hutchinson
 

Postby Ajay Askoolum » July 23rd, 2008, 7:59 am

You might use 1. Either JRO (Jet Replication Object) or 2. ODBC for compaction if you do not have the Access application or simply switch the compact on close option on if you do have Access.

Right now, I do not have all the details of these ... more later.
Ajay Askoolum
 
Posts: 884
Joined: February 22nd, 2007, 2:16 am
Location: United Kingdom

Postby Ajay Askoolum » July 23rd, 2008, 2:52 pm

I attach a zip file that contains a version 8.0 workspace with the backup functions and a PDF that lists the same functions (in case you cannot load the WS).

The syntax of each function is as follows:

1. CompactODBC 'drive:\path\dbname.MDB'
2. CompactJET 'drive:\path\dbname.MDB'
3. CompactOnClose 'drive:\path\dbname.MDB'

1. above requires an entry into the ADF file; see the PDF for details.
3. above requires the Access application installed on your PC: 1 & 2 do not but you do need the JET 4.0 provider.

Remember to backup your database(s) before using these function(s).


Why can't I attach ZIP FILES???
Attachments
Access Backup.pdf
(54.27 KiB) Downloaded 633 times
ACCESS BACKUP.w3
(9.17 KiB) Downloaded 616 times
Ajay Askoolum
 
Posts: 884
Joined: February 22nd, 2007, 2:16 am
Location: United Kingdom

Postby Tech Support » July 24th, 2008, 12:35 am

Ajay Askoolum wrote:Why can't I attach ZIP FILES???


Zip files are acceptable attachments. Maybe it's the size of your zip file. What is its size?
Tech Support
 
Posts: 1230
Joined: February 10th, 2007, 7:33 am
Location: Rockville, MD

Postby Ajay Askoolum » July 24th, 2008, 1:37 am

I tried to attach the above pdf & w3 as a zip, so much less than 64KB.
Ajay Askoolum
 
Posts: 884
Joined: February 22nd, 2007, 2:16 am
Location: United Kingdom

ADO Compact MS Access Database

Postby Garth Hutchinson » July 24th, 2008, 1:28 pm

The solution provided by Ajay above works well.

One thing to note if you are in situation such as mine where I repeatedly want to compact the database during a single run as it approaches the critical size. It is necessary to completely close the database. This means that the .ldb file disappears. Note, then the following: APL+WIN (or at least my old version) seems to cause the equivalent a sticky open in this situation.

Simply deleting the connection does not close the file. Using the same connectID for a new DB does close the file. It is best to close the file explicitly:

connectID []WI 'XClose'
connectID []WI 'Delete'

do what you want (copy, rename, compact, etc)

then reconnect using ADOConnect and recreate any commandID etc.
Garth Hutchinson
 

Postby Ajay Askoolum » July 24th, 2008, 3:16 pm

Hi Garth,
Glad to hear that some of the solutions were appropriate.

There is some latency with the .LDB file that may get in the way now and then; that is a Microsoft 'design feature' beyond the control of the APL+Win environment.

I would strongly recommend that you move the Access databases to SQL Server Express, for the following reasons:

1.SQL Server Express is free, offers a 4GB limit on database sizes and is much better at handling concurrent usage than Access.

2. Management Studio is also a free download that provides all the database management tools that you might want, including the defragmentation of tables and indices whilst the database is in use.

3. You can backup the database while it is in use; but you'd need exclusive access to restore it to a previous state from a backup.

4. Access is excellent in that it gives you a GUI for interrogating/maintaining the data tables and queries.

5. You lose nothing: the same Access GUI can hookup to the SQL Server database and you you have identical facilities-even the SQL dialect within Access changes automatically to SQL Server. You suddenly find that the SQL Server Books on line documentation (free download) apply within the Access GUI. I am tallking about Access Data Projects (uses extension ADP instead of MDB).

6. You can maintain your constraints, triggers and stored procedures for the SQL Server database from within Access (natively, Access does not support triggers or stored procedures for MDB files--only for ADP files).

7. You have a choice of whether to use the Access drivers/OLE DB providers and hookup to the Access ADP or use native clients to hookup diectly to the SQL Server database.

8. There are free tools for upgrading Access MDB files to SQL Server.

APL+Win is equally at home using MDB or SQL server databases.
Ajay Askoolum
 
Posts: 884
Joined: February 22nd, 2007, 2:16 am
Location: United Kingdom

Atlternatives to MS Access

Postby Garth Hutchinson » July 26th, 2008, 9:42 am

Ajay: I agree with all the reasons you have given as well as the fact that MS Access is notoriously bad for handling multiple updates to records, particularly where the records increase in length each time, as is my case. I have used some old mainframe BISAM/BDAM techniques to alleviate this but as the records needing update are (somewhat) random and arbitrary they cannot remove the problem. Unfortunately, I am constrained by other factors to using MS Access and that seamlessly between versions, or I would have used MySQL or SQL Server both of which I have on my machines.

That I have not used these with APL+Win has been more a question of what has been dictated by need than of the considerations you have expressed so well. I could have mentioned that in the dim consulting past (2002) I used an old version of LinkPro to access native bases on an AS400 system ... but that is hardly relevant today!

(However, the stated technique for compaction does work.)
Garth Hutchinson
 


Return to APL+Win and Databases

Who is online

Users browsing this forum: No registered users and 3 guests

cron