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

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: SQL Server, T-SQL

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.

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

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:

DBMSADSN.DLL
MSXML.DLL
DBNMPNTW.DLL
SQLOLEDB.DLL

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

Technorati tags: Access, runtime, setup, Vista

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.

New address of Access Junkie

Jeff Conrad has moved his page, here a new address :
http://www.accessmvp.com/JConrad/accessjunkie.html

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.

Technorati tags: MVP Summit 2007, sushi

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: access, reports, tricks

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: Access, Excel, Export, Error

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
dbsData.TableDefs.Refresh
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"
tdf.Fields.Refresh

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


End Function