Microsoft Access Blog

Колонный зал дома союзов у меня всегда ассоциировался с погребение наших вождей. Никогда не участвовал, но благодаря REMIX удалось посетить. Как будто вернулся на 20 лет назад, за все эти годы там похоже ничего не поменялось. Особенно поразил буфет - обстановка, персонал и еда точно соответствовала тому времени. Наверное самому молодому представителю персонала было лет 50. Под конец милиционер всех бродивших с пивом по фойе загонял в буфет, ну совсем как в 80-е!

Сам REMIX прошел на высоте, Дмитрий Сотников зажигал как обычно. Стив Балмер тоже пытался зажечь, но его постоянно тушил нудными вопросами Антон Носик. Да, теперь я понял почему он выбрал такое название для своего блога :-). Думаю надо было кого поживее найти, да и вопросы подобрать поинтереснее. Коллегам, кто видел его первый раз, все понравилось, но они просто не видели как он на самом деле отжигает. Например посмотрите как было на оригинальном MIX’08.

Вопросов к экспертам на этот раз было не много, видимо аудитория другая чем обычно, не девеловеры. Одна девушка, программист на PHP, очень заинтересовалась технологией Silverlight, спрашивала у меня про возможности хостинга PHP и ASP.NET. Может она конечно и не переметнется никогда в наш лагерь, но уж точно попробует новые технологии Microsoft. Так что думаю событие удалось!

Да, для тех, кто не попал - все можно посмотреть на сайте REMIX.

Bob Heifler from MyAccessProgram.com pointed me to these video tutorials, which can be useful mostly for novice Access users:

How to write Visual Basic for Applications (VBA) functions

How to create a Subroutine using VBA programming code

How to write error trapping code for VBA functions and subroutines

Use Access Events to trigger Visual Basic for Applications (VBA) Programming Code

Data Validation code for Microsoft Access forms

Methods for debugging Visual Basic for Applications(VBA) program code

Create different types of pop up user Message Boxes with VBA code

Conditional Formatting of form and report controls without needing to write VBA programming code

Microsoft Access 2007 Tutorial - How to use File Attachment Controls

How to display Calculated Values in Microsoft Access Forms or Reports

Microsoft Access 2007 Anchoring Fields so they stretch to the changing size of a form or report

Microsoft Access 2007 Tutorial - Data Collection By Email

Microsoft Access 2007 Tutorial - Outlook Task to Scheduled Reoccurring Access Imports or Exports

Microsoft Access 2007 Tutorial - How to show Date Picker

Microsoft Access 2007 Tutorial - Alternating Row Color in Reports and Datasheet Views

Microsoft Access 2007 Video Tutorial -Queries Part One - Simple query formulas combining field values, arithmetic on field values, creating alias field names, and use of NZ function

Microsoft Access 2007 Video Tutorial - Queries Part Two - Query Table Joins

Microsoft Access 2007 Video Tutorial - Queries Part Three - Query Criteria Methods and use of Like Operator and Wildcard

Microsoft Access 2007 Video Tutorial - Queries Part Four - Action Queries - Make Table Query, Update Query, Append Query, Delete Query

Microsoft Access 2007 Video Tutorial - Queries Part Five - Using Aggregate Functions in Group By Queries

A single function to get any number of stored public variable values

Если вы еще не знаете - там будет Стив Балмер, только ради этого стогит туда сходить!

Спеши зарегистрироваться!

Tony Toews, Access MVP, mentioned this post - Nested msi with custom action : msp file.

Несколько раз уже слышал за последнее время что в растет популярность Visual Basic (VB) в США. Вот еще наглядная иллюстрация. Т.е. если взять два равноценных языка – C# и VB.NET – то число любителей второго языка резко превышает любителей первого. А для России совсем наоборот, обычно я слышу требование – мол проект должен быть реализован на C#. Чем это объяснить? Любовью вводить фигурные скобки (а что, больше символы {} использовать особо негде, чего им зря простаивать?:)) Былой популярностью С? Как вы думаете?

Я лично могу писать и на c# и на VB.NET, но предпочитаю второй, т.к. не люблю скобки и легче переключаться с VBA, на котором я тоже много пишу. А если что – можно быстро конвертировать в нужный язык.

Very cool feature for SQL Server 2005 - Missing Indexes Feature.  It uses dynamic management objects and Showplan to provide information about missing indexes that could enhance SQL Server query performance. So you can just run a query to find out which indexes are missing. Of course it can't replace developer or DBA, who certainly knows better how to fine tune DB, but can help to do so.

Here a blog post of Russian MVP Alexander Gladchenko, who pointed me to this feature.

I wrote some export to DBF (yes, people still using DBF to exchange data!) routine today, and looked for some function to check that field name is valid. Have found nothing, so I wrote my own. Here it is, if you think something should be added – please write in comments:

Function IsValidFieldName(strFieldName As String) As Boolean
    Dim i As Long
    'first check that is does not start with number
    IsValidFieldName = Not (strFieldName Like "#*")
    For i = 1 To Len(strFieldName)
        Select Case True 'then check for allowed chars
            Case Mid(strFieldName, i, 1) Like "[a-z]"
            Case Mid(strFieldName, i, 1) Like "[0-9]"
            Case Mid(strFieldName, i, 1) Like "_"
            Case Else
                'found something - not passed
                IsValidFieldName = False
                Exit For
        End Select
    Next i
End Function

Access Translator works now also in Access 2007. Last time, when I tried to upgrade it, I was stopped by DoCmd.SelectObject bug. But now this bug was fixed in Office 2007 SP1, and furthermore I found another workaround, so it should work in any currently existing Access 2007 version. But if you find any problem running it – kindly let me know!

Registered users can get new version for free, just let me know.

The best description on how to add and configure trusted locations I found at Gunter Avenius site. He also offers a executable, which you can run with command-line parameters in order to add trusted location to registry. This small program can be also used for application setup with Package and Deployment Wizard.

I like using datasheets as subforms, but Access does not offer datasheet-specific events and properties to automate it. Here 2 cool samples from Krzysztof Naworyta, Access MVP, which help to make datasheet more friendly.

How to recognize what form's element you click in datasheet view: column, record selector and so on

New events in datasheet view and Which column I’ve just clicked

Both samples in Polish, but VBA still in English.

Normally you can print document using ShellExecute API with Print parameter. This also works for PDF, but leaves Acrobat Reader open. Imagine what can you get on a screen after multiple document printing? But fortunately - there is a way to close it after print, using old technology - DDE (remember this one? It was the only way to communicate with external program before OLE Automation). Here a procedure to print PDF files from VBA code. Originally I found some parts this code on internet, but can't find source anymore. So if you saw parts of it somewhere - please let me know, I will put a link and reference message.

Now I am posting VBA code in separate HTML pages on PointLtd site, after several tries to find a good way to post code here this way looks more attractive for me, and hopefully for readers also.

In Windows 95 time I often used HKEY_LOCAL_MACHINE registry hive to store user data, for example path to backend, user settings. Security was at low level, user management was not really used, user could easy create a new account just logging first time on the PC. And the only permanent storage for data was HKEY_LOCAL_MACHINE. But not now!

Security getting stronger, and with Windows Vista you are not allowed to write to HKEY_LOCAL_MACHINE hive even you are administrator (unless you disable UAC)! The only good place to store user data – is HKEY_CURRENT_USER registry hive. But, as I recently read in newsgroups – developers are still trying to use HKEY_LOCAL_MACHINE. And now it could be dangerous!

Imagine we have Access application with linked tables, and we store path to BE in HKEY_LOCAL_MACHINE (using some dirty hacks). When user gets new FE version and first run it – we read saved path and relink new FE to it. Looks ok? But then network administrator decided to install Active Directory security with roaming user profiles stored on server. And here we get a problem – user could easy use any PC to log in, but he don’t know that last user, logged into that PC, have connected to different BE! And he starts to delete data from real database instead of a test copy, or type new data into archive database. whatever. We are in trouble!

One more dilemma – where to install FE? In corporate environment, terminal services and Window Vista users are not allowed to write to C:\Program Files, so they can’t update FE version using VersionCheck or any other similar program. I think that <$USER>\Local Settings\Application Data folder could be a good place to save FE – VersionCheck will deliver new version to this folder when user start application.

Do you have other suggestions?

One more great program – PDFCreator, you can use it as PDF printer, but also you can use it automation object to manipulate PDF documents content – search, copy/paste, split, merge, etc. Imagine you need to create an electronic document package – Access report, Word document and Excel sheet printout in single file - PDFCreator can easy do it.

Супер отчет с фотографиями всех шагов. Не для слабонервных!

Очень интересная фича NTFS - Alternate Data Streams, сколько лет уже пользуюсь NTFS, а узнал только сегодня, прочитав в Windows Secrets.

1. достань свой ноутбук из сумки;
2. открой его медленно и спокойно;
3. включи его;
4. убедись, что этот придурок смотрит в твой экран;
5. запусти твой любимый поисковик;
6. закрой глаза и подними голову к небу;
7. глубоко вдохни и нажми ни эту ссылку
 http://www.thecleverest.com/countdown.swf
 8. посмотри на лицо твоего соседа)))))

If you have some add-in installed with Access – you can run it using Menu Tools-Add-Ins. But sometimes you need to run it from button click on your form. Here a VBA code which starts add-in Translator:

Dim cb As CommandBar, cbb As CommandBarControl
Set cb = CommandBars("Menu Bar")
Set cbb = cb.Controls("&Tools").Controls("Add-&Ins").Controls("Translator")
cbb.Execute

Еще один сайт посвященный Selespeed, куча информации по устройству, обслуживанию и поиску неисправностей.

CompareEM is a very simple to use utility to analyze a pair of related Microsoft Access databases, determine what has changed from one to the other, and then generate VBA code that will transform one into the other. FREE!