Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2574
  • Last Modified:

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)

Any idea? Why would it generate above error?

I would greatly appreciate your quick response.

Regards
Hirak1977
0
hirak1977
Asked:
hirak1977
  • 11
  • 4
  • 4
  • +4
1 Solution
 
will_scarlet7Commented:
Can you post the SQL of your queries...
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
hirak1977Author Commented:
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
hirak1977Author Commented:
If I import tables from sql server to access as local tables, it works just fine..
0
 
will_scarlet7Commented:
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
 
hirak1977Author Commented:
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
 
hirak1977Author Commented:
Points increased to 400
0
 
will_scarlet7Commented:
I posted a couple pointers, so hopefully we can get some of the SQL guru's attention...
0
 
hirak1977Author Commented:
Appreciate your help.
0
 
SidFishesCommented:
Invalid argument value

you may have a type conversion (different data types in tables) problem or are not handling a null perhaps?
0
 
hirak1977Author Commented:
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
 
JaffaKREECommented:
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
 
hirak1977Author Commented:
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
 
SidFishesCommented:
"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
 
SidFishesCommented:
err...with the appropriate

inner join ON balh = blah
0
 
hirak1977Author Commented:
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
 
will_scarlet7Commented:
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
 
SidFishesCommented:
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
 
SoftEng007Commented:
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
 
hirak1977Author Commented:
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
 
hirak1977Author Commented:
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
 
SoftEng007Commented:
looks like you will have to rewrite the Queries. :(
0
 
JaffaKREECommented:
You can update rows that contain PK autonumbers, just don't try to change the autonumber field.
0
 
hirak1977Author Commented:
JaffaKREE,
But the whole problem is . I Am using update query to insert new record as well.
0
 
moduloCommented:
PAQed with points refunded (400)

modulo
Community Support Moderator
0

Featured Post

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.

  • 11
  • 4
  • 4
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now