Web Browser control in MS Access form craches when Internet Explorer 7 is installed.

We have MS Access XP application that has form with embedded Web Browser control, that is used to show links.
The application was build on development machine with IE 6 installed.

  It was found that if  Internet Explorer 7 is installed on a client machine, attempt to open URL in embedded Web Browser control causes Access to crach with C0000005 error.
We came out with the simplest solution( may be not acceptable for others) to completely exclude  Access form with embedded control and  open links in the full browser, installed on client.

Search Access database source code

If you have MS Access database(or project ADP) and it is used SourceSafe for version control,

the simplest way to search VBA source code is to use Visual Studio “Search in Files” function pointed to <path>YourDatabase.scc folder.

Note that it will also search form and reports properties and controls declarations, which can be quite handy.
I am using it with Access XP.
May be, latest virsions of Access have integrated search, similar to VS.

Overflow error when opening form with MSCal.ocx in Access 2002

Our Access XP application uses calendar control(MSCAL.OCX).
It works fine on most machines, however some sites reported “Overflow” error(6) when they tried to open a form with a calendar.
It’s possibly related to problems, described in the articles http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b236529 or http://support.microsoft.com/default.aspx?scid=kb;en-us;Q311219

Our support team managed to workaround the problem by manually copy expected version of mscal.ocx instead of wrong one.

Possible reasons for Access messages “Data Being Updated by Another User”

I was asked to investigate, why in Access 2000 linked table(to SQL server database) any change in data was refused with message returning “Data Being Updated by Another User”.

The actual message could be misleading, because it returns if Update SQL returns 0 records back.
Access creates dynamic SQL passing in WHERE clause current values of fields.
However if column was created as BIT NULL and actually has NULL value, access sends in WHERE columnName=0 , which causes mismatch.
The problem happened in MS Access 2000 and MS Access XP.

Another issue in  Access 2000(not a problem in XP) is if your DateTime column stores seconds(or seconds with milliseconds). The access passes back in WHERE value in format ‘Dec 8 2006 5:59PM’ truncating seconds and milliseconds, which causes mismatch.

Possible  workarounds:
1. Run SQL
 update Table  set BitNullableColumn=0 where BitNullableColumn is null

update Table  set DateTimeColumn=Convert(nvarchar(30) ,DateTimeColumn, 0)

2. Create your BIT columns as NOT NULL.

3. Do not use old versions of Access( hope that new versions of Access handle the issue correctly)

4. Do not use  Access at all.

5. Update: I was adviced that the way to solve this problem is to add a timestamp field to each row. When you do this, Access uses an optimistic locking strategy and is able to recognise if you’re altering the same version of the record. Timestamps heve been supported since (at least) Access 95.

Link Child/Master fields in ADP do not work for Numeric key

A long time ago I reported the problem with MS Access ADP Reports/Forms with subforms/subreports where Link
Master/Child fields are numeric but not integer (i.e scale greater than zero). 
I had the join field  defined as numeric(28.2).
However Access doesn’t recognize scale and generate incorrect SQL
statements, eg
exec sp_executesql N’SELECT  *  FROM “dbo”.”PickSlipOverdues” WHERE
((@P1 = “PatronID”))’, N’@P1 numeric(28)’, 7
instead of correct , N’@P1 numeric(28.2)’, 7.01
As a result join doesn’t work and subform is not populated.
The parent form is bound to the table with primary field defined as
numeric(28.2), child subforms are bound to the view or select
statements that has the same link field definition -numeric(28.2)

Today I had to fix the report with subreports where again Master/Child fields link fields are numeric (e.g . [BookID] [numeric](28, 2))
The workaround is to add to RecordSource  of each report/subreport string version of the key , e.g. (,cast(BookID as nvarchar(25)) as sBookID )
After this it is possible to specify sBookID as Master/Child fields and Access correctly generates string joins. 

Note: This post originally appeared on an thespoke web site, but site is down at the moment and the text was recovered from G o o g l e‘s cache

problems attempting to install a 2002 access deployment package on a machine with Windows 2003

Someone asked me with relation to my post Access 2003 Package Wizard – a big step BACK.
“I was wondering if you have run into problems attempting to install a 2002 access deployment package on a machine with 2003.  Does not seem to work.“

I’ve emailed him the following answer:

You should check MSDN article :http://support.microsoft.com/?id=837150 “You may receive error messages when you install an Access 2002 runtime deployment package on a computer that is running Windows XP SP2 or Windows Server 2003”

 I am using batch file Win2003Setup.bat:

@rem Required to run to avoid error during “Access 2002” Setup
@rem “Office System Pack could not be installed on this computer as it requires Windows NT Service pack 6 or later.”
copy ….TroubleShootingsdbmsadsn.dll %windir%system32
@rem Required to exclude registration of MSCAL.OCX  to avoid error during “Access 2002” Setup
cscript MSCAL_NoRegister.vbs
Setup.exe

where dbmsadsn.dll  is located in  ….TroubleShootings subfolder on my installation

and MSCAL_NoRegister.vbs file is the following:

Option Explicit
    Const ForReading = 1, ForWriting = 2, ForAppending = 3
    Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
ReplaceLineTest

Sub ReplaceLineTest()

Dim FSO ‘As FileSystemObject
Dim fSrc, fTrgt ‘As TextStream
Dim sFileName, sBackupName, sToFind, sReplace, strg
sFileName = “Setup.lst”
sBackupName = sFileName & “.Bac”
sToFind = “@MSCAL.OCX,$(AppPath),$(DLLSelfRegister)”
sReplace = “@MSCAL.OCX,$(AppPath),”
Set FSO = CreateObject(“Scripting.FileSystemObject”)
On Error Resume Next
 FSO.CopyFile sFileName, sBackupName, False
On Error GoTo 0
Set fSrc = FSO.OpenTextFile(sBackupName, ForReading)
Set fTrgt = FSO.OpenTextFile(sFileName, ForWriting)
Do While fSrc.AtEndOfStream <> True
    strg = fSrc.ReadLine
    strg = Replace(strg, sToFind, sReplace)
    fTrgt.WriteLine (strg)
Loop
fSrc.Close
fTrgt.Close
Set FSO = Nothing
End Sub

 

Access 2003 Package Wizard – a big step BACK.

I am supporting Access XP(2002) Project application and recently found problems with SourceSafe 2005.

Because I’ve got a new machine, I decided to install the latest Access 2003. The ADP file was retrieved from SourceSafe and I didn’t have any major problems with upgrading.

However when I wanted to create Setup Project, I found that the old MOD 2002 Packaging Wizard is not available any more, but a new Access 2003 Developere Extensions Package Wizard should be used.

I found the following issues with the new package wizard.

1.No way to convert existing .pks file that was used by Packaging Wizard in MOD XP to access 2003 developer extensions Package Wizard.
2. Not able to be installed in nested folders under the installation folder
3. Installation and registration of COM components is not supported 
4.If the user installed office to a non default location, then all shortcuts generated by the wizard are wrong
5.If the user install application to a non default location, the shortcut in Start/Programs is wrong.
6.There is no way to create shortcuts in Start Menu|Programs subfiolder like
Start Menu|Programs|e-Knowledge Manager|e-Library Utilities and no way to create additional shortcuts.
7. I didn’t find a way to allow overwrite previous version by installer. I have to manually uninstall previous version before installing a new one.

Additionally there are new for Access 2003 security warnings that I have to address in some way.

Also I’ve tried to use access 2003 developer extensions Custom Sturtup Wizard, which hangs taking 100% CPU. 

So the options that I have are: Use some another setup tool (e.g. Inno as it is suggested here) or stay with Access XP.

At the moment I decided to return back to Access 2002 with MOD 2002.