Page 3 of 3 FirstFirst 123
Results 31 to 38 of 38
  1. #31
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664

    This might be of interest Replication and GUIDs, the Good, the Bad, and the Ugly

  2. #32
    shylock is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2018
    Location
    Dayton, Ohio
    Posts
    100
    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:

    Code:
     LSID = Forms!frmLogicalServers!Text46
        SVID = Me.lstAssocServices.Column(0)
        
        sqlQry = "INSERT INTO ServiceLogicalServer (ServiceID, LogicalServerID)" _
                 & " VALUES (SVID, LSID);"
        DoCmd.RunSQL sqlQr
    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?

    Any suggestions?

  3. #33
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    if that is an exact paste, your docmd.runsql sqlqr isn't the same as your variable sqlqry

  4. #34
    shylock is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2018
    Location
    Dayton, Ohio
    Posts
    100
    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.

  5. #35
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    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.

  6. #36
    shylock is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2018
    Location
    Dayton, Ohio
    Posts
    100
    This is the code for Click event for the Command Button:

    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
    The result of the DeBug.Print is:
    {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.

  7. #37
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Try this line to replace your current line

    Code:
     sqlQry = "INSERT INTO ServiceLogicalServer ([ServiceID], [LogicalServerID])" _
                 & " VALUES ('" & SVID] & "', '" & LSID  & "');"
    You need to get the values rendered by Access to use with the INSERT.

    If SVID and LSID are NOT strings, then remove the single quotes (')

  8. #38
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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.

Page 3 of 3 FirstFirst 123
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 06-02-2017, 10:40 AM
  2. Runtime Error 2501 on docmd.runSQL
    By schwachmama in forum Access
    Replies: 5
    Last Post: 04-17-2015, 11:28 AM
  3. Compile Error: Syntax Error in DoCmd.RunSQL Statement
    By Evilferret in forum Programming
    Replies: 1
    Last Post: 08-27-2012, 12:32 PM
  4. Run-time error 2342
    By tariq1 in forum Programming
    Replies: 1
    Last Post: 07-09-2012, 12:01 PM
  5. Syntax Error with DoCmd.RunSQL
    By dandoescode in forum Programming
    Replies: 2
    Last Post: 06-25-2012, 11:06 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums