Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Invalid Argument in an ODBC Append Query, text fields too long...

Posted on 2006-06-28
10
Medium Priority
?
693 Views
Last Modified: 2008-02-01
...Greetings Everyone!

I have an append query that takes data from text boxes located on a form, and then appends it to a linked Oracle table. If the user enters more than ~100 characters, Access gives the Invalid Argument error.

This post explains a possible solution - hardcoding the query instead of calling it:

http://www.experts-exchange.com/Databases/MS_Access/Q_20492703.html

However, when I do that, I now get an error that says this, so clearly that solution doesn't work:

ODBC--insert on a linked table "TABLE_NAME" failed.

[Oracle][ODBC]No data at execution values pending. (#0)

Please help, I'm running out of ideas and I have to get this resolved today...:(
0
Comment
Question by:DwayneZandbergen
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
  • +1
10 Comments
 
LVL 26

Expert Comment

by:dannywareham
ID: 17002719
If it always fails at 100 characters, do you have a field limit in teh Oracle system?
0
 
LVL 18

Expert Comment

by:Data-Man
ID: 17002907
Are there single or double quotes in the text?  This can cause problems if you don't properly handle them.

Thanks,
Mike
0
 
LVL 1

Author Comment

by:DwayneZandbergen
ID: 17003185
The field limit in Oracle s 400 characters, I'm saying that they're failing at approximately 100 characters. There are no quotes in the text.
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 18

Expert Comment

by:Data-Man
ID: 17003521
Can you post the SQL before it is sent to oracle?  Not the expression that builds it, but the actual SQL.

Mike
0
 
LVL 54

Expert Comment

by:nico5038
ID: 17003618
Hmm, why not bind the textbox to the linked Oracle table ?
Can't you use a subform ?

Nic;o)
0
 
LVL 1

Author Comment

by:DwayneZandbergen
ID: 17004727
I would rather not bind it if I have to, in part because of inefficiencies invovled with binding and also because I want to work through this issue because I'm stubborn. The SQL code is as follows:

Dim strSQL As String
   
    strSQL = "INSERT INTO BKV_DATA_PA_MAINASSESS ( PROCNUM, PROCOMM, PERFATNUM, PERFATCOMM, ASSMANNUM, ASSMANCOMM, PERFDELNUM, PERFDELCOMM, TMNORMNUM, TMNORMCOMM, CREATNUM, CREATCOMM, PERFNUM, PERFCOMM, SRINUM, SRICOMM, INTERNUM, INTERCOMM, CONTNUM, CONTCOMM, YN, ASSESSING, IND, TEAM ) " _
    & "SELECT '" & [Forms]![frmEntry]![cboProcedures] & "' AS Expr1, '" & [Forms]![frmEntry]![txtProcedures] & "' AS Expr2, '" & [Forms]![frmEntry]![cboPerformanceAttitude] & "' AS Expr3, '" & [Forms]![frmEntry]![txtPerformanceAttitude] & "' AS Expr4, '" & [Forms]![frmEntry]![cboAssetManagement] & "' AS Expr5, '" & [Forms]![frmEntry]![txtAssetManagement] & "' AS Expr6, '" & [Forms]![frmEntry]![cboPerformanceDelivery] & "' AS Expr7, '" & [Forms]![frmEntry]![txtPerformanceDelivery] & "' AS Expr8, '" & [Forms]![frmEntry]![cboTeamNorms] & "' AS Expr9, '" & [Forms]![frmEntry]![txtTeamNorms] & "' AS Expr10, '" & [Forms]![frmEntry]![cboCreativity] & "' AS Expr11, '" & [Forms]![frmEntry]![txtCreativity] & "' AS Expr12, '" & [Forms]![frmEntry]![cboPerformance] & "' AS Expr13, '" & [Forms]![frmEntry]![txtPerformance] & "' AS Expr14, '" & [Forms]![frmEntry]![cboSPI] & "' AS Expr15, '" & [Forms]![frmEntry]![txtSPI] & "' AS Expr16, '" & [Forms]![frmEntry]![cboInterpersonal] & "' AS Expr17, '" _
    & [Forms]![frmEntry]![txtInterpersonal] & "' AS Expr18, '" & [Forms]![frmEntry]![cboContent] & "' AS Expr21, '" & [Forms]![frmEntry]![txtContent] & "' AS Expr22, '" _
    & [Forms]![frmEntry]![txtYourName] & "' AS Expr19, '" & [Forms]![frmEntry]![cboVictim] & "' AS Expr20, '" _
    & Format(Now(), "yyyymmddhhnss") & "' AS Expr24, '" & Relative() & "' AS Expr23;"
0
 
LVL 18

Expert Comment

by:Data-Man
ID: 17004800
When you run the code can you set a break point after this line processes and then post the value of strSQL?

Also....Two things
     1)    When I create an insert statement I like to use the syntax  -  INSERT INTO (Field1, Field2, etc) Values (Value1, Value2, etc)
     2)    In VBA I prefer to use form_frmEntry.cboProcedures   if you use form_frmEntry you will get the intellisense help when you press the period.  Saves you from having to memorize all the control names.

Thanks,
Mike

   
0
 
LVL 54

Accepted Solution

by:
nico5038 earned 1500 total points
ID: 17005457
Hmm, with such a long query string combined with a memo field you're running the risk to get a syntax error when going over the limit Access uses to store a string value.
When you want to use a query for the storing and not bind the fields to a linked table, the a workaround could be to use a one row temp table and issue an append from the filled row to the linked table.

Nic;o)
0
 
LVL 1

Author Comment

by:DwayneZandbergen
ID: 17010796
Binding the form to a temporary table, and then using an append query to transfer from the temporary table to Oracle seems to have done the trick. Thanks Nico, and blast Microsoft for their roundabout ways!
0
 
LVL 54

Expert Comment

by:nico5038
ID: 17011088
Or I need to be gratefull for their ways as they give plenty of questions here.... :-)

Success with your aaplication !

Nic;o)
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

636 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