P
Patrick
I have a very large program in excel VBA that has been working fine until the
other day. I have a connection to ADO that has all of a sudden seemed to
cause an error. I receive the error that "Office Excel has encountered an
error and needs to close." If i out a message box right before the procedure
runs that i have narrowed the error down to, it runs fine. Also, it doesnt
cause an error when the userform loads if i have the VBA editor open. This
error occurs when I go to open the userform. Excel 2003, doesnt matter which
Windows version I am running, I have Vista at home, and XP, and Windows 2000
at work I think.
Here is the code where i believe the error happens:
Public Sub Load_Customer_alerts(ByRef oAlerts As Collection) 'loads alerts
to oAlerts and fills combobox with customer names. then loads cust criteria
Dim starttimer As Long
starttimer = Timer
Debug.Print "Call Procedure - Load_Customer_Alerts - " & Now
Dim IDcount As ClsAlertData
Dim CustAlert As ClsAlertData
Dim rs2 As ADODB.Recordset
'MsgBox "Right before load_DB_Connections", vbOKOnly
*****ERROR happens here i Think*****
Set rs2 = frmSearch.dbconn.Execute("SELECT * FROM tblAlerts;")
'if there are records then continue
Set oAlerts = New Collection
If Not rs2.BOF And Not rs2.EOF Then ' if it exists then....
rs2.MoveFirst
Do While Not rs2.EOF
DoEvents
Set CustAlert = New ClsAlertData
---------------------------------------------------------------------------
This is how I set up the ADO connection:
In Frmsearch(the start up form), I have in general declarations.
Public dbconn As ADODB.Connection
then in a seperate module I set up the ADO connection so that I can use it
publicly:
Public Sub GetCn(ByRef dbcon As ADODB.Connection, ByRef dbrs As
ADODB.Recordset, sqlstr As String, dbfile As String, usernm As String, pword
As String)
Set dbcon = New ADODB.Connection
dbcon.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbfile & ";", _
usernm, pword
Set dbrs = New ADODB.Recordset
dbrs.Open sqlstr, dbcon, adOpenDynamic, adLockOptimistic
End Sub
------------------------------------------------------------------------
I got plenty more code if this doesn't make sense.
At work today I added a line that loaded a form right before the problem
code, and unloaded it after the problem code, and it doesnt seem to work. It
seems like it is loading too much information for excel to handle. Help!
other day. I have a connection to ADO that has all of a sudden seemed to
cause an error. I receive the error that "Office Excel has encountered an
error and needs to close." If i out a message box right before the procedure
runs that i have narrowed the error down to, it runs fine. Also, it doesnt
cause an error when the userform loads if i have the VBA editor open. This
error occurs when I go to open the userform. Excel 2003, doesnt matter which
Windows version I am running, I have Vista at home, and XP, and Windows 2000
at work I think.
Here is the code where i believe the error happens:
Public Sub Load_Customer_alerts(ByRef oAlerts As Collection) 'loads alerts
to oAlerts and fills combobox with customer names. then loads cust criteria
Dim starttimer As Long
starttimer = Timer
Debug.Print "Call Procedure - Load_Customer_Alerts - " & Now
Dim IDcount As ClsAlertData
Dim CustAlert As ClsAlertData
Dim rs2 As ADODB.Recordset
'MsgBox "Right before load_DB_Connections", vbOKOnly
*****ERROR happens here i Think*****
Set rs2 = frmSearch.dbconn.Execute("SELECT * FROM tblAlerts;")
'if there are records then continue
Set oAlerts = New Collection
If Not rs2.BOF And Not rs2.EOF Then ' if it exists then....
rs2.MoveFirst
Do While Not rs2.EOF
DoEvents
Set CustAlert = New ClsAlertData
---------------------------------------------------------------------------
This is how I set up the ADO connection:
In Frmsearch(the start up form), I have in general declarations.
Public dbconn As ADODB.Connection
then in a seperate module I set up the ADO connection so that I can use it
publicly:
Public Sub GetCn(ByRef dbcon As ADODB.Connection, ByRef dbrs As
ADODB.Recordset, sqlstr As String, dbfile As String, usernm As String, pword
As String)
Set dbcon = New ADODB.Connection
dbcon.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbfile & ";", _
usernm, pword
Set dbrs = New ADODB.Recordset
dbrs.Open sqlstr, dbcon, adOpenDynamic, adLockOptimistic
End Sub
------------------------------------------------------------------------
I got plenty more code if this doesn't make sense.
At work today I added a line that loaded a form right before the problem
code, and unloaded it after the problem code, and it doesnt seem to work. It
seems like it is loading too much information for excel to handle. Help!