Replication ID in Access

[Eclipse DevSoft]

In MS SQL, you treat GUID as string so the SQL statement below will work:

"SELECT * FROM MyTable WHERE rowguid = '{8367BB02-D6EA-20E4-0194-1091B8F387CA}'"

If you are using an Access Database, the statement above will not work (won't return any record). How would you translate the above statement to work in an Access? In short, how would you treat GUID (Replication ID) in access?

------------------
Share your knowledge, it is the best way to achieve immortality

[Clunietp]

I put that number in a MS Access text field and could select it fine. MS Access does not have a GUID field similar to SQL server. Could you put this in a Access text field instead? Or if it is being transferred to an Access DB from SQL, what field type is it going in to?

[Eclipse DevSoft]

If it would be in an Access text field, then there would be no problem but it should maintain the same type (16bit number). And GUID in Access is just called 'Replication ID' (Look in Number or AutoNumber type). I am writing an app that can access multiple database (SQL, Access...etc), so across databases, each column must be of the same type. The only way I came up with is by using a recordset, which is a very expensive alternative compared to direct SQL statements (I'm also using GUID for DELETES, UPDATES and INSERTS).

------------------
Share your knowledge, it is the best way to achieve immortality

[Clunietp]

Thank you for correcting me on that Access GUID field, that is interesting to know. I also found out that you must use single quotes to query on a SQL UniqueIdentifier Field, and no single quotes to query on MS Access autonumber/GUID.

This is the code I used to find out....MS Access 2000 MDB and SQL Server 7

Dim rs As Recordset
Dim ret As String
Dim cn As Connection

'new connection to Access DB
Set cn = New Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Biblio2k.mdb"

'get rs, initial value with GUID
Set rs = cn.Execute("Select * from table1")
ret = rs.Fields(0).Value

'show GUID
MsgBox ret

'query on GUID
Set rs = cn.Execute("Select * from table1 where PriKey = " & ret)

'returns a value
MsgBox rs.Fields(0).Value
cn.Close


'connect to SQL Server 7
cn.Open "Provider=SQLOLEDB.1;Data Source=NTSRV;Initial Catalog=TEST;Integrated Security=SSPI"

Set rs = cn.Execute("Select * from Names")

ret = rs.Fields(0).Value

'show GUID value
MsgBox ret

'query on GUID
Set rs = cn.Execute("Select * from names where PriKey = '" & ret & "'")

'returns a value, but must use single quotes for SQL server
MsgBox rs.Fields(0).Value


Looks like you'll have to determine the DBMS and format your SQL accordingly, or store your GUID field in MS Access as text so you can use the same SQL statements....

HTH

Tom

[Eclipse DevSoft]

Thanks Clunietp. I didn't bother to check the code in VB till now. Both query, with or without the single quotations, will work in VB if you are using the JET OleDB 4.0 provider but only the query wrap with the single quotation will work if using the ODBC provider (MSDASQL) for Jet databases. What I was doing is building the SQL statement within MS Access SQL designer. The reason being is that I was be able to test instantly if the query will work or not. This has worked for me previously until I came across the GUID type, and I swear to God, I can't make it work within MS Access SQL designer (with or without single quotation, even tried double????).