[AccessD] SQL Formatter (Was How To Display an Image from a Pervasive Database table Via Access 2007)

[Brad Marks]

Jürgen,

Thanks for your post.  I have not run into very many people who work with Pervasive databases.

We have Sage's accounting system called Business Works.  This system works fairly well, but like many purchased packages, the canned reports and the built-in report-writer are not very good.  We therefore have used the Microsoft Access report engine to build a very nice "Inquiry System" which provides "on demand" reports for our users.

I still have not been able to figure out how to display images stored in the Pervasive Database via an Access Report or Form, however.  

I put together a little query for the Pervasive table that holds the images.  When I run this query, I see "OLE Object" in the image field in each record.  The other fields, like the part number are shown properly.

This is not a serious problem, as our users can go through the "front door" and view the Part info and images via the Sage Business Works screens.  If I can figure out how to show images with Access, however, I could provide a better method for our users.

The other interesting thing is that when our users run one of the canned reports in Business Works, the report may take 1-2 minutes to run.  When they use the inquiry system that we built with Access, they can obtain the same info in less than 2 seconds (via ODBC).

Brad

PS.  We also have a purchased manufacturing system called DBA Manufacturing that uses a DBMS called Firebird.  We have had great success in pulling data from this database also with ODBC to feed Access-based reports.
   


 

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jurgen Welz
Sent: Monday, May 11, 2015 3:59 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] SQL Formatter (Was How To Display an Image from a Pervasive Database table Via Access 2007)

I use http://poorsql.com/
It does a great job of parsing the text into something much more comprehensible.

I've been using Pervasive data since Sage bought out Precision Estimating (Timberline).  Sage's most recent foray into SQL server with their construction estimating product has us running two systems; Pervasive for the estimators and SQL Server for the project managers.  Their conversion to SQL Server was a total flop.  The whole system started out as text file fields limited to 50 characters concatenated a fixed number of times to a maximum field length.  This rule was simply carried over to the Pervasive and then SQL Server versions.  The result is unweildy in production.

Pervasive itself was pretty slick and pulled data out of data stored in the file system.  There was a root file with metadata about the data files, a sub folder named 'PVData' and roughly 60 files beneath that approximately mapping to a table each.  The fun was copying estimate files and renaming the estimate without renaming the PVData folders to match.  It was not possible to mine data across multiple estimates without linking to multiple estimate backends (1 per estimate) or sequentially linking, extracting and compiling data in a aggregation database, but within a single estimate, it at least worked consistently and very quickly.

In all our time with Pervasive, we've never had image data stored there so I can help on that issue.

Ciao
Jürgen Welz
Edmonton, Alberta

> Date: Mon, 4 May 2015 17:59:08 -0400
> From: bensonforums at gmail.com
> To: accessd at databaseadvisors.com
> Subject: Re: [AccessD] How To Display an Image from a Pervasive 
> Database table Via Access 2007
> 
> Someone showed me an SQL editor that they pasted code into and it 
> showed up much better than in the Access SQL window. I wish I knew 
> what that was, I am sure it was a common editor many people use. 
> Doesn't prevent Access from messing with it afterward of course.
> 
> On Mon, May 4, 2015 at 7:33 AM, Jim Lawrence <accessd at shaw.ca> wrote:
> 
> > Ouch.
> >
> > Jim
 		 	   		  
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com