News, links, downloads, tips and tricks on Microsoft Access and related

About Me Search
Name:Alex Dybenko

Location:Moscow, Russia

Tuesday, November 30, 2010

Application for Access 2003 and 2007+: Substitute missing class in reference with custom MDE.

This trick I learned from Graham Mandeno, Access MVP. Here we talk about Ribbon interface, but same trick can be used for other libraries.

In Access 2007 command bars were replaced with Ribbon. You can develop application which can run on 2003 and 2007/2010, but if you build custom menu bar or command bar for Access 2003, when running in A2007+ the menu bar gets buried in the "Add-Ins" ribbon. So you would like to create a proper ribbon UI which would appear if the MDB/E is opened in A2007+, and still have the old-style CommandBar UI appear if opened in A2003.

You can create the ribbon for 2007/10, but the callbacks fail to compile in A2003 because class objects such as IRibbonControl do not exist in the Office11 object library. You can try to declare all instances "As Object" and that keeps A2003 happy, but then the ribbons don't work in A2007+. Let us give word to Graham:

What I have done is create a very small MDE containing nothing but class modules for IRibbonControl and IRibbonUI, each with the necessary properties and methods declared as empty procedures. The class modules need to have instancing set to “PublicNotCreatable”. Then I added a reference to the MDE to my project, ensuring that it is last in the list – BELOW the reference to MSO.DLL.

Now, when running on A2007+, the code picks up the reference from the Office 12/14 object library and works fine. On A2003 it finds it in the dummy MDE and compiles OK (of course, the actual code is never called in A2003).

In case anyone else is interested, here are the three class modules (I don’t actually use IRibbonExtensibility, but I included it for completeness):

' Class: IRibbonControl

Option Explicit

Public Property Get Context() As Object

End Property

Public Property Get Id() As String

End Property

Public Property Get Tag() As String

End Property


' Class: IRibbonUI

Option Explicit

Sub Invalidate()

End Sub

Sub InvalidateControl(ControlID As String)

End Sub


' Class: IRibbonExtensibility

Option Explicit

Public Function GetCustomUI(RibbonID As String) As String

End Function


Labels: , ,

Tuesday, November 16, 2010

Dynamic Cursor with sp_executesql

Recently I learned about quite useful sp_executesql stored procedure, check it out if you never used it. Using it you can execute dynamically built SQL statement with embedded parameters, including output parameters. For example, here you open cursor on dynamically built sql statement, like we open recordset in Access (before I used temporary tables for this):

DECLARE @Stock CURSOR, @sql varchar(1000), @Source varchar(1000)

Set @Source='Select ID From MyTable WHERE ' + @WhereString

set @sql=N'SET @Stock = CURSOR STATIC FORWARD_ONLY FOR ' + @Source + ';OPEN @Stock'

EXEC sp_executesql @sql, N'@Stock cursor OUTPUT', @Stock OUTPUT

FETCH NEXT FROM @curStockBooking

--and here goes typical cursor operation

Labels: ,

Thursday, November 11, 2010

Delete on joined table

Once I wrote how DISTINCTROW predicate helps to specify what table to UPDATE or DELETE. Below is one more case when DISTINCTROW could help.

Let’s say you want to delete records from the table, taking into consideration result of aggregate query, for example - delete customers, which have no orders. In Northwind.mdb we make a query qryCusromersWithOrders:

SELECT Orders.CustomerID
FROM Orders
GROUP BY Orders.CustomerID

Delete query SQL you can write as:

DELETE Customers.*
FROM Customers LEFT JOIN qryCusromersWithOrders ON Customers.CustomerID = qryCusromersWithOrders.CustomerID
WHERE qryCusromersWithOrders.CustomerID Is Null

But this query will fail with error “Could not delete from specified tables”, as you have non-updateable query in join. The trick is to add DISTINCTROW as:

FROM Customers LEFT JOIN qryCusromersWithOrders ON Customers.CustomerID = qryCusromersWithOrders.CustomerID
WHERE qryCusromersWithOrders.CustomerID Is Null

Labels: ,