how to assign a GUID from Access2000 to SQL server

bhive
bhive used Ask the Experts™
on
Is  it possible to assign a guid which is in the form of a string variable in Access to a guid field in a linked sql server table ?
I have a link to sql table in access. The sql table has a field of type Guid.
In my Access code -
  set rs = db.OpenRecordset("SELECT * from Table1;", dbOenDynaset)
  rs.AddNew
  rs![GuidField] = "{D345 ..... xxxx-xxxx}"  '<--- Is this allowed ???
  rs.Update  '<--- I get an error here, thats why i suspect the previous line.

The call to Update causes this error -
" Runtime error 3146
  ODBC call failed"

I am not sure if its because the guid assignment problem.
My first guess is that its because of type mismatch.

Pl let me know either way.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
A GUID is a byte array and when working with it you must use conversion functions.  Access has two built in functions to help handle them: GUIDFromString() and StringFromGUID().  Take a look at the on-line help for some examples.

Also, the following will be of interest:

ACC2000: "????????" Is Displayed as the Value of a SQL Server GUID Field
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q278099

HOWTO: Use GUIDs w/ Access, SQL 6.5 and SQL 7
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q197916

  The second article will be the most helpfull as it has lots of sample code and downloadable examples.

Jim.

Author

Commented:
Jdetteman,
I am getting a data type conversion error when I used the function GUID2ByteArray() shown in the second link given by you

    Dim bytGUID() as Byte
    bytGUID = GUID2ByteArray("2DBC81CBB56C47079E60DABEA7A91D4A")

    strSQL = "SELECT * FROM TrialTable1;"
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
    With rs
        .AddNew
        rs!guidField = bytGUID  '<----- Error as described below.
     .Update
    end with

-----------------------------
'Run time error '3421'
 Data type conversion error'
-----------------------------

TrialTable1 is the table Linked from sql8.0 into A2k.
guidField is the uniqueidentifier field in TrialTable1.

In the debug mode, i can see the byte array (bytGUID) getting filled right, but when i assign it to the recordset field, i get the error.

I tried to use the GUIDFromString() function as -
    rs!guidField = Application.GUIDFromString("{2DBC81CB-B56C-4707-9E60-DABEA7A91D4A}")
and I get the same Data Type conversion error.

I tried to use the ADO (2.7) and get a runtime error
 'Multiple-step OLE DB operation generated errors. Check each oledb status value, if available'

I am using Dao 3.6.
I have increased the points to 75 to make-do.

Author

Commented:
hell, long hours.
Jdettman,
 I AM able to assign a string variable to the guid field. The problem was that there were one field that was non-nullable and i was not caring for it. Thats the reason why the odbc call was failing on .Update. Allowing it to be nullable, makes peace with world.
From the links you provided, i realize that i am using Jet 4.0 and hance am immune to the pitfalls of guid in earlier versions of jet. Correct if my deduction is wrong.
I feel much released now.
I appreciate your help and being around. thx. I did learn thr these times. hard way though.

Well, saga doesnot end here. I have a new trouble now and hence a new posinting.

anyway, I wish to delete this question.

Commented:

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
 - PAQ'd and pts refunded
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
Per recommendation, points refunded and question closed.

Netminder
EE Admin

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial