bhive
asked on
how to assign a GUID from Access2000 to SQL server
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.
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.
ASKER
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("2DBC81CBB5
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
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.
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("2DBC81CBB5
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
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.
ASKER
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.
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.
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)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.