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=Ex cel Files;DBQ=" & DBPath & ";HDR=Yes';"
Conn.Open sconnect
S_OutRight = "SELECT * FROM [Table_Exposures_Input]"
S_OutRight = S_OutRight & " WHERE [Table_Exposures_Input].[P eakPeriod] = 'Peak'"
Temp_OutRight.Open S_OutRight, Conn
msgbox Temp_OutRight Is Nothing
Worksheets("Temp_Table_Out Right").Ce lls(2, 2).CopyFromRecordset Temp_OutRight
Temp_OutRight.Close
Conn.Close
End Sub
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=Ex
Conn.Open sconnect
S_OutRight = "SELECT * FROM [Table_Exposures_Input]"
S_OutRight = S_OutRight & " WHERE [Table_Exposures_Input].[P
Temp_OutRight.Open S_OutRight, Conn
msgbox Temp_OutRight Is Nothing
Worksheets("Temp_Table_Out
Temp_OutRight.Close
Conn.Close
End Sub
ASKER
How to increase points? I don't even see the place to add points. EE is not as good as before.
I keep getting "Connection with Excel Lost" while using Excel Workbook itself as database.
Why? use another workbook as the 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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Other comments didn't solve it.
ASKER