UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> How To Update A Linked SQL Table Binary Field With A 0?, Any Versions    
 
   
jkribas
post Jul 3 2019, 06:16 AM
Post#1



Posts: 16
Joined: 11-December 07
From: Oporto, Portugal


Hi all,

I need to update a new field that was created in a corparate SQL database table.
The table is linked, and the filed datatype shows binary. I need to store a 0 (zero) in binary in that field.
In SQL, the guys are updating the field with cast(0 as binary(16))

How can I do it with access? Im using access2007.

YINTERFACE.AddNew

YINTERFACE!YSYSORI_0 = "MRR"
YINTERFACE!AUUID_0 = ???? ====> this is the field to store a 0 in binary

YINTERFACE.Update

Hope you can help,
Jose


Go to the top of the page
 
cheekybuddha
post Jul 3 2019, 06:42 AM
Post#2


UtterAccess VIP
Posts: 11,459
Joined: 6-December 03
From: Telegraph Hill


Hi,

Use a pass-thru query. Search UA for more info.

Then you can use T-SQL directly.

The SQL will look something like:
CODE
INSERT INTO YourTable (
  YSYSORI_0,
  AUUID_0
) VALUES (
  'MRR',
  CAST(0 AS BINARY(16))
);


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
nvogel
post Jul 3 2019, 07:09 AM
Post#3



Posts: 976
Joined: 26-January 14
From: London, UK


Try this:


CODE
YINTERFACE.AddNew
YINTERFACE!AUUID_0 = Chr(0)
YINTERFACE.Update


One factor here is whether this is actually a BINARY or VARBINARY column (Access shows both as BINARY but SQL Server treats them differently). The expression cast(0 as binary(16)) does not store "zero" in the field - it returns a sequence of sixteen 00 bytes. If the column is a BINARY(16) then I believe the above code will achieve the same result.

Hope this helps.
Go to the top of the page
 
jkribas
post Jul 3 2019, 05:25 PM
Post#4



Posts: 16
Joined: 11-December 07
From: Oporto, Portugal


Hi

I read somewhere in the forum that pass-through queries only generate snapshot recordsets, and do not allow updates on tables.
Tomorrow I will test.
Thanks

Jose
Go to the top of the page
 
cheekybuddha
post Jul 4 2019, 01:21 AM
Post#5


UtterAccess VIP
Posts: 11,459
Joined: 6-December 03
From: Telegraph Hill


That is true, but your code is simply adding a record. You don't need a recordset.

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    18th August 2019 - 01:53 AM