?
Solved

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

Posted on 2006-06-28
10
Medium Priority
?
683 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

764 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