Referencing GUID in VBA code to SQL db steel 31.01.2006 10:40:33 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

Re: Referencing GUID in VBA code to SQL db "giorgio rancati" <giorgio_No_Spalmer_rancati[ at ]tiscali.it> 31.01.2006 12:21:09 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 ---- seek also the VBA function *StringFromGUID* bye -- Giorgio Rancati [Office Access MVP] "steel" <steel[ at ]community.nospam> ha scritto nel messaggio news:2C5A4194-1CF3-49A9-B5CD-50C7869399DC[ at ]microsoft.com... [Quoted Text] > 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

Re: Referencing GUID in VBA code to SQL db "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> 31.01.2006 20:04:06 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. -- Sylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: http://cerbermail.com/?QugbLEWINF "steel" <steel[ at ]community.nospam> wrote in message news:2C5A4194-1CF3-49A9-B5CD-50C7869399DC[ at ]microsoft.com... [Quoted Text] > 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

Re: Referencing GUID in VBA code to SQL db steel 01.02.2006 06:32:29 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. -- Regards, Alan "Sylvain Lafontaine" wrote: [Quoted Text] > 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. > > -- > Sylvain Lafontaine, ing. > MVP - Technologies Virtual-PC > E-mail: http://cerbermail.com/?QugbLEWINF > > > "steel" <steel[ at ]community.nospam> wrote in message > news:2C5A4194-1CF3-49A9-B5CD-50C7869399DC[ at ]microsoft.com... > > 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 > > >