Solved

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

Posted on 2006-06-28
10
658 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 500 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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying 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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

735 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