AccessBlog.net

ImportXML method "Cannot establish connection to the server" error

noreply@blogger.com (Alex Dybenko) — Thu, 31 May 2012 04:34:49 PDT

My friend Peter, Access MVP, was recently puzzled by weird error, produced by simple line:

Application.ImportXML strPathFile, acStructureAndData

Runtime error was 31521 "Cannot establish connection to the server". There was no any network connection involved, strPathFile was as simple as "C:\Temp\Customer.xml". This xml file has been generated by:

Application.ExportXML acExportQuery, strQuery, _
strPathFile, , , , , acEmbedSchema

Fortunately Peter found, that Access Trust Center option "ActiveX Settings" was a source of such error, it was set to "Disable all controls without notification". Once it was changed to "Prompt me before enabling all controls …" – it works!

Well done, Peter!

Access to MySQL and PostgreSQL

noreply@blogger.com (Alex Dybenko) — Thu, 03 May 2012 00:50:55 PDT

bullzip.com offers 3 utilities, useful for Access developers:

Access To MySQL

Access To PostgreSQL

Access To MSSQL; for Access t0 SQL conversion we also have Upsizing tools and SSMA.

Also check out other useful utilities like Free PDF printer and Color Syntax.

Thanks to Daniel Pineault for link!

Partition function in Access

noreply@blogger.com (Alex Dybenko) — Tue, 01 May 2012 19:54:05 PDT

Once I found that Access has Partition function, which I never used. Function returns a Variant (String) indicating where a number occurs within a calculated series of ranges. You can use is to find lower and upper edge of partition where number falls. Few samples:

?Partition(33,1,100,5)
31: 35

?Partition(7,1,100,5)
6: 10

?Partition(999,1,100,5)
101:

Now I am thinking – how can I use it? Looks like I never missed such function. Do you have real-life example using Partition function?

History of Access

noreply@blogger.com (Alex Dybenko) — Tue, 24 Apr 2012 03:08:36 PDT

A Brief History of Access gives a good overview of Access versions and features. Although post title is "A Brief History of Time Wasted", I can't agree that working with Access can be waste of time.

Make SQL Server linked table more secure

noreply@blogger.com (Alex Dybenko) — Thu, 05 Apr 2012 05:34:48 PDT

Ben Clothier, Access MVP, shares a great tip on Cached ODBC connection in Access, which helps to avoid storing login information in linked tables and pass-through queries. This tip is quite useful for SQL Azure, where you can't use Windows security and have to supply login and password!

There is an interesting behavior in Access we want to take advantage of. When Access opens an ODBC connection, it caches that connection. Any subsequent ODBC objects that happen to match on three parameters—ODBC driver, server, and database—will reuse that cached connection. This means we don’t have to specify the full connection string for all ODBC objects each time. We only need to supply the complete connection string once at startup and store only the incomplete connection string. We can then leave it up to Access to match subsequent ODBC objects to that cached connection string. This helps immensely in simplifying the security setup.

How to put comma or semicolon in Listbox using AddItem

noreply@blogger.com (Alex Dybenko) — Tue, 27 Mar 2012 02:54:48 PDT

If you try to add list item, containing text with comma or semicolon, to Access listbox using .AddItem method - item will be split into rows, listbox interpret comma as separator:

Me.List0.AddItem "One, two, three"

The trick is to put string in quotes:

Me.List0.AddItem "'One, two, three'"

How to get subform control name

noreply@blogger.com (Alex Dybenko) — Wed, 14 Mar 2012 03:02:23 PDT

Say you have FormA inserted several times on FormB as subform, and you want to know subform control name from button click event on FormA – you can use this code:

MsgBox "Subform control name is: " & _
Me.Parent.ActiveControl.Name

Migrating Access tables to the cloud

noreply@blogger.com (Alex Dybenko) — Mon, 13 Feb 2012 00:12:00 PST

A great video by Albert Kallal Migrating Access tables to Office 365 steps through the process of moving Access tables to SharePoint site or Office 365 and enabling referential integrity for those tables.

No Access 15 for ARM

noreply@blogger.com (Alex Dybenko) — Sun, 12 Feb 2012 01:29:16 PST

Steven Sinofsky in his recent article Building Windows for the ARM processor architecture mentioned, that Access will not be ported to ARM architecture and touch experience. What is not much surprising, knowing how much Access hungry for processor power :)

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