Oracle Odbc Syntax Error Or Access Violation. #0
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss error [42000] [oracle][odbc]syntax error or access violation the workings and policies of this site About Us Learn more about
Microsoft Odbc Sql Server Driver Syntax Error Or Access Violation
Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow oracle syntax error or access violation Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Microsoft ODBC driver for Oracle Syntax error or access violation (-2147217900) up vote 0 down vote favorite I have a large VB program that connects to Oracle database. strCn = "Driver={Microsoft ODBC for Oracle};" & _ "SERVER=PSPROD;" Set Cn = New ADODB.Connection Cn.ConnectionString = strCn Cn.CursorLocation = adUseNone Cn.Open There are many users of my program so I have a table that contains each user's login name and their access rights to the various tables. I create a recordset of all users when the program is started and then select USERNAME and GRANTED_ROLE from the record set where USERNAME and PASSWORD are found. I use a "Set role 'GRANTED_ROLE' identified by 'password'" statment and Cn.Execute statement to set up the user's access rights. This is all done in a Module. On a form, I want to call a Stored Procedure that will SELECT, INSERT and UPDATE information into another schema's tables. I am able to call and run the stored procedure when I create a new connection to the database with this code: Dim cmd5040 As ADODB.Command Dim conn5040 As ADODB.Connection Dim param5040 As ADODB.Parameter Set conn5040 = New ADODB.Connection conn5040 = "Driver={Microsoft ODBC for Oracle};" & _ "SERVER=PSPROD; UID=XXXXXXX; PWD=XXXXXXXX" conn5040.Open Set cmd5040 = New ADODB.Command With cmd5040 .ActiveConnection = conn5040 .CommandType = adCmdStoredProc .CommandText = "S4115040_IMPORT_NEWBIDITEMSPES.S4115040_CheckTime" .Parameters.Append .CreateParameter(, adInteger,
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: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. Results 1 to 8 of 8 Thread: "between #1/1/2001# and #12/31/2001#" doesn't work Tweet Thread Tools Show Printable Version Subscribe to this Thread… Search Thread http://stackoverflow.com/questions/2030531/microsoft-odbc-driver-for-oracle-syntax-error-or-access-violation-2147217900 Advanced Search Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode 02-03-12,14:06 #1 chuck_forbes View Profile View Forum Posts Registered User Join Date Dec 2003 Posts 1,074 Unanswered: "between #1/1/2001# and #12/31/2001#" doesn't work We're finally moving from Access97 to Access2010, and something that used to work, now throws an: Code: http://www.dbforums.com/showthread.php?1674653-quot-between-1-1-2001-and-12-31-2001-quot-doesn-t-work "ODBC call failed. [Oracle][ODBC] Syntax error or access violation. (#0)" We can't put a date range into a query, like: Code: between #1/1/2001# and #12/31/2001# or really, any kind of date comparison (like "> #1/1/2001#") without generating that error. If I set up the dates as parameters and get prompted for them at runtime, everything runs as expected. Did this functionality get de-supported in Access2010? Thanks, --=Chuck Reply With Quote 02-03-12,14:22 #2 r937 View Profile View Forum Posts Visit Homepage SQL Consultant Join Date Apr 2002 Location Toronto, Canada Posts 20,002 dude, you're using msaccess syntax on an oracle database -- you gots to use oracle syntax rudy.ca | @rudydotca Buy my SitePoint book: Simply SQL Reply With Quote 02-03-12,15:02 #3 Sinndho View Profile View Forum Posts Moderator Join Date Mar 2009 Posts 5,440 Provided Answers: 14 As Access is capable of performing some translation, you could try: Code: "between " & #1/1/2001# & " and " & #12/31/2001# I do
Tips/Tricks Top Articles Beginner Articles Technical Blogs Posting/Update Guidelines Article Help Forum Article Competition Submit an article or tip Post your http://www.codeproject.com/Questions/60972/Syntax-error-or-access-violation Blog quick answersQ&A Ask a Question View Unanswered Questions View All Questions... Linux questions C# questions ASP.NET questions SQL questions fabric questions discussionsforums All Message Boards... Application http://p2p.wrox.com/access-vba/18533-using-pass-through-query-call-oracle-sp.html Lifecycle> Running a Business Sales / Marketing Collaboration / Beta Testing Work Issues Design and Architecture ASP.NET JavaScript C / C++ / MFC> ATL / WTL / syntax error STL Managed C++/CLI C# Free Tools Objective-C and Swift Database Hardware & Devices> System Admin Hosting and Servers Java .NET Framework Android iOS Mobile SharePoint Silverlight / WPF Visual Basic Web Development Site Bugs / Suggestions Spam and Abuse Watch features Competitions News The Insider Newsletter The Daily Build Newsletter Newsletter archive Surveys Product Showcase syntax error or Research Library CodeProject Stuff communitylounge Who's Who Most Valuable Professionals The Lounge  The Insider News The Weird & The Wonderful The Soapbox Press Releases Non-English Language > General Indian Topics General Chinese Topics help What is 'CodeProject'? General FAQ Ask a Question Bugs and Suggestions Article Help Forum Site Map Advertise with us About our Advertising Employment Opportunities About Us Ask a Question All Questions All Unanswered FAQ Syntax error or access violation Rate this: Please Sign up or sign in to vote. See more: VB VB.NET hi..... Given below is my code to insert data int a table : USER. Dim myInsertQuery As String = "INSERT INTO USER (USERID,PASSWORD) Values('roll', 'name')" Dim mycommand As New OdbcCommand(myInsertQuery) mycommand.Connection = myConnection mycommand.ExecuteNonQuery() But the code is not working properly...i am getting the error message given below .....can any one suggest a solution please,thanks in advance.. 'ERROR [42000] [Sybase][ODBC Driver][Adaptive Server Anywhere] 'Syntax error or access violation: near 'USER' in INTO [
Unanswered Topics Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA Using a Pass-Through Query to call an Oracle SP User Name Remember Me? Password Reminder Password Register Register | FAQ | Members List | Calendar | Today's Posts | Search Access VBA Discuss using VBA for Access programming. Search Forums Show Threads Show Posts Advanced Search Find All Thanked Posts Go to Page... Welcome to the p2p.wrox.com Forums. You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free . Thread Tools Display Modes #1 (permalink) September 17th, 2004, 04:41 AM Richelou Registered User Join Date: Sep 2004 Location: , , . Posts: 2 Thanks: 0 Thanked 0 Times in 0 Posts Using a Pass-Through Query to call an Oracle SP Hi, I'm trying to do just that. I've successfully managed to perform inserts, updates, deletes etc through a PTQ but I'm calling a working Stored Procedure in our Oracle 9i dbase and getting the following error message: ODBC--CAll Failed [Oracle][ODBC]Syntax error or access violation (#0) Any ideas? Thanks, #2 (permalink) September 17th, 2004, 04:56 AM Richelou Registered User Join Date: Sep 2004 Location: , , . Posts: 2 Thanks: 0 Thanked 0 Times in 0 Posts Aha! After a couple of days of searching I found the answer! And after only minutes of posting on here... No need to answer, but for those that need this VERY elusive command, here it is. It's not simply execute stored_proc, its...: {call tablespace.stored_proc}; Including the brackets! Thanks for reading lol!! « Previous Thread | Next Thread » Thread Tools Show Printable Version Email this Page Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is Off HTML code is OffTrackbacks are Off Pingbacks are On Refbacks are Off Similar Threads Thread Thread Starter Forum Replies Last Post call oracle function using oracle link server vl SQL Server 2000 1 July 12th, 2007 08:19 AM MSAccess pass-through