AccessBlog.net

News, links, downloads, tips and tricks on Microsoft Access and related

About Me Search
Alex
Name:Alex Dybenko

Location:Moscow, Russia

Monday, April 30, 2012

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?

Labels:

Tuesday, April 24, 2012

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.

Labels:

Thursday, April 05, 2012

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.

Labels: , ,