Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

ODBC - Call failed . [Microsoft] [ODBC manager] Invalid argument value (#0)

Posted on 2004-11-23
9
Medium Priority
?
2,237 Views
Last Modified: 2013-12-03
I recently migrated my backend from access to SQL 2000. And I am using ODBC DSN to link SQL backend tables to ACCESS frontend.  It seems to be working fine except few queries which are all update query (with one to many relationship).

Those query generates a weird error :
ODBC - Call failed . [Microsoft] [ODBC manager] Invalid argument value (#0)

I am being able to exucute update queries on Numeric fields but it's not working on textual field. Is that a data conversion issue???


Any idea? Why would it generate above error?

I would greatly appreciate your quick response.

Regards
Hirak1977
0
Comment
Question by:hirak1977
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 1

Expert Comment

by:PolsonTech
ID: 12656460
I am not sure if this is your issue, but in times past, I have had a similar problem.  

Let's just eliminate a few possible issues...

1)  Try refreshing the links in the linked table manager and then see if you are still having problems.

2)  Then try deleting and then recreating the linked tables in your database and test again.

3)  Can you provide the Update query for us to look at?
0
 
LVL 15

Expert Comment

by:jmantha709
ID: 12656492
Can you post an example of a query that doesn't work ?  Maybe it's the character delimiters...  Try using quotes instead of double-quotes if applicable...
0
 
LVL 1

Author Comment

by:hirak1977
ID: 12656526
PolsonTech:

I am linking my tables in SQl to Access front end via ODBC manually.

1). how can i refresh then dynamically ( thru code?)

2). how?

3).

UPDATE (ShippingDetail RIGHT JOIN SellScanDetail ON ShippingDetail.scanid = SellScanDetail.ScanID) INNER JOIN PickDetailMain ON SellScanDetail.SellID = PickDetailMain.PickDetailID SET ShippingDetail.ActualShipDate = Date(), ShippingDetail.scanid = [sellscandetail].[scanid]
WHERE (((PickDetailMain.SellOrderID)=[Forms]![SellOrderDetail]![SellOrderID]));

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 1

Author Comment

by:hirak1977
ID: 12656659
Let me give you more appropriate example.

say  i have two tables:

One IS TEST:
with two fields. 1) numeric primary AA 2) . text field BB

second Main:
with 1). numeric primary AA 2) text field abc 3) date etc.

lets assume that there is no record in first table.

if i write an update query to update field AA of table 1 with table 2 and field BB of table 1 with table abc of table 2 then my query will work just fine....

but here is a scenario:

table one HAS only one field 1) AA varchar primary key and taBLE 2 REMAINS THE SAME.

now, if i write an update query (basically to add new records as well) to update AA of table 1 with field abc of MAin table. this query will not work and will result in ODBC call failed......

Hope this helps.


0
 
LVL 1

Expert Comment

by:PolsonTech
ID: 12656816
Can you look over the tables in design mode and make sure that you don't have anything unusual going on.

Another quick question....Can you manually update the record directly in the table?

Refresh the links from Access's Linked Table Manager by navigating on the menu bar:   Tools - Database Utilities - Linked Table Manager

Recreate the links to your tables by deleting the linked tables and then right clicking in empty space within the tables section and choosing LINK TABLES from the context menu.   NOW....if you have never done this before...you might try creating a link to a table in your ODBC database BEFORE you delete the existing links...just to make you feel comfortable with what you will be doing.

When linking to the tables on your SQL server, you need to know the name of the DSN you will be using and have the username and password to access the server (Unless you are using NT authentication)
0
 
LVL 15

Expert Comment

by:jmantha709
ID: 12656879
If the key to table one is a varchar and the key to table two is numeric, you can link them this way :
INNER JOIN Table1 ON Val(Table1.Key) = Table2.Key
0
 
LVL 1

Author Comment

by:hirak1977
ID: 12656885
Polsontech:

I know , how to update links manually. I have tried doing that again too. But no luck so far! I am dead man in the middle of water. Neway, I can also update record/ add new record directly into the linked table.

P.S. I am using Access Update query to not only update existing record but to also add new record based upon relationship. If the record exist, it will work just fine. But if it doesnt then it will generate ODBC error. :((

thanks!
0
 

Accepted Solution

by:
modulo earned 0 total points
ID: 14194550
PAQed with points refunded (500)

modulo
Community Support Moderator
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

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…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Suggested Courses
Course of the Month20 days, 15 hours left to enroll

810 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