Link to home
Start Free TrialLog in
Avatar of jfz2004
jfz2004

asked on

"Connection with Excel Lost" Error

I am using VBA and the MS ActiveX Data Objects 6.1 Library.
I keep getting "Connection with Excel Lost" while using Excel Workbook itself as database.

Sub Test()

Dim Temp_OutRight As New ADODB.Recordset
Dim S_OutRight As String
Dim Conn As New ADODB.Connection
Dim DBPath As String, sconnect As String
DBPath = ThisWorkbook.FullName
sconnect = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & DBPath & ";HDR=Yes';"

Conn.Open sconnect

S_OutRight = "SELECT * FROM [Table_Exposures_Input]"
S_OutRight = S_OutRight & " WHERE [Table_Exposures_Input].[PeakPeriod] = 'Peak'"

Temp_OutRight.Open S_OutRight, Conn

msgbox Temp_OutRight Is Nothing

Worksheets("Temp_Table_OutRight").Cells(2, 2).CopyFromRecordset Temp_OutRight

Temp_OutRight.Close

Conn.Close

End Sub
Avatar of jfz2004
jfz2004

ASKER

Can someone help me on this one? Thanks.
Avatar of jfz2004

ASKER

How to increase points? I don't even see the place to add points. EE is not as good as before.
Avatar of David Johnson, CD
I keep getting "Connection with Excel Lost" while using Excel Workbook itself as database.

Why?  use another workbook as the database
This happens when you are trying to run a query using an ODBC connection to an Excel spreadsheet and have the spreadsheet open at the same time. Please close the Excel spreadsheet and try again.

Other possible reasons (long filename, antivirus, access rights, incompatible add-ons) are similar to ones described here:
http://support.microsoft.com/kb/271513
ASKER CERTIFIED SOLUTION
Avatar of jfz2004
jfz2004

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jfz2004

ASKER

Other comments didn't solve it.