Solved

"Connection with Excel Lost" Error

Posted on 2015-01-29
6
114 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
  • 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 80

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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 42

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

When we want to run, execute or repeat a statement multiple times, a loop is necessary. This article covers the two types of loops in Python: the while loop and the for loop.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
The viewer will learn how to use the return statement in functions in C++. The video will also teach the user how to pass data to a function and have the function return data back for further processing.

713 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