MS Access, brings back memories (both good and bad) as I started by carrier programming in it. Still indeed the best query builder I have ever found. We currently use dbForge Studio. It is good, but as anything I ever used, it somehow could never match with the MS Access one.
I agree fully about MS-Access having the best query and report builder… cannot find anything as good as this.
I still have a client using access as for report writer for my application using CubeSQL as the backend…
SSRS has an API 
So I see
Does that expose the visual query builder ?
If not then I’m back to “Client wants a visual query builder”
Its all configured/administered through a web interface so embed a HTML container and boom!
Havent tried it yet but it seems promising
That MAY have potential as long as I can do the visual query building, export and raw csv data and maybe even “reports”
I’m a neophyte when it comes to SSRS
Here’s an http server in Python 3.8 that will run a requested MS Access report and return it as a *.pdf (you’ll need to install the win32com module):
from http.server import HTTPServer, BaseHTTPRequestHandler
from socketserver import ThreadingMixIn
import cgi, mimetypes, os, threading
import pythoncom, win32com.client
ROOT_DIR = os.path.abspath(os.path.dirname(__file__))
WEB_ROOT = os.path.join(ROOT_DIR, 'www')
DB_FILE = os.path.join(WEB_ROOT, 'mydb.accdb')
USE_HTTPS = False
PORT_NO = 4444
class Handler(BaseHTTPRequestHandler):
def log_error(self, fmt, *args):
return
def log_message(self, fmt, *args):
return
def return_error(self, code, msg):
body_text = '<html><body>{}</body></html>'.format(msg)
self.send_response(code)
self.send_header('content-type', 'text/html')
self.end_headers()
self.wfile.write(body_text.encode('utf8'))
def return_file(self, fpath):
try:
if os.path.isfile(fpath):
sz = os.path.getsize(fpath)
mt = mimetypes.guess_type(fpath)
f = open(fpath, 'rb')
self.send_response(200)
if mt[0]:
self.send_header('content-type', mt[0])
self.send_header('content-length', sz)
self.end_headers()
self.wfile.write(bytes(f.read()))
f.close()
else:
self.return_error(404, 'File Not Found')
except Exception as e:
print(str(e))
def do_GET(self):
try:
s = self.path.split("/")
fpl = os.sep
fpe = fpl.join(s)
fpf = WEB_ROOT + fpe
pos = fpf.find('?')
if pos != -1: # we're not doing parameters so chop any off
fpf = fpf[0:pos]
self.return_file(fpf)
return
except Exception as e:
self.return_error(500, str(e))
return
def do_POST(self):
try:
form = cgi.FieldStorage(
fp = self.rfile,
headers = self.headers,
environ = {'REQUEST_METHOD': 'POST'}
)
report_name = form.getvalue("reportname")
out_file = os.path.join(WEB_ROOT, report_name + '.pdf')
pythoncom.CoInitialize()
a = win32com.client.Dispatch("Access.Application")
a.visible = 1
db = a.OpenCurrentDatabase(DB_FILE)
a.DoCmd.OutputTo(3, report_name, r'PDF Format (*.pdf)', out_file)
a.Quit()
self.return_file(out_file)
return
except Exception as e:
self.return_error(500, str(e))
return
class ThreadingSimpleServer(ThreadingMixIn, HTTPServer):
pass
def run():
server = ThreadingSimpleServer(('0.0.0.0', PORT_NO), Handler)
if USE_HTTPS:
import ssl
server.socket = ssl.wrap_socket(server.socket, keyfile='./key.pem', certfile='./cert.pem', server_side=True)
print('Listening on port ' + str(PORT_NO))
server.serve_forever()
if __name__ == '__main__':
run()
And here’s some demo html for requesting a report:
<html>
<head>
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta http-equiv="content-type" content="text/html;charset=utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=no">
</head>
<body>
<form action="/" method="POST">
<label for="reportname">Report Name:</label>
<input type="text" name="reportname" id="reportname" value="myreport"/>
<input type="submit" value="Submit" />
</form>
</body>
</html>
MS Access enables you to link tables from an external database using ODBC so you can still use its query builder and reports even if the data is in an alternative RDMS.
So you should be able to build an app that utilises MS Access’s query & report designer even if you don’t use it for the input screens and logic.
I’ve been doing Access development for 25 years, since the Access 2.0 days on Windows 3.1. It certainly has its good and bad points, and some versions along the way have been better than others. I find its the quickest (lower cost to client) way to build an application. With multiple users you need to use a “real” database as a back end. Native Access (Jet) databases corrupt themselves too easily. The biggest issue with Access these days is that the user and the actual back end database server need to be on the same LAN for acceptable performance.
And I do agree - it’s the best query builder I’ve seen. A great tool for learning SQL - create the query in Access and see the SQL it generates.
As a developer familiar with Access (and Xojo) I would want more information from the end users before recommending or commenting on a specific solution.
Sounds like something you would read about on page 2 of The Sun
heh
typo 
The other consideration is that they want to be able to find people to do work in the future
Access is more likely given the # of job postings we’ve seen in their area
Xojo has 0 job postings and gathered 0 replies when they looked
We’ve chatted about this, but so far we don’t know the nature of ‘the work’.
Is it a need for ‘we just thought of something, can you just…’
Or is there a specific change that needs to be made/ app that needs to be built?
Specific app(s) that need to be created
Mostly crud type data entry - except the custom query builder which is a must have
Client HAS existing apps - but no source code etc and the MDB’s are locked
Original developer passed away (long story)
So this is literally “rewrite what we have today”
The spec IS the existing apps - which I cant show you here
The work will be posted eventually as a paid contract position
But my client has asked me to ask my contacts if anyone could/would take this on ahead of posting
It may be possible to unlock the MDBs. There are several tools that claim to do this.
And if they still have MDBs they’re on a very old version of Access. This makes working on it harder as the newer versions of Access refuse to open the older files.
I could be interested in this, depends on where they’re located - I prefer to work with local clients. (I’m in Maryland, US.)
I asked client a few questions
From my client
- do you know what version of access was used to create the apps ?
- have you tried opening them to see if we might be able to just modify
them (I’ll assume you have or we wouldnt be recreating them) ?
- what is the file extension on the Access file (this will tell people if
they are locked in a way we cant modify them)
I remember having fun hacking that for Excel. Wasn’t really complicated.
The .accdb indicates the database is in the 2007 or newer format. This is good.
The password prompt indicates the developer locked it. I’m not sure why this was done, there are better ways (compiled file .accde, per-user copies) to prevent accidental (or malicious) changes. The Access password breakers may not work on the .accdb as Access actually encrypts the data. So might be looking at rebuilding it.
Exactly what they are going to be doing
Sucks but it is what it is