Access MVP team on MVP Summit'05

It is a great pleasure to meet most of fellow MVPs, MVP Leads and Access Product team...

and even Microsoft executives, for example Lori Moore:

Lost in Translation?

There is an article from Smart Access, which you can find on MSDN - Lost in Translation. Well done, but why do you need to reinvent the wheel? Just run the MS Access Translator - it will do the rest!

Also I finished SPS Course

MVP Academy course was called "Building collaborative applications for SharePortal server". I am using SPS more then one year, and this was quite useful to learn how to manage service better. Unfortunately there was missing part of building web parts, which I was looking for.
Here some useful links:

TechNet Virtual Lab: Microsoft Office SharePoint Portal Server 2003

Book "The Rational Guide to Building SharePoint Web Parts"

Book "Advanced Sharepoint Services Solutions"

VSTO Course finished

Recently I finished a VSTO course in MVP Academy (full name of the course is "Using Microsoft Visual Studio Tools for the Microsoft Office system"). Actually I tried once before to start using VSTO to build a simple Excel template, but found that much more efficient to use old good VBA instead. Now, after I learned much more about VSTO, I become more optimistic about it, but not so much to start doing everything on it. There are still performance issues, few functionality issues, and finally VBA is just native staff so far for the Office. Anyway - there are few areas where it is good to use VSTO, for example if your template consumes webservice. Looking forward to see next, 2005 version of VSTA.

Here few links which could be useful if you start to look into VSTO:

Creating a Capital Expenditure Model with Visual Studio 2005 Tools for Office and Excel 2003 Using Visual Basic and C#

Deploying Office Solutions

BUG: "Old format or invalid type library" error when automating Excel

Creating Office Solutions for Use in Multiple Countries/Regions

Globalization and Localization Issues for Solutions Created with Microsoft Visual Studio Tools for the Microsoft Office System

How to undo whole record

This tip I learned from MVP Allen Browne
Here a code, which you can place into Undo button click event:

    If Me.Dirty Then
        Me.Undo
    End If
    If Not Me.NewRecord Then
        RunCommand acCmdDeleteRecord
    End If

MS Access functions reference online

Do not remember where I got these links, but think it is worth to put here:
MS Access Reference of functions

The site also contains number of tips for Access developers; you can start browsing from Access Topics page

How to show OpenFile Dialog in preview mode

To show a simple OpenFile Dialog is quite easy - you can fill it structure and call GetOpenFileNameA() API. Good sample is on the AccessWeb.
But if you want to let user open picture and show him preview in OpenFile Dialog? Then you have to use Office FileDialog function. A good sample of using it together with sample database is on the Access & VBA FAQ of MVP Juan M. Afán de Ribera

Short look at Access 12

Clint Covington, Lead Program Manager of Access team, has published links to PDC presentations at Utter Access site

VSTO become VSTA 2005

Recently at PDC05 was announced a new product Visual Studio 2005 Tools for Applications. You can read about it in Paul Stubbs blog and official Microsoft page. I just finished a MVP Academy course on VSTO, and can't say that I am so much impressed with it, but heard lot interesting about 2005 version and looking forward to know it!

Video on Office 12

On Channel 9 Scobleizer now published a great video on Office 12

Object communication using events

MVP Peter Walker have published an article "Data Broadcasting and Communication Pipelines using Events". Interesting reading! Just to add to first approach - you can also declare a parent form with events, and then use it Change event to be notified about record changes. Less programming, but also less possibilities. Only thing I don't like here - this can cause GPF very easy, unfortunately Access is not so OOP fiendly.

Microsoft shows Office “12” screenshots

After Bill Gates speech at PDC Microsoft starts to publish some information about new Office. This Q&A article shows few screenshots, and of course Access UI screenshot. Hmm, looks more like Outlook - don't you think so?

Top 1 does not return only one record

I was sure that Select top 1 MyField from MyTable Order by OtherField always return 1 record. But it is not! This feature I just recently discovered while helping a guy from Colombia. When this select is ordered - then result depends on values in OtherField. Access help actually states this also: "Returns a certain number of records that fall at the top or the bottom of a range specified by an ORDER BY clause". So if you have a table Journal:

ID Car Miles Date
1 Alfa 100 01/09/2005
2 Alfa 110 02/09/2005
3 Alfa 120 03/09/2005
4 Alfa 130 04/09/2005
5 Alfa 140 04/09/2005

Then following select:

SELECT TOP 1 * FROM Journal Order By Date Desc

Returns 2 records:

ID Car Miles Date
5 Alfa 140 04/09/2005
4 Alfa 130 04/09/2005

MODI VB Reference

MVP Stephen Lebans recently pointed to useful links for Microsoft Office Document Imaging:
Microsoft Office Document Imaging Visual Basic Reference and VBA Language Reference for the Document Imaging Object Model. Keep it in place in case you think to start using MODI.

Screen resolution

Actually I thought that almost nobody use screen resolution less then 1024x768 in modern world. But according to latest VBWire survey - 40% of users use lower resolution, and these are mostly developers:
800 X 600: 24%
960 X 600: 16%
1024 X 768: 25%
1152 X 864: 16%
1280 X 720: 19%

Means that we have to write UI and web sites to be fitted into 800 X 600...

Bring back Access 97 MsgBox

Old good Access 97 has MsgBox function which allows you to show message box with text, divided into 3 sections, with bolded first. Since Access this functionality gone. But not completely - you can still do the same in macros. Here a workaround how to bring old functionality back. Just add a function below to any module, and all MsgBox start to work like in Access 97:

Public Function MsgBox(Prompt As String, _
     Optional Buttons As VbMsgBoxStyle = vbOKOnly, _
     Optional Title As Variant = "", _
     Optional HelpFile As Variant, _
     Optional Context As Variant _
    ) As Variant
On Error Resume Next
  If Nz(Title, "") = "" Then
    Title = CurrentDb().Properties("AppTitle")
  End If
  On Error GoTo PROC_ERR
  If IsMissing(HelpFile) Or IsMissing(Context) Then
    MsgBox = Eval("MsgBox(""" & Prompt & """, " & _
        Buttons & ", """ & Title & """)")
  Else
    MsgBox = Eval("MsgBox(""" & Prompt & """, " & _
        Buttons & ", """ & Title & """, """ & _
        HelpFile & """, " & Context & ")")
  End If

PROC_EXIT:
  Exit Function

PROC_ERR:
  MsgBox = VBA.MsgBox(Prompt, Buttons, Title)
  GoTo PROC_EXIT
End Function

Software project live circle

Here a nice explanation why sometimes we come into problems with software project development :-)

Office Video Tips

MVP Dian Chapman starting to publish Video tips to use Office more efficiently. First video available at latest TechTrax Ezine

ISO Country names

English country names and code elements list and updates can be useful to build a country table. BTW, CountryCombo's countries list is based on ISO table also.

Use Latitude And Longitude To Find Distance Between Two Points

This post in Microsoft Support newsgroups I found useful (not sure about name of poster, sorry...):

Const PI As Double = 3.14159265358979
Const Circumference As Double = 40123.648 'kilometers
Const MilesPerKilometer As Double = 0.6214
Public Function Distance(ByVal Latitude1 As Double, _
        ByVal Longitude1 As Double, _
        ByVal Latitude2 As Double, _
        ByVal Longitude2 As Double, _
        Optional Miles As Boolean) As Double

    Dim CosArc As Double
    Dim Arc As Double
    Latitude1 = Radians(Latitude1)
    Longitude1 = Radians(Longitude1)
    Latitude2 = Radians(Latitude2)
    Longitude2 = Radians(Longitude2)
    CosArc = (Sin(Latitude1) * Sin(Latitude2)) + _
    (Cos(Latitude1) * Cos(Latitude2) * Cos(Longitude1 - Longitude2))
    Arc = Degrees(Atn(-CosArc / Sqr(-CosArc * CosArc + 1)) + 2 * Atn(1))
    Distance = Arc / 360 * Circumference
    If Miles = True Then Distance = Distance * MilesPerKilometer
End Function
Private Function Radians(ByVal Degrees As Double) As Double
    Radians = PI * Degrees / 180
End Function
Private Function Degrees(ByVal Radians As Double) As Double
    Degrees = Radians / PI * 180
End Function

Testing on New Delhi (28 37 / -77 13) and
San Francisco ( 37 48 / 122 27)
we get 12380.0276235478 kilometers.
National Graphic Family Reference Atlas gives 12380 km ...
so you may have to walk 25 extra yards.

Caution: the second number in latitude and longitude designations are minutes and must be divided by 60 before being passed to the function as in Debug.Print Distance(28 + 37 / 60, -77 - 13 / 60, 37 + 48 / 60, 122 + 27 / 60) (for the two cities bove).

Signs must be adjusted for north and south latitude and east and west longitude ...
one positive and the other negative If one gets to seconds then the numerator will be 3600.

How to save form image

I was asked - I have Access form, I have to save it image to file. Shamil Salakhetdinov pointed to VBDOTNET post with a sample code. Hope you will find this useful also. More samples can be found at MVP Stephen Lebans site.

Be the first to know about Office 12

You can register at Future of Microsoft Office page to get latest news

How to modify embedded MS Office document

Say you have a Bound Object Frame on your form, bound to OLE Object field type. User has inserted a MS Office document there, for example Excel Sheet, and you want to modify it contents. The trick is that you have to activate field first, using Action property, and then modify its content. Here a code from sample procedure:

Private Sub cmdModifyExcel_Click()

    Dim oSheet As Object

    Me.oleExcelObj.Action = acOLEActivate
    Set oSheet = Me.oleExcelObj.Object.Sheets(1)
    oSheet.Cells(1, 1).Value = "Hi from VBA!"
    Me.oleExcelObj.Action = acOLEClose

    Set oSheet = Nothing

End Sub

Attensa publishing option

Ok, it is working, but still not perfect. Good that I can type in Outlook, using Microsoft Word as editor, but when it publish plain text to blogger - extra HTML tags added, SPAN for example. Furthermore - I have 2 blogs - how I can post to second blog?

Attensa now publish to Blogger

Just downloaded a new version of Attensa (RSS aggregator I am using), and started to test publishing option. Few test message were successfuly published! Great job! Ok, this one i also writing in Outlook.

How to change Access language

If you have say Russian version of Access installed, and for some development you need an English version - there is a easy way to change language version - you need to find a MSAIN.DLL file in OFFICE11\1033 folder on a PC where English Access installed, and copy it over same file in OFFICE11\1049 folder of Russian Access. Don't know if this is a supported way of doing this, perhaps there are a better way, but at me this works fine since Access 2.0