AccessBlog.net

How to get most recent DBEngine object

noreply@blogger.com (Alex Dybenko) — Thu, 26 Jan 2012 22:23:01 PST

Below is Graham Mandeno's version of VBA GetDBEngine(), now in VB.NET. If user have several versions of DAO installed this function returns reference to latest available.

 1:  Public Function GetDBEngine() As Object

 2:  Dim aVersions() As String = {"120", "36", "35"}

 3:  Dim i As Integer

 4:  GetDBEngine = Nothing

 5:  Try

 6:   For i = LBound(aVersions) To UBound(aVersions)

 7:    Try

 8:      GetDBEngine = CreateObject("DAO.DBEngine." & _
        aVersions(i))

 9:      Exit For

 10:   Catch ex As Exception When Err.Number = 429

 11:   'continue after "Cannot create ActiveX component"

 12:   Catch

 13:    Throw

 14:   End Try

 15:   If i = UBound(aVersions) Then _ 

        Throw New System.Exception("No known version" & _
        " of DAO is available")

 16:   Next

 17:  Catch ex As Exception

 18:   Throw New System.Exception("Cannot create " & _
      "DBEngine" & vbCrLf & ex.Message)

 19:  End Try

 20:  End Function

How to make Windows 8 work for you

noreply@blogger.com (Alex Dybenko) — Fri, 13 Jan 2012 22:45:12 PST

These 2 steps you need to do if you want to do some coding/testing on Windows 8, rather than playing with Metro UI.

1. Switch to Window 7 type Start menu
Navigate using RegEdit to: HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer
Set registry key RPEnabled to 0

How to get a Windows 7 start menu in Windows 8

2. Disable UAC completely
Navigate using RegEdit to: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System
Set registry key EnableLUA to 0

How To (Really) Completely Disable UAC on Windows 7

Replace special characters for XML

noreply@blogger.com (Alex Dybenko) — Mon, 26 Dec 2011 02:41:20 PST

If you are writing custom function for XML export – do not forget to replace predefined XML characters, else XML parsing function may fail. There are only 5 characters, so function looks pretty simple:

Function XMLSpecialChars(ByVal varText As Variant) As String 
  varText = varText & "" 
  varText = Replace(varText, "&", "&amp;") 
  varText = Replace(varText, "'", "&apos;") 
  varText = Replace(varText, """", "&quot;") 
  varText = Replace(varText, ">", "&gt;") 
  varText = Replace(varText, "<", "&lt;") 
  XMLSpecialChars = varText 
End Function

Lost in conversion? Excel's Convert() will help you

noreply@blogger.com (Alex Dybenko) — Wed, 30 Nov 2011 02:35:50 PST

Quite useful Convert() function, if you need to convert inches to meters or gallons to cups. Excel's CONVERT function for metric and more article shows usage samples and measurement list.

Multi-value Fields - Appending / Updating with query

noreply@blogger.com (Alex Dybenko) — Wed, 23 Nov 2011 04:14:29 PST

Access 2007 introduced Multi-value fields (MVF), some people like it, some – hate it. I personally like UI for MVF in Access, but don't like realization in table level – you can't get access to junction table of MVF. Anyway - any Access developer have to learn working with it, sooner or later, and here something your can look at.

A.D. Tejpal, Access MVP, has published Multi-value Fields - Query Based Solution For Bulk Appending / Updating sample database, which shows several techniques to work with MVF.

Convert linked table to local

noreply@blogger.com (Alex Dybenko) — Wed, 16 Nov 2011 02:24:59 PST

There is an easy way to convert linked table to local in Access 2010, in other words – import linked table into local database, using acCmdConvertLinkedTableToLocal command:

 DoCmd.SelectObject acTable, "Company", True RunCommand acCmdConvertLinkedTableToLocal

Command is mentioned in Office Help, but not really documented. Thanks to fellow MVPs for pointing to it!

Access source control software

noreply@blogger.com (Alex Dybenko) — Thu, 03 Nov 2011 06:20:05 PDT

Source control software is quite useful when several people work on the same project same time; one can take ownership on certain object or module (check-out), make modification, put it back (check-in), and other developers get changes you made. I have used myself Visual Source Safe, long time ago, in Access 97 time. Last version, Visual SourceSafe 2005, will retire from mainstream support on 10 July 2012, and now you can use Team Foundation Server, which offers similar functionality. Recently I learned about OASIS-SVN versioning system, which is much cheaper than TFS. Do you know other source control software for Access? Are you happy using it? Please share your experience!

Microsoft Access and SQL Azure

noreply@blogger.com (Alex Dybenko) — Thu, 20 Oct 2011 20:01:51 PDT

Great resource for developers, have interest in SQL Azure, by

Patrick Wood, Access MVP:

Microsoft Access and SQL Azure Information Center for Developers

And check out other useful stuff in Free Downloads and Free Code

How to remove VBA project password

noreply@blogger.com (Alex Dybenko) — Wed, 12 Oct 2011 06:56:50 PDT

Originally it was posted for Excel VBA, but apparently works for Access.

Make backup.
Open the file with a hexadecimal editor, find the string "DPB" and change it to "DPx".
Then you open the file in Excel, switch to the VBA project and change the password (don't remove it, just change it). Save the file, reopen it, go to VBA project (using the password you've just created) and remove the password.

How to link FoxPro tables

noreply@blogger.com (Alex Dybenko) — Fri, 30 Sep 2011 21:37:32 PDT

In Access 97 FoxPro ISAM was included in setup, and linking FoxPro table was as easy as Access or DBF table. It was even a nice fox icon to linked FoxPro table:

Then it was deprecated in Access 2002 (or 2000?), and now you can only connect using ODBC.

First you need to install Visual FoxPro ODBC Driver. Then try to create new DSN to some FoxPro table using ODBC Data Source Administrator. If it works – you can link FoxPro table to Access database.

Following code shows how to create linked table using VBA:

Set tdf = dbs.CreateTableDef(strTableName, 

   _ dbAttachExclusive, 

   _ strFileName, 

   _ "ODBC;DSN=Visual FoxPro Tables;SourceDB=" & strFolder & 

   _ ";SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;" & 

   _ "Collate=Machine;Null=Yes;Deleted=Yes;" & 

   _ "TABLE=" & strFileName) 

dbs.TableDefs.Append tdf dbs.TableDefs.Refresh

Preview Access report at 100% zoom

noreply@blogger.com (Alex Dybenko) — Mon, 26 Sep 2011 07:20:12 PDT

Now, with bigger monitors, it is better to preview report at 100% zoom, rather than fit it to screen, so you can see it at actual size, not zoomed to a whole screen. It's easy to change zoom level on preview, but such setting is missing in report design. There is a "Auto Resize" report property, which controls this, when you set it to False (No) – your report will be previewed at 100% zoom.

Controls do not display their Control Tips

noreply@blogger.com (Alex Dybenko) — Tue, 13 Sep 2011 01:25:49 PDT

When you have controls on a form grouped with rectangle, and want them to show Control Tips, it can happen that some controls do not display Control Tips unless you move focus in the control or click on it.

The problem is in rectangle, which z-order is above controls. To fix it - put rectangle below all controls by selecting it and using menu Format>Send to Back or ribbon Arrange>Send to Back.

Hotfix: Running query takes longer than expected

noreply@blogger.com (Alex Dybenko) — Fri, 09 Sep 2011 03:02:01 PDT

Access 2010 hotfix package (Ace-x-none.msp): August 30, 2011

Issue that this hotfix package fixes:

Assume that you run a query in Access 2010 on a computer that is running Windows 7 or Windows Server 2008 R2. In this situation, the taskbar displays "Running query" together with a process bar and it takes longer than expected to return the query results.
Note This issue does not occur if you run the query in Access 2010 on a computer that is running Windows XP.

Workaround for “Win 7 SP1 breaks ADO”

noreply@blogger.com (Alex Dybenko) — Thu, 25 Aug 2011 04:15:00 PDT

This is a follow-up to Windows 7 SP1 breaks ADODB, here a MS KB article with typelibs, you can download, which resolves the issue:

An ADO application does not run on down-level operating systems after you recompile it on a computer that is running Windows 7 SP 1 or Windows Server 2008 R2 SP 1 or that has KB983246 installed

TSQL and xQuery magic

noreply@blogger.com (Alex Dybenko) — Tue, 23 Aug 2011 23:03:15 PDT

A person from StackOverflow helped me to find simple SQL to put field names and corresponding values of one row into text. If you run SQL below on Northwind database:

select T2.N.value('local-name(.)', 'nvarchar(128)')+': '+   
       T2.N.value('.', 'nvarchar(max)') 
from (select * 
      from Customers 
      Where CustomerID='ALFKI' 
      for xml path(''), type) as T1(X) 
  cross apply T1.X.nodes('/*') as T2(N)

You will get result as:

CustomerID: ALFKI 
CompanyName: Alfreds Futterkiste 
ContactName: Maria Anders 
ContactTitle: Sales Representative 
Address: Obere Str. 57 
City: Berlin 
PostalCode: 12209 
Country: Germany 
Phone: 030-0074321 
Fax: 030-0076545

Very simple SQL, although I have to learn what really means the last line.

Here the original discussion: TSQL: Values of all field in a row into one string

Workaround for Access 2010 SP1 crash

noreply@blogger.com (Alex Dybenko) — Wed, 20 Jul 2011 21:59:12 PDT

How fix Access crash after installing SP1: Acc2010: You receive an error in Microsoft Access using the built-in wizards after installing Microsoft Office 2010 SP1

Problems reported on Office 2010 SP1

noreply@blogger.com (Alex Dybenko) — Wed, 13 Jul 2011 08:52:46 PDT

Office 2010 SP1 and Access 2010 Runtime SP1 are broken Cypris' lookout

Limitations of SQL Azure: only one DB per connection

noreply@blogger.com (Alex Dybenko) — Tue, 05 Jul 2011 06:55:15 PDT

If you don’t know yet – Access can work with SQL Azure database, like it works with SQL database, you can link tables and run pass-through queries.Works great! I did some work recently on porting SQL server app to SQL Azure, and found that you can’t use following syntax:
Select [SupplierID] From [AlexDB]..Suppliers
It fails with "reference to database and/or server name in is not supported in this version of sql server" error.
Fellow MVP pointed me to SQL Azure and relational data : Limitations of SQL Azure article, which explains why it fails: its nature is similar to USE restriction – you can use one connection with only one SQL Azure database.

SQL Server Development Techniques and Scripts

noreply@blogger.com (Alex Dybenko) — Fri, 01 Jul 2011 05:02:29 PDT

Just found few interesting samples here: SQL Server Development Techniques and Scripts - a great resource of Transact-SQL scripts and other SQL Server code and techniques

Running 32 and 64-bit versions of Access Database Engine 2010 together

noreply@blogger.com (Alex Dybenko) — Thu, 30 Jun 2011 05:45:30 PDT

According to Tony’s post you have to add /passive switch and install 64-bit version from command line after you have 32-bit version running. And keep in mind, that side-by-side installations is unsupported scenario.

Deprecated Database Engine Features in SQL Server 2008 R2

noreply@blogger.com (Alex Dybenko) — Tue, 31 May 2011 06:47:23 PDT

If you are planning to migrate your SQL Server DB to next SQL Server version – check this article: Deprecated Database Engine Features in SQL Server 2008 R2. And even not planning – worth reading it.

Classic VB is 20!

noreply@blogger.com (Alex Dybenko) — Mon, 30 May 2011 03:03:29 PDT

Classic VB is 20 and still missed by many. I also still use VB6, and do not see any replacement. VB functions/classes libraries, ActiveX controls, complex user interface forms – this you can easy build in VB6 and use in Access application. What about you?

Jet Replication Wiki

noreply@blogger.com (Alex Dybenko) — Thu, 26 May 2011 05:39:04 PDT

David Fenton's replication wiki:

What is Jet Replication?
How does Jet Replication work?
Resources for learning about Jet Replication
Replication Wiki Frequently Asked Jet Replication Questions (FAQ)
Things not to believe in the MS documentation
Best Practices

Working with Compressed (zipped) folders in MS Access

noreply@blogger.com (Alex Dybenko) — Tue, 10 May 2011 22:58:17 PDT

How to use Windows Shell object to zip and unzip files – read this article by Doug Steele, Access MVP.

Word 2010 Command Finder

noreply@blogger.com (Alex Dybenko) — Fri, 06 May 2011 22:19:56 PDT

Office-Watch.com: "We’ve become really frustrated with finding a feature in Word, especially in the command list. So we’ve done something about it with the new online Command Finder for Word 2010"

Useful tool! One thing I found frustrating - "now searching all 1,752 commands listed by Word 2010..." takes several seconds, like it is 1,752,000 commands or even more. Interesting – what database they are using? Access can do this much faster :)