Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 38
  1. #16
    shylock is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2018
    Location
    Dayton, Ohio
    Posts
    100
    Orange and Davegri:



    I believe you are correct, it may be a GUID. What needs to be done, in that case?

  2. #17
    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
    I did a little googling and found this link
    https://oakdome.com/programming/MSAc...sing_GUIDs.php

    It seems that using GUID is requiring customized code to get and operate that field from Sql Server unisng Access vba.

    More info here.

  3. #18
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    What is the code now, or has it not changed since last posted? If [Name] is a number field, you were wrapping it in single quotes as if text. That certainly won't work.
    If removing the quotes and treating it as a number doesn't work, then convert it to text as the GUID article suggested?

    I would try the above or maybe...
    can you not map the [Name] field to a text representation of the GUID in another table or another field in the same table?

  4. #19
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    absolutely sure the field type is a number per post 13?
    I don't think you can have this {01ACAA27-E7FE-E555-72E5-B3D35B92776A} in an Access table as a number, so is this not an Access table?

    these work if it's text

    ?DLOOKUP("VAL","tblGUID","ID = 3")
    {01ACAA27-E7FE-E555-72E5-B3D35B92776A}

    ?DLOOKUP("ID", "tblGUID", "VAL = '{01ACAA27-E7FE-E555-72E5-B3D35B92776A}'")
    3
    Last edited by Micron; 01-16-2019 at 12:19 PM. Reason: accidentally posted before finished

  5. #20
    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
    ?? I think we have to hear from Shylock. I think in Access you have to identify the field as autonumber and format Replication ID
    but I haven't worked with these, nor SQL Server, so have no experience/advice.

  6. #21
    shylock is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2018
    Location
    Dayton, Ohio
    Posts
    100
    Micron:

    [Name] is a Short Text field, [LogicalServerID is Data Type Number.

    My current code is:

    Code:
    Private Sub cmdOK_Click()
        Dim LSID As String
        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 = DLookup("[LogicalServerID]", "LogicalServer", "[Name] = '" & SName & "'")
        SVID = Me.lstAssocServices.Column(0)
        
        sqlQry = "INSERT INTO ServiceLogicalServer ([ServiceID], [LogicalServerID]" _
                 & " VALUES (SVID, LSID);"
        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
    I use the following code, on the same form to retrieve the LogicalServiceID and Description. This code works well. The LogicalServerID is the same Data Type as the LogicalServerID, only in separate tables dervived from SQL Server 2012.

    Code:
    Private Sub Form_Open(Cancel As Integer)
        Dim strSQL, strTbl As String
        Dim dbs As Database
        Dim x As Integer
            
        On Error GoTo ErrorHandler
        x = DCount("[ServiceID]", "TempServiceTbl")
            
        If x > 0 Then
            strTbl = "TempServiceTbl"
        
            '''Delete the temp table if it exists
            DoCmd.DeleteObject acTable, strTbl
        End If
        
        ''' Create a temporary table with an IsSelected field set to False
        strSQL = "SELECT Service.ServiceID, Service.Description, Service.ServiceCategoryID, False as IsSelected" _
        & " INTO TempServiceTbl" _
        & " FROM Service" _
        & " ORDER BY Service.Description;"
        
        CurrentDb.Execute strSQL
        
        ''' Display the services in the Available Services listbox
        Me.lstServices.RowSource = "SELECT TempServiceTbl.ServiceID, TempServiceTbl.Description FROM TempServiceTbl ORDER BY TempServiceTbl.Description ASC;"
            
        Exit Sub
        
    ErrorHandler:
        MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
        Resume Next
        
    End Sub
    Private Sub Label22_Click()
        If Len(Me.lstAssocServices.Column(1) & vbNullString) = 0 Or Me.lstAssocServices.Column(1) = "" Then
            DoCmd.CancelEvent
            Me.lstAssocServices.SetFocus
            myMsg = MsgBox("No Associated Services have been selected.", vbokonlky + vbInformation, "Selection Needed")
        End If
    End Sub
    Private Sub lblAddService_Click()
        Dim SelID, varItm As Variant
        Dim strSQL, strTbl As String
        strTbl = "TempServiceTbl"
        
        For Each varItm In Me.lstServices.ItemsSelected
            SelID = Me.lstServices.Column(0, varItm)
            strSQL = "UPDATE TempServiceTbl" _
                 & " SET [TempServiceTbl].[IsSelected] = True " _
                 & " WHERE [TempServiceTbl].[ServiceID] = " & SelID & ";"
             CurrentDb.Execute strSQL
        Next varItm
        Me.lstServices.Requery
        Me.lstAssocServices.RowSource = "SELECT TempServiceTbl.ServiceID, TempServiceTbl.Description FROM TempServiceTbl WHERE TempServiceTbl.IsSelected = True ORDER BY TempServiceTbl.Description;"
        Me.lstAssocServices.Requery
        
    End Sub
    Private Sub lstServices_DblClick(Cancel As Integer)
        Call lblAddService_Click
    End Sub
    As you can see, I create a temporary table for the Service data and retrieve the Service data from it. This code works perfectly. Given that the ID nubers for both tables are the same, shouldn't I be able to retrieve the LogicalServerID?

    I will try to convert the LogicalServiceID to a string when I do the DLookup.

  7. #22
    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
    Shylock,

    Where does this fit? {01ACAA27-E7FE-E555-72E5-B3D35B92776A} ????

    Also, VBA requires explicit DIM statement.

    Dim strSQL, strTbl As String 'this does not do what you think.
    in this case strSQL will be data type variant.


    You can
    Dim strSQL As String, strTbl As String
    OR
    Dim strSQL As String
    Dim strTbl As String

  8. #23
    shylock is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2018
    Location
    Dayton, Ohio
    Posts
    100
    Orange:

    I have no idea where this object ( {01ACAA27-E7FE-E555-72E5-B3D35B92776A} ) came from. However, it is in the format of either the LogicalServerId or the LogicalServiceID.

    I will have to go back and change the Dim statements to be explicit. Thanks. Old habit die hard. It has been almost 10 years since I have done any coding and then it was C programming.

  9. #24
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Modified by DB in Post#14 to create GUIDs. Indeed you cannot DLookup a field containing a GUID; it returns nonsense characters.
    You probably could handle this sort of coding with an API if you can find the proper activex DLL. I avoid those if possible as the DLLs can become version dependent and cause trouble years down the road.
    This is a dead-end for me.

  10. #25
    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
    Yes, dead end for me also. If Shylock, who said he expected {01ACAA27-E7FE-E555-72E5-B3D35B92776A}, doesn't know where it exists or comes from in resolving his post, and now says This code works perfectly I think we have exhausted any assistance. Unfortunately, we don't have a clear description of the problem and solution.

  11. #26
    shylock is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2018
    Location
    Dayton, Ohio
    Posts
    100
    Quote Originally Posted by orange View Post
    Yes, dead end for me also. If Shylock, who said he expected {01ACAA27-E7FE-E555-72E5-B3D35B92776A}, doesn't know where it exists or comes from in resolving his post, and now says This code works perfectly I think we have exhausted any assistance. Unfortunately, we don't have a clear description of the problem and solution.
    Davegri and orange:

    I misunderstood your question: RE: Where does this fit? ...

    {01ACAA27-E7FE-E555-72E5-B3D35B92776A} is a LgocalServiceID from the TempServiceTbl table. This table is also an SQL Server table. The code that works "perfectly" is from when I assign all of the services (along with the LogicalServiceID's) to another listbox. I do this by the following code:

    Code:
     ''' Create a temporary table with an IsSelected field set to False
        strSQL = "SELECT Service.ServiceID, Service.Description, Service.ServiceCategoryID, False as IsSelected" _
    This lloads all of the services available along with their LogicalServiceID'. This work perfectly. The logiclServiceId and LogicalServerId are the same "Number" format in their respective tables. Both tables come from SQL Server.

    Since it appears that DLookup will not work could I retrieve the ServerId with a SELECT query casting the ID as a string and then trim off the GUID tag? If so, what would the query syntax be?

  12. #27
    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
    Can you post all of the code involved? Your latest post only shows first line of the SQL involved.

    We are interested in how you processed the LogicalServerID if it in fact was a GUID in SQL server.

  13. #28
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    could I retrieve the ServerId with a SELECT query casting the ID as a string and then trim off the GUID tag?
    Don't know. I would have to try it and see. I have no way of trying it, but you do. Good luck.

  14. #29
    shylock is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2018
    Location
    Dayton, Ohio
    Posts
    100
    I don't understand your question: ... how you processed the LogicalServerID if it in fact was a GUID in SQL Server. This is what I am trying to do now. I processed the LogicalServiceID. The entirety of the 2 codes is posted in post #21 of this thread. The first set of code tags shows my attempt to process the ServerID. The second set is how I processed the ServiceId.

    I have created an Access Query shown below that works, although I am using a literal server name:

    Code:
    SELECT LogicalServer.Name, LogicalServer.LogicalServerID
    FROM LogicalServer
    WHERE (((LogicalServer.Name)="AAAAJack"))
    ORDER BY LogicalServer.Name;
    if I cast the LogicalServerID as a string it also works.

    Code:
    SELECT LogicalServer.Name, CStr(LogicalServer.LogicalServerID) as LSID
    FROM LogicalServer
    WHERE (((LogicalServer.Name)="AAAAJack"))
    ORDER BY LogicalServer.Name;
    However, when I try to use it in VBA it sends the error "Run time error 2342: A RunSQL action requires an argument consisting of an SQL statement."

    This is my present code:

    Code:
    Private Sub cmdOK_Click()
        Dim LSID As String
        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
        
        sqlQry = "SELECT CStr(LogicalSerrver.LogicalServerID) AS LSID FROM LogicalServer WHERE LogicalServer.Name = " & SName & ";"
        
        DoCmd.RunSQL sqlQry
        
    '    LSID = DLookup("[LogicalServerID]", "LogicalServer", "[Name] = '" & SName & "'")
    '    LSID = CStr(DLookup("[LogicalServerID]", "LogicalServer", "[Name] = '" & SName & "'"))
        SVID = Me.lstAssocServices.Column(0)
        
        sqlQry = "INSERT INTO ServiceLogicalServer ([ServiceID], [LogicalServerID]" _
                 & " VALUES (SVID, LSID);"
        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

  15. #30
    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
    This part of you procedure is a SELECT query.
    You can only use DoCmd.RunSQL with an ACTION query INSERT, UPDATE or DELETE.
    Code:
    sqlQry = "SELECT CStr(LogicalSerrver.LogicalServerID) AS LSID FROM LogicalServer WHERE LogicalServer.Name = " & SName & ";"
        
        DoCmd.RunSQL sqlQry
    Your later SQL involves INSERT queries, which are ACTION queries, and runs as expected.

    Code:
    sqlQry = "INSERT INTO ServiceLogicalServer ([ServiceID], [LogicalServerID]" _
                 & " VALUES (SVID, LSID);"
        DoCmd.RunSQL sqlQry
        
        SServDes = Me.lstAssocServices.Column(1)
        
        sqlQry = "INSERT INTO ServertoService ([Name], [Description], [ServiceID], [LogicalServerID]" _
                 & " VALUES (SName, SServDes, SVID, LSID);"
                 
        DoCmd.RunSQL sqlQry
    I have not seen an example of MSAccess processing a GUID. I have seen some articles that say you must call a function(s) to work with GUIDs.

Page 2 of 3 FirstFirst 123 LastLast
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