More Programming Fun ...

[Calilasseia]

I'm about to install MySQL to go with my PHP installation. Promises to be fun. Then I have the fun of wading through the reference manual.

[a non-amos]

Please do not overlook the massive amount of help available online (for free).

In my case I chose MS SQL over MySQL because of the better error checking (better for what I was doing), but I discovered other advantages in seamless user authentication.  My company had already paid for an enterprise-wide license for MS SQL, so to me they were both zero cost.

The 500 pound gorilla in this room is the obvious question why in the hell would I need to make a new database.  Over the years I have come to the conclusion that our database staff cannot find their own butt cheeks with both hands.  We had specific and immediate needs, so I filed a request and then went back to school to learn SQL with the intention of circumventing them for the better good.  We need at least one system that works.

Further education followed by a bit of trial and error in the real world does not really meet the immediate and pressing need for accurate and comprehensive information that we needed last year, but it does provide a light at the end of the tunnel.

About 6 months after I had the new system up and running I received an interesting reply from our illustrious database personnel.  They told me that my previous request was not physically possible, and my having asked for it showed my inexperience and ignorance.  I took some joy in circulating that among a few friends and coworkers.

- A

[Calilasseia]

MySQL is huge. The reference manual for it is a whopping 4,000 pages. But already, it's revealed that there are some nice features I can press into service for a taxonomic database, that will help me keep as much of it in domain-key normal form as possible. Which I seek to do because it will make referential integrity checks a lot easier.

[a non-amos]

I'm having my share of programming fun over here, too.

I am using Access as a front end interface to MS SQL Server.  I found a way to upload an Access table from the user's computer into a local temporary table on the server.  This is a query that executes on the user's computer.

Side note:  in MS SQL Server a local temporary table is only visible to the user who created it.  After the end of the user's session this table is deleted.  If created within a stored procedure, it is deleted when the procedure terminates.  This is extremely helpful in multi-user systems.  If a stored procedure needs to make a temporary table, but two people are using the same stored procedure, local temporary tables do not interfere with each other and they are automatically deleted when they are no longer needed.  Local temporary tables can also be used as the equivalent of a print job in a queue.  One user's job will not interfere with another user's job, and there will be no old rubbish tables cluttering up the schema.

It worked fine in Access 2010.  It also works in Access 2016.  So, what's the problem?

Access 2016 will not allow me to save the query; it only gives me an error message.  Even in the old projects developed in Access 2010, this functions perfectly but I cannot open the query and save it unchanged. 

Now I have to use this trick for a new project.  I cannot even copy-paste the functioning SQL code from the query in the old project; Access will not allow me to save it.

My best guess is that Access 2016 added some error checking when queries are saved, and what it identifies as an error is anything but.

[Calilasseia]

That sounds to me as if you're afflicted by the well-documented problem known as "Microsoft not bothering to adhere to standards set by someone else, and not even bothering to adhere to their own previous standards in later versions". Which afflicts a good many applications within which such things as VBA scripts and SQL queries worked fine, until the new version came along.

If you have the manual (and if you do, it'll be huge), then it's worth checking what sort of changes have been made to the handling of SELECT queries in particular, as these are always ripe for abuse in Microsoft's hands.