Solved

"Connection with Excel Lost" Error

Posted on 2015-01-29
6
111 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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This is about my first experience with programming Arduino.
If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
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.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

808 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