I am facing a quite strange problem. I have written a VBA code which
is called using Command Button. It opens the SQL database and
retrieves some numbers which are entered into excel cells.
Surprisingly, the code works if the VBA window is open. If it is
closed, the code returns empty cells. All the Subs and Functions are
public. The code is very simple with a straight forward SQL sentence.
Does anybody have any ideas where might be the problem? Many thanks.

Justina

Let us see the code, or the critical portion so we can see what is happening.
--
Pops Jackson


" wrote:

I am facing a quite strange problem. I have written a VBA code which
is called using Command Button. It opens the SQL database and
retrieves some numbers which are entered into excel cells.
Surprisingly, the code works if the VBA window is open. If it is
closed, the code returns empty cells. All the Subs and Functions are
public. The code is very simple with a straight forward SQL sentence.
Does anybody have any ideas where might be the problem? Many thanks.

Justina

I added 3 subs where I suspect the problem might be. The first sub is
called by command button which calls the GetFund_ID function. The
latter connects to the DB and should retrieve the IDs. I also added
the EnsureConnection Sub, though it does not seem to be a problem
because it is used in other codes.
The worst thing is that I cannot trace the bug because if i open the
VBA window, the code works.

-------------------------
Public Sub ImportButton_Click()
Dim i As Integer
Dim Error As Integer
Dim Result As Variant

On Error GoTo Handler:
Error = 0

'retrieve fund_id's from DB

For i = 1 To 50
If Sheet9.Cells(9, i + 1) = "" Then Exit For
Call GetFund_ID(Sheet9.Cells(9, i + 1), Result)
Sheet9.Cells(10, i + 1).Value = Result
Next i

<..importing..

'restore connection with DB. I am using 2 DBs, therefore i have to
restore connection with the default one. The RestoreConnection sub is
almost the same as EnsureConnection, just with different parameters.

RestoreConnection

MsgBox ("The import is finished")

If Error = 1 Then
MsgBox ("Connection with DBfailed. Please try again.")

End If

Exit Sub

Handler:
Error = 1
Resume Next

End Sub


-------------------------------------------------


Public Sub GetFund_ID(RefCol As String, Result As Variant)
Dim strSQL As String

If Application.VBE.MainWindow.Visible Then
Debug.Print "System Report:" & strSQL


strSQL = strSQL & "SELECT Fund_id FROM Funds WHERE Fund_Name like
'%"
strSQL = strSQL & RefCol
strSQL = strSQL & "%'"

EnsureConnection

Dim rs As Recordset: Set rs = OpenRecordsetCache(Cache, Cn, strSQL)
Result = ADODBExcel.FormatRecordset(rs)

End If

End Sub


------------------------------------------

Public Sub EnsureConnection()
Dim SQLString As String

SQLString = "Provider=SQLOLEDB.1;Password=Password1;"
SQLString = SQLString & "Persist Security Info=True;User
ID=User1;"
SQLString = SQLString & "Initial Catalog="
SQLString = SQLString & CStr([DBNAME])
SQLString = SQLString & ";Data Source=ServerName
\DatabaseName;"
SQLString = SQLString & "Use Procedure for Prepare=1;Auto
Translate=True;"
SQLString = SQLString & "Packet Size=4096;Workstation
ID=PCID;Use Encryption for Data=False;"
SQLString = SQLString & "Tag with column collation when
possible=False"

Set Cn = New ADODB.Connection
Cn.ConnectionString = SQLString

Cn.Open

SaveSetting "DBName", "Database", "ConnectionString",
Cn.ConnectionString
End Sub