Solved

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

Posted on 2004-10-18
26
2,101 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)

Any idea? Why would it generate above error?

I would greatly appreciate your quick response.

Regards
Hirak1977
0
Comment
Question by:hirak1977
  • 11
  • 4
  • 4
  • +4
26 Comments
 
LVL 15

Expert Comment

by:will_scarlet7
Comment Utility
Can you post the SQL of your queries...
0
 
LVL 84
Comment Utility
Did your Constraints (i.e. Relationships) upsize as well? You must also have Primary Keys defined on ALL tables in SQL Server ... this doesn't always happens when you use the upsizing wizard (or if you use DTS, for that matter).
0
 
LVL 1

Author Comment

by:hirak1977
Comment Utility
I used DTS and I manually created Primary keys and auto number for my tables in SQL 2000.
Pasted below is one the query in Access.


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]));

The above query involved three tables. The following is somwhat simple query - copying record from one table to another one.

UPDATE AssetDetail LEFT JOIN FinalAssetDetail ON AssetDetail.ScanID = FinalAssetDetail.ScanID SET FinalAssetDetail.ComputerID = [assetdetail]![computerid], FinalAssetDetail.Brand = [assetdetail]![brand], FinalAssetDetail.Type = [assetdetail]![type], FinalAssetDetail.Model = [assetdetail]![Model], FinalAssetDetail.ScanID = [assetdetail]![scanID], FinalAssetDetail.CPUSpeed = [assetdetail]![cpuspeed], FinalAssetDetail.CurrentOS = [assetdetail]![currentos], FinalAssetDetail.CurrentOSNum = [assetdetail]![currentosnum], FinalAssetDetail.ExpansionBusType = [assetdetail]![expansionbustype], FinalAssetDetail.Memory = [assetdetail]![memory], FinalAssetDetail.SerialPorts = [assetdetail]![serialports], FinalAssetDetail.ParallelPorts = [assetdetail]![parallelports], FinalAssetDetail.ScreenSize = [assetdetail]![screensize], FinalAssetDetail.VideoAdaptor = [assetdetail]![videoadaptor], FinalAssetDetail.VideoMemory = [assetdetail]![videomemory], FinalAssetDetail.FixedDiskPrimary = [assetdetail]![fixeddiskprimary], FinalAssetDetail.FixedDiskSecondary = [assetdetail]![fixedDiskSecondary], FinalAssetDetail.SoundCard = [assetdetail]![SoundCard], FinalAssetDetail.CASFaxModemCard = [assetdetail]![CASFaxModemCard], FinalAssetDetail.CDROM = [assetdetail]![CDROM], FinalAssetDetail.SCSI = [assetdetail]![SCSI], FinalAssetDetail.NetworkCard = [assetdetail]![NetworkCard], FinalAssetDetail.USB = [assetdetail]![USB], FinalAssetDetail.L2CacheType = [assetdetail]![L2CacheType], FinalAssetDetail.Battery = [assetdetail]![battery], FinalAssetDetail.PCMCIACards = [assetdetail]![PCMCIACards], FinalAssetDetail.UDF1 = [assetdetail]![UDF1], FinalAssetDetail.UDF2 = [assetdetail]![UDF2], FinalAssetDetail.UDF3 = [assetdetail]![UDF3], FinalAssetDetail.UDF4 = [assetdetail]![UDF4], FinalAssetDetail.UDF5 = [assetdetail]![UDF5], FinalAssetDetail.Comments = [assetdetail]![comments], FinalAssetDetail.ScreenTypeID = [assetdetail]![ScreenTypeID]
WHERE (((AssetDetail.ScanID)=[forms]![assetform].[scanid]));

Thanks!
Hirak1977
0
 
LVL 1

Author Comment

by:hirak1977
Comment Utility
If I import tables from sql server to access as local tables, it works just fine..
0
 
LVL 15

Expert Comment

by:will_scarlet7
Comment Utility
I'm still fairly new to SQL server databases compared to others in the forum, but from what I've experienced SQL Server tables are a lot more picky about little things when it comes to queries. In your queries you have a mix of syntax formats, and while I don't know whether it makes a difference for sure, my first suggestion would be to try to standardise your query to use the official standard syntaz of "TableName!FieldName" or "[TableName]![FieldName]" instead of the "TableName.FieldName" (Note the "!" instead if the ".") and see if that makes a difference.

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
 
LVL 1

Author Comment

by:hirak1977
Comment Utility
I did exactly what you asked me to do. But I am still having same problem. I am a dead man in middle of water. :(.
Ne other  ideas? I am running out of options.
0
 
LVL 1

Author Comment

by:hirak1977
Comment Utility
Points increased to 400
0
 
LVL 15

Expert Comment

by:will_scarlet7
Comment Utility
I posted a couple pointers, so hopefully we can get some of the SQL guru's attention...
0
 
LVL 1

Author Comment

by:hirak1977
Comment Utility
Appreciate your help.
0
 
LVL 36

Expert Comment

by:SidFishes
Comment Utility
Invalid argument value

you may have a type conversion (different data types in tables) problem or are not handling a null perhaps?
0
 
LVL 1

Author Comment

by:hirak1977
Comment Utility
Well, I thought about datatypes earlier. I changed datatypes of the table , made it very simplistic like numberic and text and also dropped all the existing records (to avoid null values). Its still causing the same problem. Now, the interestign thing is if I am modify the update query to update existing record, it will work just fine. It causes problem only when I try to use update query to insert new record( one - to many).
0
 
LVL 6

Expert Comment

by:JaffaKREE
Comment Utility
Text in SQL is actually a very large datatype.  Try Varchar instead, although I'm not sure if this will actually help your problem.

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Author Comment

by:hirak1977
Comment Utility
No it didnt.

One more strange observation: I have to include all the fields in the update query to make it work. One of them is Prime key (auto number), which I cant update. So neway, I am screwed.
0
 
LVL 36

Expert Comment

by:SidFishes
Comment Utility
"It causes problem only when I try to use update query to insert new record( one - to many)."

try this

INSERT Into MasterTable (SELECTsomething from SomeTable INNER JOIN on SomeOtherTable WHERE var = somecriteria)

0
 
LVL 36

Expert Comment

by:SidFishes
Comment Utility
err...with the appropriate

inner join ON balh = blah
0
 
LVL 1

Author Comment

by:hirak1977
Comment Utility
I guess, I cant use Insert query. I am kinda stuck with update query because of the fact that I am using Update query to update existing record as well as to add new records.
0
 
LVL 15

Expert Comment

by:will_scarlet7
Comment Utility
hirak1977,
You said:
>> I guess, I cant use Insert query.

Why is that? Does it return an error? In order to get an insert query you might need to run cascading queries one table at a time.

Sam
0
 
LVL 36

Expert Comment

by:SidFishes
Comment Utility
well...stuck in that you can't do it in one shot but there's no reason you can't create code to do an insert then the update...and if you can create stored procedures ...it's even easier

Create Procedure dbo.stpInsertUpdate
@val1 INT,
@val2 Int,
@val3 INT

as
insert into tblOne val1 = @val1
update tbltwo set val2 = @val2 where val1 = @val1
go

you could also use a trigger to do this...

0
 
LVL 9

Expert Comment

by:SoftEng007
Comment Utility
check the linked tables in the access db to see if the primary keys are defined in access.
you won't be able to do updates on one to many tables with out the primary keys being defined in access.
Access uses an internal row identifier for updates that is not available with out a primary key on linked tables.
0
 
LVL 1

Author Comment

by:hirak1977
Comment Utility
SidFishes,

Here is the deal why I dont want to write Stored procedure or Insert query. Most of my update queries are consist of more than one table but updatin just one table at a time. I check to see if record already exist, if yes (update query with one - to many relationship) will update that particular record if not , then it will insert the new record.

I can write store procedure but I afraid, there are many update queries and I will end up wasting lot of my time.

0
 
LVL 1

Author Comment

by:hirak1977
Comment Utility
SoftEng007,
Primary keys are well defined in access as well. I can update existing record but cant insert new record with update query, unless I update all the fields of the tables which i cant do bcoz of autonumber primary key.
0
 
LVL 9

Expert Comment

by:SoftEng007
Comment Utility
looks like you will have to rewrite the Queries. :(
0
 
LVL 6

Expert Comment

by:JaffaKREE
Comment Utility
You can update rows that contain PK autonumbers, just don't try to change the autonumber field.
0
 
LVL 1

Author Comment

by:hirak1977
Comment Utility
JaffaKREE,
But the whole problem is . I Am using update query to insert new record as well.
0
 

Accepted Solution

by:
modulo earned 0 total points
Comment Utility
PAQed with points refunded (400)

modulo
Community Support Moderator
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

763 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now