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

About Me Search
Name:Alex Dybenko

Location:Moscow, Russia

Wednesday, March 28, 2007

How to scramble data

Sometimes you need to send your application with sample data to somebody, but how to get a sample data? You can just scramble data of some real database, perhaps several times and for multiple fields. There is a DataScramble.mdb, provided by Roger Carlson, Access MVP, which can help you with scrambling your data.

05/06/2010: Updated link DataScramble.mdb

Tuesday, March 27, 2007

Be careful using ISNULL()

Recently I found quite unpredictable (for me) behavior of SQL Server ISNULL() function. If you use it in Select, both arguments are nvarchar and length of first is less then second – then value of second field will be truncated to the length of first. So, if you run this SQL:

SELECT Address,
ISNULL(Region, Address)
FROM Suppliers

on sample Northwind database – result will be:

9-8 Sekimai Musashino-shi|9-8 Sekimai Mus
92 Setsuko Chuo-ku 92|Setsuko Chuo
Lyngbysild Fiskebakken 10|Lyngbysild Fisk

Any idea why this happens? Fortunately – COALESCE() works correct in this case, so I would recommend using it instead of ISNULL().


Technorati tags: ,

The Access Unlimited Newsletter

The Access Unlimited Newsletter is XML/RSS Newsfeed and email newsletter on Office Automation, Access and VB topics, edited by Garry Robinson, Access MVP, whom I recently met at Microsoft MVP Summit.

Friday, March 16, 2007

Summit's third day

and here the last day's photos, not sure it was third or forth, time passed very quickly:

Steve showing Sports database

Duane shows reports

Q&A session from yesterday

Cool guys @ Microsoft

The whole gang

Thursday, March 15, 2007

Summit's second day

Almost no photos for second day, executive session was translated online, and in the evening we had fun and food in Museum of Flight.

And here the photo of Access MVPs/Product group this morning.

Access 2002 (XP) runtime installation on Window Vista

If you build a setup package using Access XP Developer Packaging wizard and try to install it on Windows Vista PC – you will get a message "The Office System Pack cannot be installed on this system because it requires Windows NT 4.0 Service Pack 6 or later". There is a KB article 837150 which mention this error, but steps to fix the problem does not work for Vista.

In order to fix this you need to remove 4 lines in Ospfilelist.txt file which starts with:


See KB article 837150 for more details how to locate Ospfilelist.txt file.

Technorati tags: , , ,

Tuesday, March 13, 2007

Summit's first day

Today we have moved to Sheraton, staff is very friendly, room is really great, bit old-fashioned. The only thing – a girl at check-in asked where do I like to have my room, I asked her to make it as high as possible, to have a good view, but she gave me somewhere in the middle. No idea why did she ask this :-)

At 1300 registration started, this time all informational staff was fit in a small badge holder, besides 2 MVP shirts, which you will certainly see at next day’s photos.

In the evening he had regional dinner, I was from main Access MVP gang, but Peter introduced me Thomas Möller from Germany.

Monday, March 12, 2007

New address of Access Junkie

Jeff Conrad has moved his page, here a new address :

MVP Summit starts with sushi

Today we made a first meeting at MVP Summit 2007, this time it was at Todai sushi restaurant. Food was really good, but bit too much, anyway you can check our happy faces below.

The main program starts tomorrow, not sure we will have sushi again these days, but certainly we will have other fun. BTW, yesterday, together with Tom Wickerath and Arvin Meyer, we made a great trip to the middle of Washington state, Ellensburg, passing Snoqualmie with its falls and huge log.


At Palace restaurant, Ellensburg

Technorati tags: ,

Sunday, March 11, 2007

How to disable UAC for administrator account only

This is what I missing in Window Vista settings – as a developer I don’t like UAC, but would like to keep it for my son, who also using out Vista PC. Here a trick from Windows Secrets newsletter:

Step 1. Click the Start button and launch the Local Security Policy editor by entering secpol.msc in the Search box.

Step 2. Select the Local Policies item in the left panel to expand the tree, then expand Security Options under Local Policies.

Step 3. Scroll down the list in the right panel to locate User Account Control: Behavior of the elevation prompt for administrators in Admin Approval Mode. Right-click that item and choose Properties.

Step 4. Select Elevate without prompting and close the dialog.

If you use Vista Home Basic or Home Premium, the Local Security Policy editor, unfortunately, isn't included. To disable UAC for administrator accounts, you'll need to edit the Registry. Follow these steps to do that — and be extremely careful, since mistakes could render your system unusable!

Step 1. Click Start and enter regedit in the Search box to launch the Registry Editor.

Step 2. Navigate to the following key:

HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ Windows \ CurrentVersion \ Policies \ System

Step 3. Double-click the ConsentPromptBehaviorAdmin item.

Step 4. Change the value to 00000000.

Step 5. Close the dialog and exit the Registry Editor.

How to limit number of records showed in report

You need to build a report with grouping, say Customer and it contacts, but show only 10 first contacts or less. You can’t use top 10 in a query – it will show only 10 records at all. So how you can do this? The trick is to use Detail section Format event, which runs for each contact record and count number of records there, once you reach the limit – cancel event. Here the code in report’s class module:

'Variable to count number of lines
Private mlngLines As Long

Private Sub Detail_Format(Cancel As Integer, _
FormatCount As Integer)
If FormatCount = 1 Then
mlngLines = mlngLines + 1
End If
If mlngLines > 11 Then Cancel = True
End Sub

Private Sub {GroupHeaderName}_Format (Cancel As Integer)
'Reset it for each group
mlngLines = 1
End Sub

There is more two way to do the same in KB article 153747

Technorati tags: , ,

Wednesday, March 07, 2007

Export mixed type data to Excel

While we have already knew how to deal with Import or Link mixed type data from Excel, here a trick on export. Today I got a screenshot from customer, who tried to export Access report to Excel and got “Type mismatch” error. Screenshot showed a typical mixed data type column with document numbers, some of them with numbers only, and other started with letters. So export engine made an assumption on a first row that column type is numeric, and later produced the error on non-numeric data. Well, this I can’t say for sure - this was my guess. Anyway – I have opened report’s underlying query and added a space at the end of document number field: Select …. DocNum & “ “ as DocNum, .... And error gone!

Technorati tags: , , ,

Labels: , , ,

Friday, March 02, 2007

How to change field type using DAO

Access makes this task very easy – you just open a table in design view, select new field type and save table. But how you can do this in code? There are 4 basic steps – create new field, copy data from old field to new, delete old field and rename new field to old. There is a sample DAO code to perform this task for a sample table shown on the picture:

Function ChangeFieldType()

Dim dbsData As DAO.Database
Dim tdf As TableDef, fld As DAO.Field

Set dbsData = CurrentDb

'---Create New Field
Set tdf = dbsData.TableDefs("MyTable")
Set fld = tdf.CreateField("MyFieldNew", dbText, 50)
'Optional: set default value
fld.DefaultValue = "0"
'We set ordinal position, just after old field
fld.OrdinalPosition = 2
'And append
tdf.Fields.Append fld

'Copy values from old field to a new one
dbsData.Execute _

"Update MyTable Set MyFieldNew=MyField", dbFailOnError

'Delete old field
tdf.Fields.Delete "MyField"

'Rename new field to old
tdf.Fields("MyFieldNew").Name = "MyField"
Set tdf = Nothing

End Function