-1

Recently, my Access .mdb database started intermittently not allowing Access (both Access 2003 and 2007) to quit. If I quit (whether by pressing the X button or from the menu, then it closes the database and appears to exit Access, as well, but then it suddenly reappears (without any database open). The only way for me to exit at that point is from the task manager.

There are two significant changes that I did recently that might be related. 1) I started using the WinSCP .Net assembly to access an ftp server, which I had to install and register for COM from the instructions here. 2) I started using ODBC, first as a linked table, and then from VBA ADO code (see this). I doubt that this second change is causing this problem because I've had the problem both when I was using the linked tables and when with ADO.

This doesn't happen every time I open the database, and I haven't noticed a pattern. What could be causing this strange problem?

Edit - I found the root of the problem. By breaking my ftp download code at various points and seeing whether it will exit, I narrowed it down to the following:

Dim PDFFolders As Recordset
Set PDFFolders = CurrentDb.OpenRecordset("PDFFolders")
Dim syncOptions As TransferOptions
Set syncOptions = New TransferOptions
syncOptions.filemask = "*/*.pdf"
On Error Resume Next    'In case it doesn't exist

Do While Not PDFFolders.EOF
    sess.SynchronizeDirectories SynchronizationMode_Local, info!RTFFolder, _
            info!BasePDFFolder & "/" & PDFFolders!Name, False, , , _
            syncOptions

    PDFFolders.MoveNext
Loop
PDFFolders.Close
Set syncOptions = Nothing
Set PDFFolders = Nothing
On Error GoTo 0

If it runs the sess.SynchronizeDirectories statement, then access won't exit, otherwise it does. Looks to me like a bug win WinSCP.

I can do other things like downloading files, creating directories, etc. with no problem, but when it gets to this statement, it doesn't exit access afterwards.

Community
  • 1
  • 1
clum
  • 469
  • 4
  • 20
  • Does the problem persist if you temporarily remove the call to SynchronizeDirectories()? If it vanishes, you should be able to reproduce the problem with a single call to SynchronizeDirectories without the loop over the Recordset. – paulroho Sep 30 '13 at 11:30
  • @Paul Rohorzka - The problem does go away when I remove the call, and it does happen with just a single call. I was thinking of making a skinned-down program with just a session connection and a call to SynchronizeDirectories to test, but I didn't see the point. It is probably a bug in WinSCP. The SynchronizeDirectories feature probably isn't used much, and likely no-one else has tried using it in VBA, so the problem never came up. I posted a bug report to their forum. – clum Sep 30 '13 at 12:32

2 Answers2

2

Sticky instances of Access usually result from hanging object references.

If Access hangs the way you described, I would suspect a nasty circular reference.

To investigate on circular references, you basically have two options:

  1. Inspect your code on circular dependencies - That might become tedious and not so easy. But if you know your code base deeply, you might have suspects where to look first.
  2. Add logging to your code - In case you cannot spot the problem via inspection alone, you can consider adding consequent logging of object creation/deletion (through Class_Initialize/Class_Terminate). For larger code bases using classes heavily, this is a good investment to start with.
    If your problem is with classes where you cannot control the code (as is your case), you might consider using that external classes only through wrapper classes where you can log creation/deletion. Of course in tricky cases, termination of the wrapper class does not mean termination of the wrapped class.

BTW, I strongly recommend to make sure to set every object reference explicitly to Nothing ASAP:

Set MyObj = GetMyObject()
' Proceed with coding here later
' First write the following line
Set MyObj = Nothing

Special thoughts have to be given in the case of local error handling to make sure to set the reference to Nothing in either case.

A good way to ensure this is using a With-block instead of an explicit variable (if the usage pattern allows to):

With GetMyObject()
   ' Use the object's members here
End With

With this pattern you save declaring the local variable and can be sure that the object reference does not survive the current method.

paulroho
  • 1,180
  • 1
  • 9
  • 25
  • I'm on vacation for a week, so I won't be able to check it out, but I noticed just before I left that it happens whenever a specific method is run, so it should be easy to track down. I will let you know when I figure it out. Thanks for the detailed response. – clum Sep 19 '13 at 19:19
  • I found the statement that's causing the problem - see my edited question. – clum Sep 30 '13 at 05:43
1

I still think it's a bug in WinSCP, but I found a workaround. I noticed that it only happened if I took the information from a table in the database, and not if I put in a hard-coded string. So I just added & vbNullString, which concatenates a blank string, which changes the data type from a Field to a String, and now it doesn't happen anymore.

clum
  • 469
  • 4
  • 20