Referencing GUID in VBA code to SQL db

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I am trying to delete some SQL db data using the following VBA code:
<connection string....>
strSQL = "DELETE * tblEmployeeVProject WHERE fldEmployeeVProject_ID=" &
Me.fldEmployeeVProject_ID_old

cmd.CommandText = strSQL
cmd.CommandType = adCmdText
Set rs = cmd.Execute
--
Notes:
fldEmployeeVProject_ID_old is a GUID
- all I get for strSQL is DELETE * tblEmployeeVProject WHERE
fldEmployeeVProject_ID=????????

How do I get the right reference GUID so that the query works? Do I need to
use a stored procedure and pass parameters instead?

Thanks for your help.

Regards,

Alan
 
Hi Alan,

try this
----
Dim strSql As String
Dim cmd As New ADODB.Command

strSql = "DELETE tblEmployeeVProject WHERE fldEmployeeVProject_ID=?"
cmd.CommandText = strSql
cmd.CommandType = adCmdText
cmd.Parameters.Append _
cmd.CreateParameter("", adBinary, adParamInput, 16,
Me.fldEmployeeVProject_ID_old)
Set cmd.ActiveConnection = CurrentProject.Connection
cmd.Execute
Set cmd=Nothing
 
You can also reference it as a string by enclosed the string representation
of the GUID between single quotes, something like:

strSQL = "DELETE * tblEmployeeVProject WHERE fldEmployeeVProject_ID
= '6F9619FF-8B86-D011-B42D-00C04FC964FF'

On TriGeminal.Com, you will find VBA code to convert a GUID to its string
representation:

http://www.trigeminal.com/lang/1033/codes.asp?ItemID=9#9

http://www.trigeminal.com/code/guids.bas

http://www.trigeminal.com/usenet/usenet011.asp?1033

Another possibility would be to use its hexadecimal representation,
something like
0xff19966f868b11d0b42d00c04fc964ff ; however I never tried this.
 
Thank you Giorgio & Sylvain for your responses.

Giorgio, you hit the nail right on its head - perfect. Code works a treat.
Sylvain, your code works just as well, however, I didn't really want to put
the GUID in the code, just the variable.

Great work. Thanks guys.
 
Back
Top