AccessBlog.net

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

About Me Search
Alex
Name:Alex Dybenko

Location:Moscow, Russia

Wednesday, November 30, 2005

Do not forget to download a copy of VB6...

... if you MSDN Subscriber. Visual Basic 6 (and whole Visual Studio 6) will gone from MSDN Downloads in June 2006. Microsoft will continue to support products throughout each product’s support lifecycle, but will no longer offer these products.

Friday, November 25, 2005

How to deploy Access project (ADP)

Once you ready to deploy a new version of your Access project (ADP/ADE) – you should delete existing connection string, else, when Access starts the project on client’s PC - it will produce a message that it can not connect to SQL Server. The trick – is to clear connection string using Application.CurrentProject.OpenConnection "". Also you can look at Microsoft KB article How to deploy an Access 2003 project that connects to an existing SQL Server 2000 database for more details on ADP deployment.

Thursday, November 24, 2005

More rules for naming in Access

As I already wrote – developer could have problems, if he uses non-English characters in object names and VBA code. What else developer should aware? First of all - List of reserved words in Access 2002 and Access 2003 and List of Microsoft Jet 4.0 reserved words, these two always good to have in mind and avoid using reserved words. Also do not forget about Special characters that you must avoid when you work with Access databases.

You can completely avoid using reserved words, if you follow some naming convention. I personally prefer using Reddick VBA Naming Conventions, which actually is recommended in our bible - Access Developer's Handbook, by Litwin Getz, et al. Access MVP Tony Toews published his Table and Field Naming Conventions, which also good to follow.

Another Access MVP Jeff Conrad has a page with all latest links to naming convention articles, which is good to check from time to time.

Wednesday, November 23, 2005

Why do we use Access to develop custom solution?

Luke Chung, President of FMS, wrote a great article “Database Evolution: Microsoft® Access within an Organization's Database Strategy”, a number of perfect arguments to show our customers and their IT guys when they ask “Why Access?” I think with new Access version we could add some more arguments, for example we can publish Access data on web server in few clicks!

Monday, November 21, 2005

Replacement for Linked Tables Manager

David Fenton has built a Reconnect Utility Database – good replacement for native Linked Tables Manager, so you can connect your tables to multiple backends, even different types of backends, not just Jet.

Saturday, November 19, 2005

AddItem and RemoveItem for Listboxes in Access 97/2000

Access XP and 2003 listboxes already have these methods. I made 2 functions which manipulate Listbox’ RowSource property. You have to set Listbox Row Source Type property to Value List and then you can add items using:
ListBox1.RowSource = AddItem(ListBox1.RowSource, "key1" & ";" & "New Item", 0)
And remove:
ListBox1.RowSource = RemoveItem(ListBox1.RowSource, 0)

These 2 functions assumes, that Listbox has 2 columns. For one-column code version please look at Update for "AddItem and RemoveItem for Listboxes in Access 97/2000"

Public Function RemoveItem(strRowSource As String, _
lngItemNum As Integer) As String
Dim Pos1 As Long, strResult As String, Pos2 As Long
Dim lngCount As Long, i As Integer

strResult = strRowSource
Pos1 = 1
For i = 0 To lngItemNum
If (Pos1 > 0) And (i = lngItemNum) Then
Pos2 = InStr(Pos1 + 1, strRowSource, ";")
Pos2 = InStr(Pos2 + 1, strRowSource, ";")
If Pos2 = 0 Then Pos2 = Len(strRowSource)
strResult = Left(strRowSource, Pos1 - 1)
If Len(strResult) > 0 Then _
strResult = strResult & ";"
strResult = strResult & _
Mid(strRowSource, Pos2 + 1)
If Right(strResult, 1) = ";" Then _
strResult = _
Left(strResult, Len(strResult) - 1)
End If

Pos1 = InStr(Pos1 + 1, strRowSource, ";")
If Pos1 > 0 Then
Pos1 = InStr(Pos1 + 1, strRowSource, ";")
End If
Next i

RemoveItem = strResult
End Function
Public Function AddItem(strRowSource As String, _
strItem As String, _
lngItemNum As Integer) As String
Dim Pos1 As Long, strResult As String, Pos2 As Long
Dim lngCount As Long, i As Integer

strResult = strRowSource
Pos1 = 1
For i = 0 To lngItemNum
If (Pos1 > 0) And (i = lngItemNum) Then
strResult = Left(strRowSource, Pos1 - 1)
If Len(strResult) > 0 Then _
strResult = strResult & ";"
strResult = strResult & strItem
If Len(strResult) > 0 Then _
strResult = strResult & ";"
If Pos1 > 1 Then Pos1 = Pos1 + 1
strResult = strResult & _
Mid(strRowSource, Pos1)
If Right(strResult, 1) = ";" Then _
strResult = _
Left(strResult, Len(strResult) - 1)
Exit For
End If

Pos1 = InStr(Pos1 + 1, strRowSource, ";")
If Pos1 > 0 Then
Pos1 = InStr(Pos1 + 1, strRowSource, ";")
If Pos1 = 0 Then Pos1 = Len(strRowSource) + 1
End If
Next i

AddItem = strResult
End Function


Tuesday, November 15, 2005

How to clear SQL Server database

Database Journal just published a very useful script to Truncate Database by Shane Lively. What I found also interesting there – that you can disable constraints/triggers for certain table using ALTER TABLE MyTable NOCHECK CONSTRAINT ALL and ALTER TABLE MyTable DISABLE TRIGGER ALL, and reset identity seed using DBCC CHECKIDENT ("MyTable", RESEED, 0). But what I don’t understand – why author uses Delete instead of Truncate, which is faster and more efficient?

Tuesday, November 08, 2005

Preparing Your Access 2003 Database for Deployment

Two parts MSDN article, discussing several aspects developer can aware when he plan to deploy application:
Preparing Your Access 2003 Database for Deployment, Part 1
Preparing Your Access 2003 Database for Deployment, Part 2

I would like to add 3 more advises:

Thursday, November 03, 2005

Access to PDF conversion tool

Stephen Lebans, Access MVP, just posted a first beta of Access to PDF conversion tool. This is an Access 2003 database containing a function to convert Reports and Snapshot files to PDF documents. No PDF Printer driver is required. We all know now that Access 12 will natively support export to PDF, but still this is quite useful tool, as people likes using older versions and also like to output reports to PDF. Access 2000 and Access XP version coming soon.