Alex & Access

How to list queries by type

2009-12-09T17:18:00.001+03:00

You can get a list of all queries with simple select statement:

SELECT Name
FROM MSysObjects
WHERE Type=5

There is a Flags field in MSysObjects which shows query type, for example you can get a list of all pass-through queries:

SELECT Name
FROM MSysObjects
WHERE MSysObjects.Type=5 AND Flags in (112, 144)

And here a list of flags field values you can use (at least what i found):

0 – Select query
3 – hidden select query, used in form’s recordsource or control’s rowsource
16 – Crosstab query
32 – Delete query
48 – Update query
64 – Append query
80 - Make-Table query
96 – DDL query
112 - Pass-through Select query
128 – Union query
144 - Pass-through Action query

Jet tables, indexes and expressions

2009-12-08T21:26:00.001+03:00

Michel Walsh, Access MVP, have posted an excellent example, which helps to understand how Jet deals with indexes and evaluates expressions:

Have a table, Ds, one field, D, with values for 0 to 9, and a primary key. Have, in a standard module, have:

Public Function ShowMe(Arg As Variant) As Boolean
MsgBox "me"
ShowMe = True
End Function

Now, make the query

SELECT d
FROM Ds
WHERE d > 100 AND ShowMe(d)

And you won't get any prompt form ShowMe.

Change the 100 to 8, and you get one prompt.

Change the 100 (or now 8) to 6, and you get three prompts (and, in A2003, a bug in the display of the result, but that is another story).

So, that is conclusive that there is a matter of short cut evaluation in the criteria and it is technically wrong to say that a function call will necessary produce a table scan. If it was true, the ShowMe would run 10 times in all the previous examples.

I have been surprised to see that

SELECT Ds.d
FROM Ds
WHERE showMe(d) AND d > 6

produces the same behavior (I was on the impression that Jet was not very optimized and did not try to re-order the WHERE parts, I was wrong).

So, indexed fields seems to be used first and if other parts are to be evaluated, then the values from these records are brought through the network, unless there are not already in the local cache.

Using Like in JOIN

2009-12-02T10:55:00.001+03:00

While reading “The Cartesian product: a feature, not a bug” by Peter Vogel, I remember that I used one more syntax to join 2 tables. From the article – we can join 2 tables using function in expression:

SELECT Table1.Field1, *
FROM Table1, Table2Num
WHERE Table1.Field1=CStr([Field2])

But you can also use same expression in FROM clause:

SELECT Table1.Field1, *
FROM Table1 INNER JOIN Table2 ON Table1.Field1=CStr(Table2.[Field2])

So you now can use Left or Right join:

SELECT Table1.Field1, *
FROM Table1 LEFT JOIN Table2 ON Table1.Field1=CStr(Table2.[Field2])

And you can use whatever expression there, most useful for me was LIKE, for example you define mask for objects in table1, like AB??01??, and your query returns all objects with code for that pattern:

SELECT Table1.Field1, Table2.*
FROM Table1 LEFT JOIN Table2 ON Table2.[Field2] LIKE Table1.Field1

Of course, do not switch query to design view, then your SQL will gone…

Using Access/SQL databases with PowerShell

2009-11-28T11:11:00.001+03:00

I am not a big fan of PowerShell, this is a language of IT Pros most of all, but anyway – good to know how it can help Access developer. Richard Siddaway, PowerShell MVP, showing how to use Access and SQL Server through PowerShell. Furthermore you can find there how you can use other applications and services through PowerShell.

Thanks to Tony Toews for link!

Get Office 2010 beta

2009-11-19T18:32:00.001+03:00

Office 2010 beta is available now!

Office 2010: Developer References

2009-11-13T14:39:00.001+03:00

Download provides developer Help content for the Office 2010 Beta release, also Access 2010 Developer Reference.

Office 10 on Skydrive

2009-11-13T13:31:00.001+03:00

Misha Birman from MSN have showed me today amazing Skydrive feature – now you can edit Word, Excel, PowerPoint and OneNote documents directly in browser. Same way like Office 2010 web services will do. I think... I tried to make a screenshot – but “We can't show you that page. Our server is having a problem.”… So try by yourself – go to Skydrive, My Documents, switch to “edit” feature, then use New menu to create document. Ok, works now, here a Excel:

Hmm, maybe we will get Access there one day???

Installing VB6 without Java

2009-11-07T11:05:00.001+03:00

This trick should help to install VB6 on Windows 7: Installing VB6 without Java

How to relink Access tables faster

2009-10-29T16:04:00.001+03:00

When you run relink procedure at application startup, and backend file is located on the network – the process can be much slower then relinking local file, even with very fast network. The trick is to open backend file before relink process and close it after. Sample code:

Dim dbData As DAO.Database

Set dbData = DBEngine.OpenDatabase(strFileName)

'Relink proc follows

...

dbData.Close

Set dbData = Nothing

Access 2010 in action

2009-10-29T13:43:00.001+03:00

Check out this great video - Access 2010 and Outlook style calendar running in a browser by Albert D. Kallal, Access MVP. Application was developed in Access and running in web browser using SharePoint Access services. Well done, Albert!

Access 2010 - Access Services

2009-10-21T13:18:00.001+04:00

Now it is official - Access Services for Sharepoint 2010. Ryan McMinn and Clint Covington opens curtain.

MVP 2009

2009-10-01T18:31:00.001+04:00

Just got my MVP award:

Congratulations! We are pleased to present you with the 2009 Microsoft® MVP Award

This is my 12th year on MVP, first was in 1998. I feel great!

Querying All Databases With sp_MsForEachDb

2009-09-10T10:30:00.001+04:00

If you need to query several databases on same SQL server – you can use undocumented stored procedure sp_MsForEachDb, see Scott Mitchell article Querying a Multi-Tenant Data Architecture for more info and execution examples.

How to use .NET assembly in VBA

2009-09-05T07:42:00.001+04:00

Example with detailed steps: Consuming .NET components within Access/VBA

How to get rid of ADMIN - 00 tables in database

2009-08-31T23:47:00.001+04:00

If you are running pass-through queries in your database – you can suddenly get a lot of ADMIN – 00, ADMIN – 01, etc. tables. Office Online article Create tables from the results of a pass-through query (MDB) explains why this happens:

Some pass-through queries can return messages in addition to data. If you set the query's LogMessages property to Yes, Access creates a table that contains any returned messages. The table name is the user name concatenated with a hyphen (-) and a sequential number starting at 00. For example, the default user name is ADMIN so the tables returned would be named "ADMIN - 00," "ADMIN - 01," and so on.

Even you set LogMessages property to No – you can get such messages. For example - in one application I am continuously getting message like:

01003 - 8153 - [Microsoft][ODBC SQL Server Driver][SQL Server]Warning: Null value is eliminated by an aggregate or other SET operation.

To get rid of it – you have to find query, which produce this message and fix it. In my case it was a query with SQL:

Select ProductID, Sum(Qty) as Shipped from tblOrder

Qty field had some null values and is was a source of this message. So I fixed it like this:

Select ProductID, Sum(Qty) as Shipped from tblOrder where not Qty is null

A new method for disabling the Mouse Scroll Wheel in Access forms

2009-08-12T18:09:00.001+04:00

Or how to run native code from VBA! I just learned about new method to put native code into memory and run it from Wayne Phillips. Very cool! He made a sample to disable mouse wheel on Access form with that method. Read the tutorial to learn more.

VB5/6 Controls and Components

2009-07-29T18:56:00.003+04:00

Now at MVPs.org site:

These downloads were all provided by Microsoft for VB5 and/or VB6, and the links will point to download.microsoft.com until Microsoft decides to remove them

Update: some links still on MSDN site, with descriptions.

How to get Tag information from MP3 files

2009-07-22T19:43:00.001+04:00

1. Check article November 2004: Sounds good to me and Stupid Date Tricks of Doug Steele, Access MVP

2. Two sample applications, on German, but you can read VBA:

3. VB6 code from VBAccelerator.

How to replace text in Macro

2009-07-22T14:03:00.001+04:00

You can use this function MacroReplaceText() to replace object name, whatever, in macro with VBA. Run it as:

MacroReplaceText "Macro1","OldForm", "frmMyNewForm"

You can use the same technique to make new macro, just create a text file in proper format, check ~macrotemp.txt file to find this out.

Public Function MacroReplaceText(strMacro As String, _

strToFind As String, _

strToReplace As String)

Dim strTempFile As String

Dim lngPos1 As Long

Dim intFile As Integer, strLine As String

Dim strResult As String

strTempFile = Environ("Temp") & "\~macrotemp.txt"

intFile = FreeFile

If Len(Dir(strTempFile)) > 0 Then _

Kill strTempFile

'Save to text

Application.SaveAsText acMacro, strMacro, strTempFile

Open strTempFile For Input As #intFile

Do Until EOF(intFile)

If Len(strLine) > 0 Then

strResult = strResult & vbCrLf & strLine

End If

Line Input #intFile, strLine

Loop

strResult = strResult & vbCrLf & strLine

Close #intFile

If Len(Dir(strTempFile)) > 0 Then _

Kill strTempFile

strResult = Replace(strResult, strToFind, strToReplace)

lngPos1 = InStr(1, strResult, "Version")

If lngPos1 > 0 Then

Open strTempFile For Output As #intFile

Print #intFile, Mid(strResult, lngPos1)

Close #intFile

Application.LoadFromText acMacro, strMacro, strTempFile

End If

If Len(Dir(strTempFile)) > 0 Then _

Kill strTempFile

End Function

New Office 2010 movie

2009-07-12T08:50:00.001+04:00

Old movie was replaced with new one:

You can also look as one more movie from Access MVP Albert Kallal, I like this one!

Why Fixed Bid Software Projects Are a Bad Idea

2009-06-22T20:21:00.001+04:00

A very good article by Armen Stein, Access MVP

exportSQL

2009-06-11T07:58:00.001+04:00

More code on conversion Access database to other database types:

exportSQL is a Microsoft Access module which exports Access Database into MySQL, mSQL and PostgreSQL. It's based on work of Pedro Freire. [fm] [CVS]
There is also a GUI version, kindly donated by Marc Breneiser <mbreneiser(at)whgrp(dot)com> which is especially useful if your Access database is already converted to .mde files. Zip Conversion.zip includes .exe and source in VB.

Quoted from *SQL-ish projects page of Dobrica Pavlinusic.

SQL Server Publishing Wizard

2009-06-09T08:53:00.001+04:00

Microsoft SQL Server Database Publishing Wizard 1.1 – scripts your database, schema and data, into single text file, so you can recreate it on remote server where only web-based panel available.

Smart Access Is Back!

2009-06-02T16:49:00.002+04:00

Thanks to Garry Robinson, Access MVP, who made Smart Access available again!

BTW, some articles are available at MSDN site.

Office 2010

2009-05-14T12:08:00.001+04:00

Here the movie. No control or no Ctrl+Alt+Delete?