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…
It is common to create a big wall of declaration in a module such as this:
|
1 2 3 4 5 |
Private strCompanyName As String Private dteOrderDate As Date Private lngOrderID As Long Private objEmail As Object Private objOutlook As Object |
While the code will compile and work fine, the discoverability and naming of the module level variables does become difficult and unwieldy. To help cut down on this ambiguity, it is recommended to create a private user-defined type…
A number of objects may have extra properties or something where it might not exist for all instances. A good example might be finding Controlsource property on an Access.Control variable:
|
1 2 3 |
For Each ctl In Me.Controls Debug.Print ctl.ControlSource 'A potential error Next |
Not all controls have a ControlSource property and thus can fail to run. A common approach is to use OERN:
|
1 2 3 4 5 |
For Each ctl In Me.Controls On Error Resume Next Debug.Print ctl.ControlSource 'A potential error On Error GoTo 0 Next |
This will work…
What is the SQL Server equivalent to Access NZ() function? Access SQL NZ([Event Timestamp]) SQL Server Equivalent COALESCE([Event Timestamp],0) ISNULL([Event Timestamp],0) * do not confuse this with the Access isnull() function.
This reference contains conceptual overviews, programming tasks, samples, and references to guide you in developing solutions based on Microsoft Access. The reference contains the following sections: What’s New: Provides a list of new members by object and in alphabetical order. Concepts: Provides important concepts for developing custom Access solutions. Reference: Provides reference materials for the Access…
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…
Access crashes and slow performance Error: “Automatic configuration of the current version of Microsoft Access has failed.” occurs when you try to start Microsoft Access after updating to Version 1802 [FIXED] ISSUE You may get the following error when start Access after updating to Monthly Channel Version 1802 (Build 9029.2167 or higher): “Automatic configuration of…
Sometimes we need to use fields from a RecordSource that aren’t shown on the form. It is legal to reference them directly even if they don’t have a control. For example, we can have a form bound to a RecordSource like so:
|
1 2 3 4 |
SELECT c.CompanyID, c.CompanyName FROM Companies; |
And only display CompanyName in a textbox but not show the CompanyID….
It is very common for us to embed some kind of logic based on a lookup table or in some cases, a column of a table. Because we have application logic connected to it, the code are fragile and subject to changes as the requirements develops. We want to avoid this situation where we might…
In Microsoft Access, a null value is a value that is not assigned or unknown. When working with databases, it’s important to understand how to use and identify null values in order to ensure accurate and efficient data management. The IS NULL operator is one of the most useful tools for working with null values…