• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 697
  • Last Modified:

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...:(
0
DwayneZandbergen
Asked:
DwayneZandbergen
  • 3
  • 3
  • 3
  • +1
1 Solution
 
dannywarehamCommented:
If it always fails at 100 characters, do you have a field limit in teh Oracle system?
0
 
Data-ManCommented:
Are there single or double quotes in the text?  This can cause problems if you don't properly handle them.

Thanks,
Mike
0
 
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
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
Data-ManCommented:
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-ManCommented:
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
 
nico5038Commented:
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
 
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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 3
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now