Passing an existing GUID as a variable value when creating NEW SQL record via Access

Question

I have SQL database with the following tables. The table structure is condensed for this question.

tbl.Vendor

VendorId (guid)  
AccountNumber
Note

tbl.VendorAlert

AlertId (guid)
VendorId (guid)
AlertMsg

tbl.VendorAlert has its uniqued Id (AlertId), but also contains unique id VendorID which links the record to tbl.Vendor.

The existing SQL tables tbl.Vendor and tbl.VendorAlert are linked tables within Access.

Access data entry will NOT be ADDING vendor records, only editing the value of the Note column for existing SQL tbl.Vendor rows. As such, I have been able to create a simple EventProcedure as follows to set the value for tbl.Vendor.Note:

Private Sub btnCommand16_Click()

 Me.[Note] = "ADBPT"

 Me.Refresh
End Sub

The problem I have is when I also need to add a NEW record into the tbl.VendorAlert. I thought of creating a pass-through query as follows:

INSERT INTO VendorAlert (AlertId, VendorId, AlertMsg)
VALUES (NewID(), 'guid ref problem', 'alert message text')

How do I specify the guid value for 'guid ref problem? The code needs to pull the existing VendorId guid from the Vendor table (ie the same "Me" value as referenced in the prior code.

Any help will be greatly appreciated.

Use VBA to modify pass-through query object SQL with static values. But why would you need to use a pass-through? — June7
– June7, Commented Oct 7 at 23:04
This question is similar to: Editing Passthrough SQL Query with Replace in Microsoft Access VBA. If you believe it’s different, please edit the question, make it clear how it’s different and/or how the answers on that question are not helpful for your problem. — June7
– June7, Commented Oct 7 at 23:04

ValNik · Accepted Answer · 2025-10-07 21:26:21Z

How to send a GUID type value to a query.

Simplest method is use MS Access StringFromGUID function.

See example

Private Sub cmAddAlert_Click()
Dim sqlExpr As String
Dim gVendorId As Variant
    gVendorId = Me.VendorId
    
    sqlExpr = "INSERT INTO tblVendorAlert ( VendorId, AlertMsg)" _
        & " VALUES ( '" & Mid(StringFromGUID(gVendorId), 7, 38) & "','alert message text 8')"
    CurrentDb.Execute (sqlExpr)
End Sub

query text is

INSERT INTO tblVendorAlert ( VendorId, AlertMsg) 
VALUES ( '{A471E31E-8412-499D-9870-C5567BD5973C}','alert message text 8')

Function "StringFromGUID(gVendorId)" result is

{guid {A471E31E-8412-499D-9870-C5567BD5973C}}

We take part of this string.

Preferable use command and query with parameter

Private Sub cmAddAlert_Click()
Dim sqlExpr As String
Dim gVendorId As Variant
Dim sGUID As String
Dim cmd As Command
    gVendorId = Me.VendorId
    Set cmd = New Command
    
    sqlExpr = "INSERT INTO tblVendorAlert ( VendorId, AlertMsg)" _
        & " VALUES ( @pVendorId, 'alert message text 4')"
    sGUID = Mid(StringFromGUID(gVendorId), 7, 38)
    cmd.ActiveConnection = CurrentProject.Connection
    cmd.CommandText = sqlExpr
    cmd.Parameters.Append cmd.CreateParameter("pVendorId", adGUID, adParamInput, 32, sGUID)
    cmd.Execute (sqlExpr)
End Sub

As for the "AlertId" column, it is better to make it auto-incremented of the GUID (Replication code) type.

Gustav · Accepted Answer · 2025-10-08 08:38:55Z

First, as mentioned by June, you don't need a pass-through query for that; the ODBC driver simply passes such a simple statement to the server.

Next, use a helper function like we do to build the SQL to make is simple and because you probably will need it again and again.

' Clean a text guid either passed:
'
'   - in the extended text format as converted by Access.StringFromGUID
'   - straight including braces
'   - straight with no braces
'
' and return it as a clean text guid including braces.
' Optionally, remove the opening and closing brace to return the clean base value only.
'
' Example:
'   GuidText = "{guid {E310AED4-DF18-4DE7-B302-752B1D21C589}}"
'   or:
'   GuidText = "{E310AED4-DF18-4DE7-B302-752B1D21C589}"
'   or:
'   GuidText = "E310AED4-DF18-4DE7-B302-752B1D21C589"
'
'   FullGuidText = TextGuid(GuidText)
'   FullGuidText -> "{E310AED4-DF18-4DE7-B302-752B1D21C589}"
'
'   BaseGuidText = TextGuid(GuidText, True)
'   BaseGuidText -> "E310AED4-DF18-4DE7-B302-752B1D21C589"
'
' 2022-07-07. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function TextGuidBase( _
    ByVal GuidText As String, _
    Optional ByVal BaseFormat As Boolean) _
    As String

    Dim Text        As String
    
    Text = Trim(Replace(Replace(Replace(GuidText, GuidHeader, ""), OpenBrace, ""), CloseBrace, ""))
    If Not BaseFormat Then
        ' Wrap in braces.
        Text = OpenBrace & Text & CloseBrace
    End If
    
    TextGuidBase = Text
    
End Function

The SQL could be created and executed like this which is easy to both read, debug, and maintain:

Const SqlMask     As String = _
    "INSERT INTO VendorAlert (VendorId, AlertMsg) " & _
    "VALUES ('{0}', '{1}')"

Dim Sql           As String
Dim AlertMessage  As String

Alert = "alert message text"

Sql = SqlMask
Sql = Replace(Sql, "{0}", TextGuidBase(StringFromGUID(VendorId)))
Sql = Replace(Sql, "{1}", Alert)
CurrentDb.Execute Sql

Of course, as you have the table linked, you could also open it as a DAO.Recordset and use method AddNew to create the record.

Thank you very much. That guides me in the right direction and I have learned something new.

Collectives™ on Stack Overflow

Passing an existing GUID as a variable value when creating NEW SQL record via Access

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related