In this section of the MSDN Library, you’ll find technical articles that demonstrate how to build and deploy complex Access 2003 runtime-based solutions, automate Access from another application; use the new XML features in Access 2003, and even how you can animate Access 2003. You can use the TOC to your left to navigate through…
What is the SQL Server equivalent to Access IIf() function? Access SQL IIf([ResultValue]>=1, [Result Value], Null) SQL Server CASE WHEN [Result Value]>=1 THEN [Result Value] ELSE NULL END
SQL Server does not allow for a dynamic set, which can be problematic if you need to pivot based on the data that comes from rows and you can have various values in the column. In general, you need a stored procedure and dynamic SQL in order to handle the dynamic pivots. This requires uses…
Microsoft Access saves information under the following file formats: File Format Extension Access Project (2007, 2010) .adp Access Blank Project Template (2007, 2010) .adn Access Database (2007, 2010, 2013, 2016) .accdb Access Record-Locking Information (2007, 2010, 2013, 2016) laccdb Access Web App Reference .accdw Access Signed Packages .accdc Access Database, used for Add-ins .accda Access…
Before we create the Make Table Query, you might like to create a new blank database file. This is so we have a separate database into which we can paste the new table from the query. In this exercise I have called it HistoricData.accdb. Here is the procedure to create a Make Table Query: Open…
Drag and Drop In MS Access Doug was on a roll in January of 2004 when he wrote his article on drag and drop in MS Access. He tested it now using Access 2016 and it still works. This article shows you how to add drag-and-drop to your Access application using combinations of multi-value and…
This is a quick tip on how to automatically display a form when the user opens your Access Application. In addition to improving User Friendliness, your database design will also appear much more professional. The step by step instructions below will display the Switchboard form in figure 1 immediately upon the application opening. This will…
SSMA is used to migrate Access databases to SQL Server. This tool converts the Access database to an SQL Server or an SQL Azure database. It is not bundled with SQL Server – you will need to download and install this tool separately. Check your system requirements and view the installation procedure for SSMA. Preparing…
What is the SQL Server equivalent to Access InStr() function? TargetField: String expression being searched SearchValue: String expression being sought StartPosition: Starting position for each search (optional) Access SQL InStr(TargetField, SearchValue) InStr(StartPosition, TargetField, SearchValue) SQL Server CHARINDEX(SearchValue, TargetField) CHARINDEX(SearchValue, TargetField, StartPosition)
Custom functions work the same way as MS Access built-in functions such as DateAdd, DatePart and DSum, but are instead created ourselves as database developers. We do this by creating a public function with the VBA programming language and save it inside a global module within the database. Today I am going to explain how custom functions can be…