Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

"Connection with Excel Lost" Error

Posted on 2015-01-29
6
Medium Priority
?
130 Views
Last Modified: 2016-02-10
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
Comment
Question by:jfz2004
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
6 Comments
 

Author Comment

by:jfz2004
ID: 40577690
Can someone help me on this one? Thanks.
0
 

Author Comment

by:jfz2004
ID: 40578130
How to increase points? I don't even see the place to add points. EE is not as good as before.
0
 
LVL 83

Expert Comment

by:David Johnson, CD, MVP
ID: 40579118
I keep getting "Connection with Excel Lost" while using Excel Workbook itself as database.

Why?  use another workbook as the database
0
Independent Software Vendors: 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!

 
LVL 43

Expert Comment

by:pcelba
ID: 40579340
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
 

Accepted Solution

by:
jfz2004 earned 0 total points
ID: 40589483
I found that by opening the spreadsheet with "Read-Only" mode, the problem went away.
0
 

Author Closing Comment

by:jfz2004
ID: 40597970
Other comments didn't solve it.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
Simple Linear Regression
Progress
Suggested Courses

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question