Orange and Davegri:
I believe you are correct, it may be a GUID. What needs to be done, in that case?
Orange and Davegri:
I believe you are correct, it may be a GUID. What needs to be done, in that case?
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.
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?
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
?? 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.
Micron:
[Name] is a Short Text field, [LogicalServerID is Data Type Number.
My current code is:
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 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
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?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
I will try to convert the LogicalServiceID to a string when I do the DLookup.
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
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.
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.
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: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.
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:
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.Code:''' Create a temporary table with an IsSelected field set to False strSQL = "SELECT Service.ServiceID, Service.Description, Service.ServiceCategoryID, False as IsSelected" _
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?
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.
Don't know. I would have to try it and see. I have no way of trying it, but you do. Good luck.could I retrieve the ServerId with a SELECT query casting the ID as a string and then trim off the GUID tag?
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:
if I cast the LogicalServerID as a string it also works.Code:SELECT LogicalServer.Name, LogicalServer.LogicalServerID 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."Code:SELECT LogicalServer.Name, CStr(LogicalServer.LogicalServerID) as LSID FROM LogicalServer WHERE (((LogicalServer.Name)="AAAAJack")) ORDER BY LogicalServer.Name;
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
This part of you procedure is a SELECT query.
You can only use DoCmd.RunSQL with an ACTION query INSERT, UPDATE or DELETE.
Your later SQL involves INSERT queries, which are ACTION queries, and runs as expected.Code:sqlQry = "SELECT CStr(LogicalSerrver.LogicalServerID) AS LSID FROM LogicalServer WHERE LogicalServer.Name = " & SName & ";" 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.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