Hey there,

I am gettinga very frustrating error which, no matter what I try,
cannot be shifted. It is the dreaded "connection cannot be used" but
as far as I can see (and this has only suddenly stopped working) there
is nothing that has changed. The error appears when the condb.open
statement passes, giving "Unspecified Error" and then when the
recordset is opened usig this db connection. The code is as follows:

Dim conDB As ADODB.Connection

Const DatabasePath As String = "C:\Working\db1.mdb"

Sub SetUpConnection()
On Error GoTo Errhandler
Set conDB = New Connection
conDB.Provider = "Microsoft.Jet.OLEDB.4.0" 'Change to 3.51 for
Access 97
conDB.ConnectionString = DatabasePath
conDB.Open
Exit Sub
Errhandler:
MsgBox Err.Description, vbExclamation, "An error occurred"
End Sub

Public Function funcGetAcctDesc(strAcctDest As String) As String
Dim rsLookup As ADODB.Recordset
Dim strSQL As String
On Error GoTo Errhandler
If conDB Is Nothing Then SetUpConnection
If (strAcctDest < "") Then
Set rsLookup = New ADODB.Recordset
strSQL = "SELECT tAcctMain.amCode, tAcctMain.amDesc " & _
"FROM tAcctMain " & _
"WHERE (tAcctMain.amCode = '" & strAcctDest & "') " & _
"ORDER BY tAcctMain.amCode DESC"
rsLookup.Open strSQL, conDB, adOpenForwardOnly, adLockReadOnly
If rsLookup.BOF And rsLookup.EOF Then
funcGetAcctDesc = ""
Else
funcGetAcctDesc = rsLookup.Fields("amDesc").Value
End If
rsLookup.Close
Set rsLookup = Nothing
Exit Function
Else
funcGetAcctDesc = ""
End If
Exit Function
Errhandler:
MsgBox Err.Description, vbExclamation, "An error occurred"
End Function