Alex & Access

Installing VB6 without Java

noreply@blogger.com (Alex Dybenko) — Sat, 07 Nov 2009 00:05:28 PST

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

How to relink Access tables faster

noreply@blogger.com (Alex Dybenko) — Thu, 29 Oct 2009 06:09:41 PDT

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

noreply@blogger.com (Alex Dybenko) — Thu, 29 Oct 2009 03:43:04 PDT

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

noreply@blogger.com (Alex Dybenko) — Wed, 21 Oct 2009 02:18:26 PDT

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

MVP 2009

noreply@blogger.com (Alex Dybenko) — Thu, 01 Oct 2009 07:31:42 PDT

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

noreply@blogger.com (Alex Dybenko) — Wed, 09 Sep 2009 23:30:33 PDT

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

noreply@blogger.com (Alex Dybenko) — Fri, 04 Sep 2009 20:42:10 PDT

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

How to get rid of ADMIN - 00 tables in database

noreply@blogger.com (Alex Dybenko) — Mon, 31 Aug 2009 12:47:40 PDT

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

noreply@blogger.com (Alex Dybenko) — Wed, 12 Aug 2009 07:09:17 PDT

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

noreply@blogger.com (Alex Dybenko) — Thu, 03 Sep 2009 08:01:36 PDT

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

noreply@blogger.com (Alex Dybenko) — Thu, 23 Jul 2009 02:56:23 PDT

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

noreply@blogger.com (Alex Dybenko) — Wed, 22 Jul 2009 03:03:10 PDT

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

noreply@blogger.com (Alex Dybenko) — Sat, 11 Jul 2009 21:50:56 PDT

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

noreply@blogger.com (Alex Dybenko) — Mon, 22 Jun 2009 09:21:03 PDT

A very good article by Armen Stein, Access MVP

exportSQL

noreply@blogger.com (Alex Dybenko) — Fri, 12 Jun 2009 22:10:55 PDT

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

noreply@blogger.com (Alex Dybenko) — Mon, 08 Jun 2009 21:53:21 PDT

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!

noreply@blogger.com (Alex Dybenko) — Tue, 16 Jun 2009 02:59:04 PDT

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

BTW, some articles are available at MSDN site.

Office 2010

noreply@blogger.com (Alex Dybenko) — Thu, 14 May 2009 01:08:11 PDT

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

Northwind.NET

noreply@blogger.com (Alex Dybenko) — Mon, 04 May 2009 22:37:47 PDT

Northwind sample database, shipped with any Access version, shows different programming techniques, so Access developer can take look at sample code and quickly learn it. Northwind.NET does the same for .NET framework – it is a set of sample applications demonstrating various modern technologies and development practices used within .NET Framework and Visual Studio 2008. If you once need to make something in .NET – just look at this project, find a type of form or report you want to build and check how you can build it using different .NET development techniques.

Plus - there is a sample of calling .NET web service from Access (or wrapping any .NET class into COM)!

Access - a gaming platform?

noreply@blogger.com (Alex Dybenko) — Thu, 30 Apr 2009 21:57:18 PDT

Sounds strange? Not a all :)

Just look at two games build on Access.

BTW, Excel too, look at Pacman for Excel!

Thanks for Albert D. Kallal for these links!

Registration-free COM

noreply@blogger.com (Alex Dybenko) — Tue, 28 Apr 2009 21:41:04 PDT

If you are building applications with ActiveX controls, then you can have problems distributing it, because of Admin privileges required to install ActiveX controls. There is a new COM activation model called Registration-free COM introduced in Windows XP, which can help you in this case. Read this article to find more about Reg-Free COM.

Update. One more link to read on this topic.

Access will export reports to Excel … again!

noreply@blogger.com (Alex Dybenko) — Fri, 17 Apr 2009 04:13:29 PDT

Service Pack 2 for the 2007 Microsoft Office System will bring back export to Excel feature for Access reports, finally! Thanks to Bob Larson, Access MVP, who pointed me to this link! Check out other SP2 improvements.

MS Access SQL Injection

noreply@blogger.com (Alex Dybenko) — Thu, 09 Apr 2009 05:46:58 PDT

MS Access SQL Injection Cheat Sheet, it is not so dangerous like for SQL Server, but still – if you are using Access database for your web site – be aware of it!

Access x64?

noreply@blogger.com (Alex Dybenko) — Sun, 22 Mar 2009 02:22:49 PDT

According to Ed Bott research next Access version, Access 14, will be also on x64 platform. Don’t know if we get performance improvement, but some problems can arise with external libraries and controls. 64-bit activex controls?? Looking forward to try see x64 version….

MVP Summit 2009

noreply@blogger.com (Alex Dybenko) — Sat, 07 Mar 2009 10:13:34 PST

This summit was great! We met some new MVPs, but also missed old good friends. We met product team and learned about their plans, they are working hard on a new version of Access. We missed salmon this time (still can't understand it), as well as opening sushi, but had closing sushi with Crystal and Peter. So we made it!

Here it is:

Access MVPs, aka "MVP Ness Envy", performing "I love rock'n'roll"