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

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
LVL 1
hirak1977Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PolsonTechCommented:
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
jmantha709Commented:
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
hirak1977Author Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

hirak1977Author Commented:
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
PolsonTechCommented:
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
jmantha709Commented:
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
hirak1977Author Commented:
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
moduloCommented:
PAQed with points refunded (500)

modulo
Community Support Moderator
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Fonts Typography

From novice to tech pro — start learning today.

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.