Replies

Hans – thanks for the confirmation!

Fwiw… re: Leban’s mouse wheel dll solution (which I used to my satisfaction): be aware to move the dll along with the database and/or have it placed in every user’s Windows/System folder… and/or include some lines in the VBA procedure’s error handling to deal with the situation when the dll is lacking for some reason.
Take care!
Hasse

(Thanks) Hans (yes, I understand, but… ) I realise now that my actual question wasn’t clear enough.

Is it OK, certainly when recovering a (possibly) corrupted database, not to import the system objects (except eventually custom ones, created by your own while developing the database)?
(Or is there any reason why or any condition under which these ‘old ones’ are needed too?)

Hasse

Interesting explanatory post you refer to Hans .
Only for the record: this Microsoft MSDN article helped me out before I found your advise: Quotation Marks in Strings.
Take care,
Hasse

OK – got it – thanks & enjoy your weekend! – Hasse

Thanks Hans… can you clarify what you mean with ‘if done correctly’? Is there some sample code you could refer to?
Hasse

FYI: HansV referred to articles of Microsoft’s support & KB in Turn of track changes (2003)… which clarify that Word 2003 shows tracked changes automatically when opening a document (regardless whether you had them hidden before)… and what you can do about it.

Question: is there an advantage using copy-paste to get rid of all tracked changes rather than ‘accepting all changes’, when using automation?
E.g. to accept all changes in the current document, I would use something like:
Dim revLoop As Revision
Dim myRange As Range

Set myRange = Selection.Range
myRange.Wholestory
For Each revLoop In myRange.Revisions
revLoop.Accept
Next revLoop

Hasse

ps [indent]

---

(…) Someone has suggested to me that comments and annotations can re-appear – has anyone encountered this?

---

[/indent]Fwiw… yep… with tracked changes… during the upgrade process in our organisation. Previously no one cared about tracking changes. Untill suddenly, with Office 2003, these started to pop up out of the unexpected… In the long term, it might be an interesting enhancement to make you aware of this hidden info which others might be able to abuse. In situations where Office 97 users’ documents (to whom these changes are invisible by default) are processed and forwarded to upgraded users, it’s quite embarrassing though, certainly if you work with important documents and memoranda. (Because… who’s actually aware of the changes stored in the document? I guess close to no one…)
Btw… our (outsourced) IT helpdesk could only explain how to switch them off or accept all in the current document – but no more structured solution available for the several hundreds of documents we are processing. Printing to a pdf was no solution… as it just printed the visible tracked changes all along :-p. They said you can’t get rid of them (…) So not much help there… and I’m glad (again) to have the Lounge .
ps For those wanting to preserve changes with copy-paste, check this out: (Carry forward Track Changes (Word 97/2000/XP/2003)

Hey… wonderful !

(Edited by HansV to make URL clickable – see Help 19)

Catharine,
this is not my cup of tea at all, but without any answer… this may be better than nothing.
At first sight, ODBC (and/or eventually ASP?) may help to connect the dots.
A thorough google/… search might reveal some interesting articles and references.
Here’s some links I found at google:
http://www.phpfreaks.com/tutorials/61/0.php%5B/url%5D (tutorial)
http://www.tek-tips.com/viewthread.cfm?qid…42&page=124%5B/url%5D (where one member also says the combination causes a problem).
Sorry I can’t help you much further…
Hasse

Mark… I completely agree… but unfortunately that’s in the central management’s hands. So I have to (start cleaning out garbage & old files we probably won’t use anymore on a regular basis or) deal with the situation as it is…
Anyway… with the backend being less than 5MB in size, I think the 50MB holds a fair margin.
Meanwhile, I imported all healthy tables in a new database, an earlier version of the corrupt ones from a backup, assembled the recent input from the intermediate backups cleaned the corrupted records (fortunately not too many as I advised to start registering on paper)… and we’re back .
Thanks for all replies… and take care,
Hasse

FYI… my solution…

1. Public function checks free disk space
=> saved in module basGetDiskSpace

Option Compare Database
Option Explicit

'Sources
'http://www.kbalertz.com/225144/GetDiskFreeSpaceEx.Retrieve.Drive.Information.aspx
'Adapted from form_click into Msgbox function: 'http://www.developerfusion.co.uk/show/153/
'Additional reading: VBA Developers Handbook (Getz & Gilbert, Sybex)

Private Type LARGE_INTEGER
lowpart As Long
highpart As Long
End Type

Private Declare Function GetDiskFreeSpaceEx Lib "kernel32" Alias "GetDiskFreeSpaceExA" (ByVal lpRootPathName As String, lpFreeBytesAvailableToCaller As LARGE_INTEGER, lpTotalNumberOfBytes As LARGE_INTEGER, lpTotalNumberOfFreeBytes As LARGE_INTEGER) As Long

'CUSTOMISED FUNCTION: checks only available space, uses customised format & MB
'E.g. MsgBox GetAvailableDiskSpace("D:")
Public Function fGetAvailableDiskSpace(sDrive As String) As Double
Dim lResult As Long
Dim liAvailable As LARGE_INTEGER
Dim liTotal As LARGE_INTEGER
Dim liFree As LARGE_INTEGER
Dim dblAvailable As Double
If Right(sDrive, 1) "" Then sDrive = sDrive & ""
'Determine the Available Space, Total Size and Free Space of a drive
lResult = GetDiskFreeSpaceEx(sDrive, liAvailable, liTotal, liFree)

'Convert the return values from LARGE_INTEGER to doubles
dblAvailable = CLargeInt(liAvailable.lowpart, liAvailable.highpart)

'Display the results
fGetAvailableDiskSpace = Format(dblAvailable / 1024 ^ 2, "#,###.0")
End Function

Private Function CLargeInt(Lo As Long, Hi As Long) As Double
'This function converts the LARGE_INTEGER data type to a double
Dim dblLo As Double, dblHi As Double

If Lo < 0 Then
dblLo = 2 ^ 32 + Lo
Else
dblLo = Lo
End If

If Hi < 0 Then
dblHi = 2 ^ 32 + Hi
Else
dblHi = Hi
End If
CLargeInt = dblLo + dblHi * 2 ^ 32
End Function


2. Prevent users of adding, editing or deleting data
=> added in module of each form where users can add, modify or delete data

Private Sub Form_BeforeUpdate(Cancel As Integer)
If fGetAvailableDiskSpace("G:") < 50 Then '... MB
MsgBox "Insufficient space on the network (< 50MB): no data entry or changes allowed. Please press 'escape' to cancel and try again later."
Cancel = True
End If
End Sub

Private Sub Form_Delete(Cancel As Integer)
If fGetAvailableDiskSpace("G:") < 50 Then '... MB
MsgBox "Insufficient space on the network (< 50MB): no data deletions allowed. Please press 'escape' to cancel and try again later."
Cancel = True
End If
End Sub

3. Monitoring… (optional)

– user form – TimerInterval property & timer event to control the situation every … minutes and whenever necessary warn the user with a message that the network is running out of memory;
– main form – text box mentioning the free disk space status, e.g. Source=”Free disk space: ” & fGetAvailableDiskSpace(“G:”) & ” MB (refresh: F9)”

Fwiw…
– I might have found another cause of corruption: (Preventing) corruption due to no disk space (97+);
– an upgrade to an informix back end might not solve much issues when we connect to it through the same network – cosidering Charlotte’s reply in the Access Stability (XP) topic (which adds to HansV’s earlier comments):
[indent]

---

Access is extremely sensitive to network interruptions, far more so than any of the other Office apps. In fact, it acts as a sort of “miner’s canary” for network problems. Network administrators customarily blame Access for these problems, but they really indicate a situation that needs to be looked at more closely. Things like a single chattering network card on a LAN can bring Access down intermittently. That isn’t a problem with Access, it’s a network issue, but it is far easier to blame Access than to track down the wiring or other hardware problems that exist but don’t seriously affect other applications.

Moving the whole thing to SQL Server requires a database administrator and someone to convert the application and create a new front end. If the backend is SQL Server and you link the tables to an Access mdb front end, you don’t gain any more network stability because Access will not tolerate network interruptions, regardless of the back end, and will not “repair” itself when the connection is reestablished. Your one person with full Access should be using the runtime as well. There is probably a difference between the versions of the files she is using and those installed with the runtime. That will definitely lead to the kind of crashes you describe. That person can use a shortcut that includes the runtime executable to run the application without changing their ability to use the full version of Access for other things.

Charlotte
Moderator: Access, VB/VBA, Books, General Office Solutions

---

[/indent]
– we are considering using Access through Citrix… but this would require a license for every user… (600

Thanks for this info… I am close to upgrading our database from 97 upwards… hoping this would decrease the number of corruption issues here. This topic might have saved me from only exchanging one kind of trouble into another… .

FYI… also other topics touch issues with Access 2003… like Mass conversion utility where Charlotte clarifies the database bloat and HansV refers to a number other topics.

Take care,
Hasse

Thanks Hans – Hasse

Your assumption is right… except that disk space being very cheap these days has no positive effect on the available disk space over here… don’t know why :-p.

I guess I’ll make the buffer larger… like 50MB at least (for the backend of about 3-5MB)… which is unlikely to vanish within the time Access needs to save the data after the BeforeUpdate event (right?).