[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 134
  • Last Modified:

"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
0
jfz2004
Asked:
jfz2004
  • 4
1 Solution
 
jfz2004Author Commented:
Can someone help me on this one? Thanks.
0
 
jfz2004Author Commented:
How to increase points? I don't even see the place to add points. EE is not as good as before.
0
 
David Johnson, CD, MVPOwnerCommented:
I keep getting "Connection with Excel Lost" while using Excel Workbook itself as database.

Why?  use another workbook as the database
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
pcelbaCommented:
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
0
 
jfz2004Author Commented:
I found that by opening the spreadsheet with "Read-Only" mode, the problem went away.
0
 
jfz2004Author Commented:
Other comments didn't solve it.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now