ImportXML method "Cannot establish connection to the server" error

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

It turned out 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!

Thanks for sharing, Peter!

Access to MySQL and PostgreSQL

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

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

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

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

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

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