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

...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...:(
LVL 1
DwayneZandbergenAsked:
Who is Participating?
 
nico5038Connect With a Mentor Commented:
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
 
dannywarehamCommented:
If it always fails at 100 characters, do you have a field limit in teh Oracle system?
0
 
Data-ManCOOCommented:
Are there single or double quotes in the text?  This can cause problems if you don't properly handle them.

Thanks,
Mike
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
DwayneZandbergenAuthor Commented:
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
 
Data-ManCOOCommented:
Can you post the SQL before it is sent to oracle?  Not the expression that builds it, but the actual SQL.

Mike
0
 
nico5038Commented:
Hmm, why not bind the textbox to the linked Oracle table ?
Can't you use a subform ?

Nic;o)
0
 
DwayneZandbergenAuthor Commented:
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
 
Data-ManCOOCommented:
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
 
DwayneZandbergenAuthor Commented:
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
 
nico5038Commented:
Or I need to be gratefull for their ways as they give plenty of questions here.... :-)

Success with your aaplication !

Nic;o)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.