Excel Vba Adodb Unspecified Error
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company adodb connection open unspecified error Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs adodb recordset unspecified error Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, run-time error '-2147467259 (80004005)': just like you, helping each other. Join them; it only takes a minute: Sign up Unspecified run time error while executing vba script up vote 0 down vote favorite I am new to vba and I am using vba script to connect to database from excel and get the records. I have written the following script for that.I am getting a run time error '-2147467259(80004005)':Unspecified error. How to resolve this error. See the error screen shot. Sub Ora_Connection() Dim con As ADODB.Connection Dim rs As ADODB.Recordset Dim query As String Set con = New ADODB.Connection Set rs = New ADODB.Recordset '--- Replace below highlighted names with the corresponding values strCon = "Driver={Microsoft ODBC for Oracle}; " & _ "CONNECTSTRING=(DESCRIPTION=" & _ "(ADDRESS=(PROTOCOL=TCP)" & _ "(HOST=host_name)(PORT=1521))" & _ "(CONNECT_DATA=(SERVICE_NAME=service_name))); uid=id; pwd=pw;" '--- Open the above connection string. con.Open (strCon) '--- Now connection is open and you can use queries to execute them. '--- It will be open till you close the connection query = "select * from security.forms" Set rs = con.Execute(query) For i = 0 To rs.Fields.Count - 1 Sheet1.Cells(1, i + 1).Value = rs.Fields(i).Value Next con.Close End Sub Error screen shot: excel oracle vba share|improve this question asked Jul 18 '14 at 12:52 Sandeep Reddy K. 3283518 What happens when you click debug? Which line is highlighted? –Gareth Jul 18 '14 at 12:56 Set rs = con.Execute(query) –Sandeep Reddy K. Jul 18 '14 at 12:57 Have you tested your query select * from security.forms work on a DB client? –Gareth Jul 18 '14 at 13:01 yes, When I execute this query in toad it is givng the records. –Sandeep Reddy K. Jul 18 '14 at 13:03 Are you sure the values in the connection string are correct? Looking at it you've got SERVICE_NANE=service_name along with uid=id and pwd=pw –Gareth Jul 18 '14 at 13:08 | show 2 more comments 3 Answers 3 active oldest votes up vote 0 down vote Had y
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up What is causing “Automation error Unspecified error” on http://stackoverflow.com/questions/24825520/unspecified-run-time-error-while-executing-vba-script Worksheet_Activate? up vote 2 down vote favorite 2 I have a worksheet named "Dates" (object name is A_Dates) that needs to be calculated when it is activated (It may be worth noting that this is in my Personal macro workbook). I regularly have workbooks open that have too many calculations in the for me to have auto-calculation on. So I have auto-calc set to manual, and the http://stackoverflow.com/questions/12270922/what-is-causing-automation-error-unspecified-error-on-worksheet-activate following code in the worksheet: Private Sub Worksheet_Activate() A_Dates.Calculate End Sub This has worked fine for the last 3 months, day-in and day-out. Yesterday, it stopped working. It now throws this error on the declaration line: Microsoft Visual Basic Automation error Unspecified error [OK] [Help] I have tried changing how I reference the sheet, using: Sheets("Dates").Calculate and ActiveSheet.Calculate to no avail. I've also included error handling: On Error Resume Next which doesn't prevent it. I've even gone so far as: Private Sub Worksheet_Activate() On Error GoTo headache Sheets("Dates").Calculate Exit Sub headache: Exit Sub End Sub and it still shows up. I am totally at a loss. Help? Additional Information I have the following references, and use all of them in various macros in this workbook: Visual Basic for Applications Microsoft Excel 12.0 Object Library OLE Automation Microsoft Office 12.0 Object Library Microsoft Scripting Runtime Microsoft Forms 2.0 Object Runtime Microsoft HTML Object Library Microsoft Internet Controls Microsoft ActiveX Data Objects 2.8 Library Microsoft ActiveX Data Objects Recordset 2.8 Library vba error-handling excel-2007 share|improve this question edited Sep 4 '12 at 21:43 asked Sep 4 '12 at 20:30 Farfromunique 1311112 If you comment that line out and manuall
open a recordset invb6 If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: http://www.vbforums.com/showthread.php?522663-quot-Unspecified-error-quot-when-I-try-to-open-a-recordset-invb6 click the register link above to proceed. To start viewing messages, select the https://www.experts-exchange.com/questions/20872144/2147467259-Unspecified-error-Using-ADODB.html forum that you want to visit from the selection below. Results 1 to 7 of 7 Thread: "Unspecified error" when I try to open a recordset invb6 Tweet Thread Tools Show Printable Version Subscribe to this Thread… Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode May 13th, 2008,03:28 PM unspecified error #1 LydiaW View Profile View Forum Posts Thread Starter Junior Member Join Date Apr 2008 Posts 22 "Unspecified error" when I try to open a recordset invb6 I am using vb6 and sql server 2000 db. I got an error "Unspecified error" when I try to open a recordset. Code: strSQL ="SELCT * FROM Cust WHERE Cust_Num ='" strdata & "' " set rcs = new excel vba adodb ADODB.RECORDSET rcs.Open strSQL, Conn, adOpenKeyset , adLockOptimistic Can anyone tell me what cause it? Thanks a lot. Reply With Quote May 13th, 2008,03:41 PM #2 si_the_geek View Profile View Forum Posts Super Moderator Join Date Jul 2002 Location Bristol, UK Posts 38,306 Re: "Unspecified error" when I try to open a recordset invb6 Thread moved to Database Development forum What does strdata contain? Which line does the error occur on? How have you declared & set up Conn? (July 2007 to June 2017) . . . . . . . . . . Hitchhiker's Guide to Getting Help at VBForums Classic VB FAQs (updated Oct 2010) ...Database Development FAQs/Tutorials (updated May 2011) (includes fixing common VB errors) .......... (includes fixing common DB related errors, and [Classic VB] ADO tutorial /further steps, and [VB.Net] ADO.Net Tutorial). Tutorial: How to automate Excel from VB6 (or VB5/VBA) .•. SQL 'Select' statement formatter/checker .•. Convert colour number to colour name .•. FlexGrid: fill from recordset .•. FlexGrid: AutoSize columns .•. DB Reserved Words checker Connection strings .•. MDAC/Jet/ACE downloads .•. SQL Server downloads .•. MZTools (free upgrade for the VB6/VBA Editor) Reply With Quote May 13th, 2008,03:49 PM #3
for Help Receive Real-Time Help Create a Freelance Project Hire for a Full Time Job Ways to Get Help Ask a Question Ask for Help Receive Real-Time Help Create a Freelance Project Hire for a Full Time Job Ways to Get Help Expand Search Submit Close Search Login Join Today Products BackProducts Gigs Live Careers Vendor Services Groups Website Testing Store Headlines Experts Exchange > Questions > -2147467259 Unspecified error (Using ADODB) Want to Advertise Here? Solved -2147467259 Unspecified error (Using ADODB) Posted on 2004-02-03 Visual Basic Classic 1 Verified Solution 6 Comments 3,057 Views Last Modified: 2013-12-25 Hi, We have a VB application running of Laptops using Wireless Network adapaters for connecting to the central server Oracle 8.1.6 database. Our application maintains a persistent Database Connection (ADODB.Connection) object that it creates during application start up and uses that for any Database calls made while application is running. We are getting the following runtime error time to time: -2147467259 Unspecified error. in the following call: rs.Open sSQL, myConnection(), adOpenStatic, adLockReadOnly where myConnection() function returns the ADODB.Connection that we create during application start up. The connection is created using: Set mDBConn = New ADODB.Connection mDBConn.Provider = "MSDataShape" mDBConn.ConnectionString = "Data Provider=MSDASQL;uid=" & sUID & ";Pwd=" & sPWD & ";DSN=" & sData To take care of this problem I implemented a retry logic in myConnection() object. I didnot find any method that can return me whether the Connection object is valid or not, except to try a database call, and check if any runtime error came, using below: On Error Resume Next Set cnd = New ADODB.Command Set cnd.ActiveConnection = mDBConn If (Err) Then Debug.Print "In retry logic" If (mDBConn.State <> adStateClosed) Then mDBConn.Close End If mDBConn.Open ENd If Using the above retry code, it is going into the "Err" conditon more often. I dont think it was generating so many