This might be of interest Replication and GUIDs, the Good, the Bad, and the Ugly
This might be of interest Replication and GUIDs, the Good, the Bad, and the Ugly
OK Guys, I finally figured out a work around for the problem of getting the LogicalSeverID and LogicalSeviceID GUID's out of the tables and into my forms. In my frmLogicalServers form I created a textbox and in the GotFocus event I placed the following code:
Code:Me.Text46 = DLookup("cstr([LogicalServerID])", "LogicalServer", Forms!frmLogicalServers!txtServerNm)
In the frmAddServices form I placed the following code in the Form_Open event :
Code:Me.lstServices.RowSource = "SELECT TempServiceTbl.ServiceID, TempServiceTbl.Description FROM TempServiceTbl ORDER BY TempServiceTbl.Description ASC;"
In the cmdOK button click event of frmAddServices I have the following:
This gives me the data I need to INSERT INTO the ServicetoServer table. Now the problem is that I get Error 3129: Invalid SQL statement; expected 'DELETE', 'INSERT',... I suspect it occurs because both LSID and SVID were cast as strings when retrieved from their respective tables. How do I convert them back to GUID's to use for the INSERT INTO the ServicetoServer table?Code:LSID = Forms!frmLogicalServers!Text46 SVID = Me.lstAssocServices.Column(0) sqlQry = "INSERT INTO ServiceLogicalServer (ServiceID, LogicalServerID)" _ & " VALUES (SVID, LSID);" DoCmd.RunSQL sqlQr
Any suggestions?
if that is an exact paste, your docmd.runsql sqlqr isn't the same as your variable sqlqry
My Bad!! Can't see the forest for the trees. Changed to sqlQry. Now get a parameter Error. "Enter Parameter Value SVID" I can see from the immediate window that SVID contains the ServiceID and LSID the ServerID.
the code if :
Code:sqlQry = "INSERT INTO ServiceLogicalServer (ServiceID, LogicalServerID)" _ & " VALUES (SVID, LSID);" DoCmd.RunSQL sqlQry
The immediate window shows:
? SVID
{23216D20-5056-9F6E-4CBB-7CEF4664CEFB}
? LSID
{2CEB15F7-1C67-4E65-B2DC-004522BA0FEF}
? sqlQry
INSERT INTO ServiceLogicalServer (ServiceID, LogicalServerID) VALUES (SVID, LSID);
I have tried it with [] around the SVID and LSID also with " " and ' '. What is the proper way?
If I load the raw data (the actual ID's) It gives me Error #440. If I click "Help" it takes me to a page for SQL Studio which is no longer being updated.
You're showing the immediate window results, but the code in your posts 32 and 34 doesn't show Debug.print???
Can you show us as much code as you can, including the debug.prints and the output?
As I mentioned earlier, I have not seen and haven't been able to find a clear working example dealing with GUIDs.
This is the code for Click event for the Command Button:
The result of the DeBug.Print is:Code:Private Sub cmdOK_Click() Dim db As DAO.Database Dim rs As DAO.Recordset Dim SVID As Variant Dim sqlQry As String, SName As String, SServDes As String ''' Get the LogicalServerID for the Server named on the "Details" tab SName = Forms!frmLogicalServers.txtServerNm LSID = Forms!frmLogicalServers!Text46 Debug.Print LSID SVID = Me.lstAssocServices.Column(0) Debug.Print SVID sqlQry = "INSERT INTO ServiceLogicalServer ([ServiceID], [LogicalServerID])" _ & " VALUES ([SVID], [LSID]);" Debug.Print sqlQry DoCmd.RunSQL sqlQry SServDes = Me.lstAssocServices.Column(1) ' sqlQry = "INSERT INTO ServertoService (Name, Description, ServiceID, LogicalServerID)" _ ' & " VALUES (SName, SServDes, SVID, LSID);" ' DoCmd.RunSQL sqlQry End Sub
{2CEB15F7-1C67-4E65-B2DC-004522BA0FEF}
{23216D20-5056-9F6E-4CBB-7CEF4664CEFB}
INSERT INTO ServiceLogicalServer ([ServiceID], [LogicalServerID]) VALUES ([SVID], [LSID]);
The DoCmd.RunSQL sqlQry results in a window asking for input of SVID.
I know that working with GUIDs is not compatible with Acces but I can only work with what I'm given.
Any suggestions will be gratefully accepted. Thanks.
Try this line to replace your current line
You need to get the values rendered by Access to use with the INSERT.Code:sqlQry = "INSERT INTO ServiceLogicalServer ([ServiceID], [LogicalServerID])" _ & " VALUES ('" & SVID] & "', '" & LSID & "');"
If SVID and LSID are NOT strings, then remove the single quotes (')
What with being involved in other forums and other posts here, I find myself wondering about the problem/issue here, especially when it gets to be 3 or more pages long.
I've used GUID's for locating/verifying Access references (libraries) in front ends and don't recall having any issues other than what happens to them with Windows updates being applied - mostly I think related to OS versions rather than simple updates. I set the field to be text. Not sure if that helps, or if not, why there seems to be an approach to storing them as something else. Maybe that doesn't help in this situation, but I thought I'd throw it out there.