Solved

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

Posted on 2004-10-18
26
2,216 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
ID: 12338430
Can you post the SQL of your queries...
0
 
LVL 84
ID: 12338511
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
ID: 12338675
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 1

Author Comment

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

Expert Comment

by:will_scarlet7
ID: 12347340
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
ID: 12347503
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
ID: 12347508
Points increased to 400
0
 
LVL 15

Expert Comment

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

Author Comment

by:hirak1977
ID: 12347775
Appreciate your help.
0
 
LVL 36

Expert Comment

by:SidFishes
ID: 12347989
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
ID: 12348060
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
ID: 12348507
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
 
LVL 1

Author Comment

by:hirak1977
ID: 12349192
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
ID: 12349460
"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
ID: 12349471
err...with the appropriate

inner join ON balh = blah
0
 
LVL 1

Author Comment

by:hirak1977
ID: 12349878
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
ID: 12350626
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
ID: 12350650
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
ID: 12350693
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
ID: 12351454
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
ID: 12351470
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
ID: 12352745
looks like you will have to rewrite the Queries. :(
0
 
LVL 6

Expert Comment

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

Author Comment

by:hirak1977
ID: 12357491
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
ID: 13056380
PAQed with points refunded (400)

modulo
Community Support Moderator
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

856 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