Fox News: Facebook Messenger now lets you unsend your messages, but you have to act within 10 minutes.
Just yet ANOTHER reason to NEVER use Microsoft Access for any reason:
Microsoft had failed to patch the flaw in due time and they decided to make the issue public, so users and companies could take actions to protect themselves against any exploitation attempts.
It looks like Microsoft is pretty serious about NOT caring about Microsoft Access, but this also includes some products like Active Directory, Microsoft Project and others.
This is an example or a misleading headline. What is says is:
38 Percent of People Still Pirate Music, Study Finds.
What it is IMPLYING is that only 62 percent of America pays for music. That is clearly not the correct stat.
What would be a better stat?
38 Percent of People Still Pirate SOME OF THIER Music, Study Finds.
God I hate DRM infested bullshit like Apple, Mac bullshit. I’d gladly rather chew on glass. Than to be able to backup my music from machine1 and then to machine2 but not being able to copy your media from machine2 to machine3? I would rather be dead.
I recently read that Congress has now passed a bill that allows the US govt to shoot down drones that are in the wrong area or doing the wrong thing.
I’m not sure how I feel about this. I still think of drones as a curiosity.. not necessarily the first thing I am going to go out and pursue. For example, I would much rather start hoarding Geographical data from sources like Tiger and integrate with other datasets.
I’ve worked with Geographical data on many occasions. Sql Server 2005 made it a LOT easier to do these types of calculations.
In 2006 I was working for Starbucks and I was brought in to work on their Sql Server database infrastructure. It was an amazing project. The girls were incredible and they all wanted to teach me how to make an espresso.
I swear I was drinking about twenty shots a day that month.
At Starbucks we had one main table that stored latitude and longitude information. There were about forty thousand geographic locations. Perhaps fifteen thousand were physical stores and the rest were either in the planning stages or something along those lines.
Basically, Starbucks operations where I was working they had no way to correlate that the Redmond stores were all supplied from the Bellevue Warehouse. They didn’t really believe in warehouses, every store was basically a mini warehouse where the products would ebb and flow.
It just blows my mind that any company would ever have fifteen thousand locations and not have a real physical database infrastructure that correlated the Redmond Stores together with the Bellevue stores into a region or an area.
We basically had been tasked with triangulating which stores were the closest to each other and which stores were not in the vincinity. There was only one major problem with the algorithm. When you have forty thousand locations trying to calculate against the same forty thousand locations.. the number of calculations I think it is 160 billion operations? I’m sure I am exaggerating a little bit.. but I’m positive that it was more than a billion.
Every time that an employee would open their dashboard this important query took at least sixty seconds to complete. I pointed it out to them and laughed. I said
Your math is right, your database is performing extremely well for what you are asking of it.
But that didn’t change the sixty seconds that it took every employee in the department on their startup page. I swear it was about five minutes of coding before I had a solution. I called this solution the ‘Magic Circle’. There was some business rule that was not being accounted for properly. It was some sort of rule that said any stores more than 300 miles apart from each other need not be considered as their nearest store.
So when I got there the query looked like this (don’t worry this is not REALLY a user defined function)
Select distancecalc(s1.latitude, s1.longitude, s2.latitude, s2.longitude)
From storelocations s1
Inner join storelocations s2
On distancecalc(s1.latitude, s1.longitude, s2.latitude, s2.longitude) < 300
The query worked it did what they wanted.. but it was at least a hundred times slower than it needed to be. Again this whole month involved five minutes of coding and then the rest of the month was simple arguing that the magic circle concept was fundamentally solid.
So what was the magic circle? Looking back it was really a square of two points latitude and longitude deviation was the approximation for 300 miles distance.
So without further ado… Here was my proposal
Select distancecalc(s1.latitude, s1.longitude, s2.latitude, s2.longitude)
From storelocations s1
Inner join storelocations s2
On s1.latitude between s2.latitude-2 and s2.latitude+2
And s1.longitude between s2.longitude-2 and s2.longitude+2
distancecalc(s1.latitude, s1.longitude, s2.latitude, s2.longitude) < 300
This basically stopped the calculation from doing a billion operations every minute and it turned the execution time to under one second. Five minutes of coding and one month of arguing with a dozen people about math. Good times.
Today Sql Server 2005 or 2008 makes the calculations simpler for sure. This project was literally the only time I had to use geometry in my career as a programmer.
This looks cool. Square, the payment provider that integrates with WooCommerce now offers a payment plan or financing to users. If I could sell a website for about a grand and then allow people to make payments? That would be amazing. I will have to look at this.. but I’m curious what services a traditional bank could provide that would be similar.. but with local branches? That is why I prefer brick and mortar a lot of times.
Having a location for me to visit is just necessary for some types of businesses. I wish that square would tie these services to a local bank to make it easier to manage.
Does square even allow you to have multiple employees setup in different roles? If I had an accountant and a clerk to run then cash register could I even segment the offerings of square to separate the duties of my courtesy clerk to be allowed to do certain things and then it would allow my accountant to do other activities?
That is how every service should get modeled after. Point and click only goes so far when you are trying to run a business.
by Philipp Stiefel, originally published May 15th, 2018
Access Logo copyright by Microsoft, background photo by Philipp Stiefel, all rights reserved.
Access Data Projects were a very powerful project type in Microsoft Access. Unfortunately, they have become deprecated. To say goodbye and bid them farewell I wrote down the story behind the ADPs.
They might be gone now, but their legacy is still present in Access and can be used to enhance your client-server-application.
Access 2000 – Birth of the Access Data Project
In summer 1999 Microsoft released one of the biggest milestones in the whole history of Microsoft Access. Access 2000 came along with a couple of very significant changes from the previous version.
Some of these changes were received with mixed responses from the community. Hardly anyone today still remembers the rather ill-fated Data Access Pages as a first attempt to integrate web development into Access. The idea to make ADO (ActiveX Data Objects) the default data access library in Access 2000 instead of the proven and reliable DAO (Data Access Objects) was questionable at best.
But there were other changes that certainly were unanimously received positively. The improvements to the VBA programming environment and the upgrade of the VBA language to version 6, with quite a lot new functionality, was huge step forward at the time.
The biggest change however, was the introduction of a whole new Access project type. The Access Data Project, or short ADP. This project type completely replaced the local Jet-Engine database Access used to use with a powerful Microsoft SQL Server database for data storage.
Access already was a huge success in desktop database market. Even though Access works surprisingly well even in small workgroup scenarios, desktop databases by design have severe limitations when used in a multi-user-scenario. So, overcoming these limitations by replacing the database engine was a very logical idea.
The first steps had been made with previous versions already. The features of Linked-Tables to an ODBC-Data-Source and ODBCDirect-Workspaces gave a tremendous amount of power to the humble Access developer. But properly using these features required a significant amount of knowledge as well as additional tools to work with the backend database.
ADP – Strengths and Weaknesses
The idea of the Access-Team at Microsoft, to integrate the powerful SQL Server database management system into Access, including a toolset to work with its objects was ingenious.
This would eliminate the need for any external tools. The direct incorporation of SQL Server into Access would pair the ease of Access development with the power of an enterprise DBMS. – It was a grand vision indeed.
The technological foundation of the ADPs was using the ADO data access library on top of OleDB connectivity instead of DAO with ODBC. DAO is the “native” data access library for the JET-Engine and works great for reading and writing data via ODBC. However, for structural changes to your SQL database with DAO/ODBC you need to resort to raw DDL-SQL. In contrast to that ADO comes with special ADOX (X for Extensibility) library that encapsulates the management of the database structure. So, in theory this would have allowed to manage every aspect of your backend database right from the Access environment.
Parts of this worked really well in practice. Seeing a list of all the tables, views and stored procedures in the backend database right in your Access development environment was very convenient. Entering a SQL String to query the SQL Server database into the Recordsource property of a form or the Rowsource property of a combo- or listbox was a huge improvement over linked ODBC tables. Being able to bind an Access form to the results of a stored procedure was amazing. The graphical query (view) designer was pretty helpful as well, but it leads directly to the somewhat problematic areas of the ADP project type.
In contrast to these great features, other necessary functionality had some significant flaws. While it was possible to create simple queries in the visual designer, it lacked usability when you needed to use built-in SQL Server functions in your views or needed to write more complex queries.
The most powerful tool to write complex queries is the SQL language. And there is no way around Transact-SQL (T-SQL, MS-SQL-Servers dialect of the SQL language) to create stored procedures on the server. T-SQL is, of course, a text-based language. Unfortunately, the text-based SQL editor of Access was (and is to this day) rather simplistic and lacked a huge number of features required to efficiently write SQL queries.
While the SQL editor in ADP projects was a slightly enhanced compared to the standard Access query editor, it was still lacking on many levels. It was marginally improved (e.g. with syntax highlighting) in subsequent versions of Access, but it was never comparable to the Query Analyzer application that came with SQL Server at the time. As a consequence, developers either used external tools, such as Query Analyzer, to write their queries or they really struggled with the tools available inside Access.
And that was it in regard to the need for external tools.
(Actually, when I took the screenshots for this text, I opened these design tools in Access for the first time in many years. – I work with ADPs in Access almost every business day!)
Technology Impedance Mismatch
One of the most amazing features of the Jet-Database-Engine used in classic Access applications, it the Jet Expression Service. The Jet Expression Service is part of the Jet-Engine. It resolves expressions you use in your queries. It figures out if your expression is fully supported by the database engine itself and can be passed to it directly, or if it references things from your frontend application, such as controls in forms, built-in VBA functions, or custom VBA functions you implemented yourself. Values from these controls or calls to these functions are then made available to database engine during execution of the query.
While this feature can be easily abused and might have a significant negative impact on the performance of queries, it is a feature that contributes massively to the ease of use of Access and its power even for novice developers. This is only possible, because frontend application and query execution all run inside the same process on the same computer.
When using any SQL-Server backend database, the query will be executed by the SQL Server process. That process is always another process that the one running your frontend application, and most of the time it will run on a different computer that might be virtually anywhere in the world. Now, it is impossible to incorporate local elements of the frontend application into the execution of the query.
(Theoretically, it would be technically possible, but the performance implications of distributed execution on multiple machines would be catastrophic. This is (part of) the reason why some queries on linked ODBC tables are dreadfully slow.)
So, with the ADP and its SQL-Backend we lost a very powerful Access feature here. Of course, Microsoft identified this problem early on and added Input Parameters to forms and reports. With these input parameters you were able to reference local controls or functions whose values would be passed on to defined parameters of stored procedures or user defined functions on the SQL Server. However, they were quite a bit clunky to use and still had quite a few limitations compared to expressions in classic JET queries. Most of all, they require a solid knowledge and understanding of the distributed architecture of client-server-database-applications. – Something that many Access users did not possess right away.
And there went the ease of use.
This is not at all the fault of the Access-Team. They did an excellent job of incorporating useful functionality into Access to mitigate this impedance mismatch in technologies. It is simply not possible to make some of the amazing features of Access desktop databases available in a distributed client-server-application.
Component Version Mismatch
The final nail in the coffin of the ADP was a (frequent) version mismatch between Access and SQL Server.
When the Access ADP technology was developed for Access 2000, it was targeting Microsoft SQL Server 7.0, which was the current version of SQL Server at the time. But shortly after Access 2000 was released to the general public, the new SQL Server 2000 was released. It included a couple of new features, most notably user defined functions, that were not supported by ADPs in Access 2000. Less significant in general, but more disruptive to Access ADPs were several smaller changes to datatypes and design time behavior of some components.
This happened again and again and again in the history of Access ADPs and SQL Server. Of course, the Access-Team upgraded the design time features of ADPs to the current version of SQL-Server. But whenever a new Access version was released with such enhancements, a new version of SQL-Sever followed soon after, and once again was not (fully) supported by Access. There were Service Releases for Access to adapt, but still Access dragged behind most of the time.
This usually was not a big problem for ADP applications at runtime. For these the SQL Server updates were hardly noticeable because in that regard SQL Server had an excellent history of downward compatibility. So, it did hardly affected any ADP solution already in production. Almost all of them continued to run with SQL Server 2000 (and later versions) without any changes required to them.
However, the design time tools in Access were suffering from this, suffering badly. It is obvious that you could not use new SQL-Server features that were not supported in your version of Access. But with some updates the Access design tools for SQL Server objects stopped working altogether.
ADP in Access 2002 to Access 2010
After their initial introduction in Access 2000, ADPs evolved only very slowly. Support for User Defined Function was added in Access 2002 (or 2003, can’t remember anymore). Other than that, there were no major new enhancements in the following versions. So, there was very little progress being made beyond keeping the basic functionality working with new versions of SQL-Server.
The demise of ADPs
Finally, with Access 2013, Microsoft discontinued ADPs and removed support for them from Access.
I do not have any inside information on the reasoning and the process behind this decision. From my outside point of view, I guess the rationale was a combination of three things.
- The adoption of ADPs was not as strong as Microsoft had hoped. Mainly due to the flaws and problems outlined above.
- The pressure of keeping up with the pretty fast SQL-Server development was probably pretty intense for the Access-Team.
- The OleDb technology was deprecated and support for it was to be discontinued. This would have removed the very basis ADPs were built on. (This decision was reversed in October 2017 and there is a new OleDb-Provider for SQL-Server available now.)
What could have been
After writing the previous paragraphs about the ADP technology and its history, I cannot refrain from thinking about what could have been if things had been done differently.
Hindsight vision is always 20/20 and from the outside seeing only results, it is hardly possible to guess which challenges existed for the Access-Team during the development and maintenance of the ADP functionality in Access. So, take the following paragraphs with a grain of salt. It’s always easy to know better after the fact, all the more without having to take the risk and facing the challenges yourself.
In the beginning – Access 2000
When designing ADPs, the Access-Team did a very sensible thing. Instead of reinventing the wheel (the graphical designers) in Access, they reused many of the graphical components from Enterprise Manager, the primary administrative client tool for SQL Server.
When integrating these components into Access it seems there was too little attention concentrated on the upgrading of those. They should have planned for a simple way to upgrade these components independently of Access (e.g. with the SQL Server client installation). That would have taken the burden of keeping up with SQL-Server from the Access-Team.
However, with SQL Server 2005 this would have fallen apart anyway. Enterprise Manager was replaced by SQL Server Management Studio and all those reusable components would not have been updated any more by the SQL Server team.
So, even in hindsight I don’t see any easy options for significant improvements when ADPs first came to light.
At the End of ADPs – Access 2013
In my opinion, there would have been another option than killing-off ADPs altogether with Access 2013. The Access-Team could have embraced the fact that most serious developers working with ADPs were using external tools for the development work anyway. So, it could haven been a viable option just to drop all those SQL-Server design tools from Access and keep the skeletal core functionality intact as it was.
This would have removed the pressure of keeping up with SQL-Server without loosing the runtime capabilities of ADPs. – If you look closely, this is almost what they did. – Of course, all the design time features of ADPs are not available in the AccDB file format. But you can do almost all the things that were essential to ADPs with VBA code. E.g. you can still bind a form to an ADODB.Recordset created from an SQL-Server stored procedure via an OleDb-Connection. Almost all of it is still there!
What is essentially missing, is just setting the main connection (CurrentProject.Connection) to an OleDb-Connection to SQL-Server and the Recordset-Property of a report. It should not have meant too much effort to continue to maintain these two tiny features.
Now – Access 2019 and onwards
SQL Server connectivity and integration into Access is still a huge topic. There are several popular suggestions on user voice about this.
This functionality will be used only by small percentage of the Access users. Nevertheless, just having the option to scale an Access application up to SQL-Server it is definitely a factor when deciding to use Access for many kinds of projects. So, improving SQL Server connectivity in Access should be a priority.
Bringing back ADPs in all their (tainted) glory, is completely out of the question. The Access-Team is rather small nowadays and the effort required to do this is beyond their capabilities.
However, they could focus on improving the areas where the current options for SQL-Server integration is lacking.
In my opinion there are two key issues that need to be fixed/improved.
- When using ODBC to connect to SQL-Server, ODBC-Pass-Through-Query need to be improved. It should be possible to get writeable Recordsets from these queries and it should be possible to get the return values or output parameters of stored procs (without crude workarounds), as it was with ODBCDirect in the past.
- The option to bind ADODB.Recordsets to reports should be added and the binding to forms should be improved. In that context, automatic detection of UniqueTable and ResyncCommand should be improved and the issues (errors and crashes) with using the F5- or CTRL+F9-key to refresh/requery open forms should be fixed.
These should be manageable tasks and would hugely improve the possibilities of Access with SQL Server.
I currently work on an ADP to AccDB application migration project for one of my key customers. Several similar migration projects have been requested by other customers and will be implemented in next couple of months.
I will probably write on this website about my experiences with these migrations and publish general information on using Access with SQL-Server backends. If you are interested in these topics you should subscribe to my newsletter at the bottom of this page.
My schedule is usually pretty busy for months in advance. Nevertheless, if you want to hire me and my team for your migration project, please contact me.
You can use Microsoft Access as a front end to MySQL by linking tables within your Microsoft Access database to tables that exist within your MySQL database. When a query is requested on a table within Access, ODBC is used to execute the queries on the MySQL database.
To create a linked table:
- Open the Access database that you want to link to MySQL.
- On the tab, choose .
- In the
Get External Datadialog box that appears, choose Link to the data source by creating a linked table and click .
- The Select Data Source dialog box appears; it lists the defined data sources for any ODBC drivers installed on your computer. Click either the File Data Source or Machine Data Source tab, and then double-click the Connector/ODBC DSN you want to link your table to. To define a new DSN for Connector/ODBC instead, click and follow the instructions in Section 5.3, “Configuring a Connector/ODBC DSN on Windows”; double click the new DSN after it has been created.
If the ODBC data source that you selected requires you to log in, enter your login ID and password (additional information might also be required), and then click .
- Microsoft Access connects to the MySQL server and displays the list of tables that you can link to. Choose the tables you want to link to (or click Notes
), and then click .
- If no tables show up for you to select, it might be because you did not choose the Database to connect to (see Section 5.3, “Configuring a Connector/ODBC DSN on Windows” for details), or choose a Database when you log in to the DSN. to connect to when you defined or logged in to the DSN. Reconfigure the DSN and specify the
- If your database on Access already has a table with the same name as the one you are linking to, Access will append a number to the name of the new linked table.
- If Microsoft Access is unable to determine the unique record identifier for a table automatically, it will ask you to choose a column (or a combination of columns) to be used to uniquely identify each row from the source table. Select the column[s] to use and click .
Once the process has been completed, you can build interfaces and queries to the linked tables just as you would for any Access database.
Use the following procedure to view links or to refresh them when the structures of the linked tables have changed.
To view or refresh links:
- Open the database that contains links to MySQL tables.
- On the tab, choose .
- The Linked Table Manager appears. Select the check box for the tables whose links you want to refresh. Click
If the ODBC data source requires you to log in, enter your login ID and password (additional information might also be required), and then click .
to refresh the links.
Microsoft Access confirms a successful refresh or, if the tables are not found, returns an error message, in which case you should update the links with the steps below.
To change the path for a set of linked tables (for pictures of the GUI dialog boxes involved, see the instructions above for linking tables and refreshing links) :
- Open the database that contains the linked tables.
- On the tab, choose .
- In the Always Prompt For A New Location check box. that appears, select the
- Select the check box for the tables whose links you want to change, and then click .
- The Select Data Source dialog box appears. Select the new DSN and database with it.
The primary benefit(s) of an Access Data Project is that (a) you get the stability, scalability, and security of a real SQL database, while (b) still using the well-known MS Access front-end and programming.
(a) As I’m sure you are aware, Access MDB files demonstrate a lot of problems when shared by multiple users at the same time, or when growing too large–these are limitations that SQL Server doesn’t have. Beyond that, you can secure your data and prevent corruption or loss more easily on the SQL Server.
(b) If you’ve already got the front-end built in MS Access, it’s fairly easy to let the program upload your data to the server. End-users get to continue using the same interface that they were before–it feels no different to them (except perhaps that it’s faster!). And it doesn’t require a ton of time starting the program from scratch in a .NET language (not to mention distributing/installing the new app across all your client systems).
Are there any downsides? Well, maybe a couple: (1) Clients still need the Access Runtime to use the software, and must also have network access to the SQL Server. (2) The automated conversion is not 100%–when you run the wizard to upload the data from Access, it will do its best to convert the Access-specific SQL queries to SQL Server/T-SQL ones; if some queries fail after the conversion, you will probably need to re-write them with correct T-SQL syntax.
There is no reason whatsoever to port an existing working Access application to an ADP front end. Simply upsize your data and use ODBC linked tables in place of the linked tables that used to link to your Jet/ACE back end.
If despite this, you decide to port to ADP, though, keep in mind that Microsoft has been deprecating ADPs for several years now and it’s not clear if the ADP has a future or not. A2007 had no enhancements to ADPs and I believe A2010 will not, either. There is some talk that the Access development team wants to revive ADPs in the version after A2010, but that’s mostly speculation based on the team’s solicitation of comments from users of SQL Server about how Access could be improved to work with it in future versions.
I was surprised when I read this:
There are open-source scalable search solutions – like Elasticsearch and Solr – that work with WordPress to optimize search indexes. They can deliver near-instant search results across millions, even billions, of source documents.
I really don’t think that I’ll be using WordPress to catalog Millions or Billions of Documents. I’m guessing that I’ll be using either SharePoint (the free edition) or SQL Server ‘Full Text Search’. Maybe I’ll try using WordPress for some of these purposes, on a Virtual Machine and then I’ll see how it responds.
I love WordPress. But I do love SQL Server a LOT more.
I almost spilled coffee when I read this:
“We have an app called Safari,” Cook added. “Safari is the app for you if you want to look at anything that’s on the free and open Internet that’s not on our app store.”
Right. Safari for the ‘Free and Open Internet’. Keep telling yourself that.. and your $1000 price for admission to your ‘walled garden’.
I’m disgusted by people like this. Safari doesn’t allow you to use the ‘Free and Open Internet’. Safari won’t even run on Linux, Windows.. Nothing other than $3000 laptops and $1000 phones. Cute, NO THANKS!