Application-defined Or Object-defined Error Query Table
Contents |
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 application defined or object defined error vba pivot table Learn more about Stack Overflow the company Business Learn more about hiring developers or application defined or object defined error excel posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow application defined or object defined error access 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 “Application Defined or Object Defined” error in VBA-SQL connection application defined or object defined error 32809 up vote 0 down vote favorite I am trying to write an Exce-Vba code for SQL connection. The code, first will open the connection to the server, then it will copy a 4 columns of table (Range("C22:G81")) from my Excel-sheet to the SQL-server (I am only trying to send numerical table now as a test, I don't send any column name) I have been trying to solve
Application Defined Or Object Defined Error In Macro
a "Application Defined or Object Defined" error quite long time. I get the error for the connection string strCon = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & strName & ";Extended Properties=""Excel 12.0;" I even tried with another version with password option like strCon = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & strName & ";Extended Properties=""Excel 12.0; Jet OLEDB:Database Password='passwd';" But I get the same error. I am quite new in SQL-coding. I wonder if I am missing something important. Lasly, I don't know if it is related to this error, but I manually created 4 columns in the SQL server for my 4 columns in the Excel. Do I need to write something specific that those 4 columns in the Excel-sheet will find the right columns in the SQL-server? Thanks in advance... The code: Private Sub inlasning() Dim MyWorkBook As Workbook Dim rs As New ADODB.Recordset Dim conn As New ADODB.Connection Dim ServerName As String, DataBaseName As String, strSQL As String Set conn = New ADODB.Connection ServerName = "E45c7642" DataBaseName = "Tables" ' Specify the OLE DB provider conn.Provider = "sqloledb" ' Set SQLOLEDB connection properties conn.Properties("Data Source").Value = ServerName conn.Properties("Initial Catalog").Value = DataBaseName ' Windows NT authentication. conn.Properties("Integrated Security").Value = "SSPI" conn.Open Dim ValidSheet As Worksh
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
Application Defined Or Object Defined Error Vba 1004
this site About Us Learn more about Stack Overflow the company Business Learn application defined or object defined error vba formula more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question application defined or object defined error vba range 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 http://stackoverflow.com/questions/32230851/application-defined-or-object-defined-error-in-vba-sql-connection QueryTables error up vote 1 down vote favorite I have the following VBA code inside an Excel macro: With ActiveSheet.QueryTables.Add( _ Connection:=ConnStr, _ Destination:=ActiveSheet.Range("A1"), _ Sql:=SqlStr) .Refresh End With And yet I am getting the error: "Run-time error '1004': Application-defined or object-defined error." The debugger points to this code as the source of error. Assuming that my ConnStr and SqlStr are correct, why am http://stackoverflow.com/questions/4567150/querytables-error I getting this error? I am using Excel 2007 and I am querying an Oracle database. oracle excel vba excel-vba excel-2007 share|improve this question asked Dec 30 '10 at 23:47 ktm5124 6,58383969 1 That part of your code looks OK (again, assuming ConnStr and SqlStr are syntactically correct). That error message is unfortunately very generic. I would check the surrounding code to make sure that it contains no errors. –bernie Dec 31 '10 at 0:20 add a comment| 1 Answer 1 active oldest votes up vote 1 down vote accepted Thanks for the reply, Adam. I managed to fix the problem. The problem was with my Connection and SQL strings. I needed to prepend "ODBC" to my conn string, and then I needed to add some spaces to my SQL string. I forget which caused the error. share|improve this answer answered Dec 31 '10 at 1:13 ktm5124 6,58383969 add a comment| Your Answer draft saved draft discarded Sign up or log in Sign up using Google Sign up using Facebook Sign up using Email and Password Post as a guest Name Email Post as a gues
soon) Ruby (coming soon) Getting Started Code Samples Resources Patterns and Practices App Registration Tool Events Podcasts Training API Sandbox https://msdn.microsoft.com/en-us/library/office/gg264310.aspx Videos Documentation Office Add-ins Office Add-in Availability Office Add-ins Changelog http://www.mrexcel.com/forum/excel-questions/68044-error-1004-application-defined-object-defined-error.html Microsoft Graph API Office 365 Connectors Office 365 REST APIs SharePoint Add-ins Office UI Fabric Submit to the Office Store All Documentation https://www.yammer.com/ http://feeds.feedburner.com/office/fmNx Office VBA language reference Visual Basic language reference Error Messages Error Messages Application-defined or object-defined error Application-defined or object-defined defined or error Application-defined or object-defined error #Else clause must be preceded by a matching #If #Else If, #Else, or #End If must be preceded by a matching #If #ElseIf must be preceded by a matching #If or #ElseIf and followed by an #ElseIf, #Else, or #End If
Forums Excel Questions Error 1004 Application defined or object defined error Page 1 of 2 12 Last Jump to page: Results 1 to 10 of 14 Error 1004 Application defined or object defined errorThis is a discussion on Error 1004 Application defined or object defined error within the Excel Questions forums, part of the Question Forums category; I must be getting old. I have a userform setup to enter data into a worksheet Whee the user hits ... LinkBack LinkBack URL About LinkBacks Bookmark & Share Digg this Thread!Add Thread to del.icio.usBookmark in TechnoratiTweet this thread Thread Tools Show Printable Version Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Dec 1st, 2003,10:12 AM #1 Peter Rouse New Member Join Date Feb 2002 Posts 14 Error 1004 Application defined or object defined error I must be getting old. I have a userform setup to enter data into a worksheet Whee the user hits the button to add the data, I get the error 1004 Application defined or Object defined error It is on the line Set LastRow Private Sub CommandButton1_Click() Dim LastRow As Object Set LastRow = Sheet1.Range("a65536").End(x1Up) ....end sub Now I am sure this has always worked in the past, so Is it me or am I really missing a plank or two? Thanks Peter Thanks Peter Share Share this post on Digg Del.icio.us Technorati Twitter Reply With Quote Dec 1st, 2003,10:36 AM #2 Joe4 MrExcel MVPModerator Join Date Aug 2002 Posts 34,558 Re: Error 1004 Application defined or object defined error It should be: Set LastRow = Sheet1.Range("a65536").End(xlUp) not Set LastRow = Sheet1.Range("a65536").End(x1Up) Share Share this post on Digg Del.icio.us Technorati Twitter TIPS FOR FINDING EXCEL SOLUTIONS 1. Use the built-in Help that comes with Excel/Access 2. Use the Search functionality on this board 3. A lot of VBA code can be acquired by using the Macro Recorder. "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!" Reply With Quote Dec 1st, 2003,10:45 AM #3 Peter Rouse New Member Join Date Feb 2002 Posts 14 Re: Error 1004 Application defined or object defined error Thanks jmiskey, I just knew it was somehting I had done, but kept looking at the wrong bit of code I Promise to remove blinkers I promise to remove..... Peter Share Share this post on Digg Del.icio.us Technorati Twitter Reply With Quote May 12th, 2009,07:03 AM #4 Fidget321 New Member Join Date May 2009 Posts 4 Re: Error 1004 Application defined or object defined error Hi All, I know this thread is old, but i am getting the exact same error. My macro gets its data from a database and just gets it into a nice format for users. Now this has been working for years a