Insert Into Query

[shylock]

Also - is Physical a boolean field? If so "No" won't be valid.
Either 0 -1, True False or No Yes without the quotes.

The table I am using comes from SQL Server and Physical is a Yes/No field, which, I think, maps to 'bit' in SQL Server.

[shylock]

Micron:

, [IPID], [CommissionDate], [LogicalServerTypeID], [DecommissionDate], [Status], [Physical]"
sqlAppend = sqlAppend & " VALUES ('" & SNm & "', '" & vIP & "', #" & CD & "#, {guid{" & vType & "}}, #" & Ddate & "#, '" & Stat & "', '" & PhysS & "');"

I replied to your suggestion on Sat. but don't see it here. So, I will repeat my reply and show my code. I used you suggestion, verbatim and debug.int showed:

INSERT INTO LogicalServer ([Name], [IPID], [CommissionDate], [LogicalServerTypeID], [DecommissionDate], [Status], [Physical] VALUES ('AAAA-Test', {guid{{9C0012B5-E7FE-E555-79AF-6F8274A8909C}}}, #3/25/2019#, {guid{{A331FB98-E7FE-E555-74FA-AC3CD4221013}}}, #12:00:00 AM#, 'Testing', No);

Note the extra {} delimiters. I received the same error "Syntax error in INSERT INTO statement"

Ithen deleted the { and} after guid and the end of the vIp and vType as follows:

    sqlAppend = sqlAppend & " VALUES ('" & SNm & "', {guid" & vIP & "}, #" & CD & "#, {guid" & vType & "}, #" & Ddate & "#, '" & Stat & "', " & PhysS & ");"

Debug.print showed:

INSERT INTO LogicalServer ([Name], [IPID], [CommissionDate], [LogicalServerTypeID], [DecommissionDate], [Status], [Physical] VALUES ('AAAA-Test', {guid{9C0012B5-E7FE-E555-79AF-6F8274A8909C}}, #3/25/2019#, {guid{A331FB98-E7FE-E555-74FA-AC3CD4221013}}, #12:00:00 AM#, 'Testing', No);

Received the same syntax error.

Minty, on Monday mornign 3/25/19 asked about the data type for the 'Physical' field. The table is from SQL Server and has a data type of 'Yes/NO'. Don't know if this makes and difference but I tried 0 and -1 one and got the same syntax error.

[Minty]

I know for a fact that -1 works on SQL Bit fields.
That leaves the GUID fields as probably being the guilty parties.

If they are GUID's in the table I'm not sure you can insert new ones? Are they not determined by the table at insert ?

[Minty]

After a bit of Googling you would appear to need to use the GUIDFromString () function.
https://docs.microsoft.com/en-us/pre...3doffice.11%29

This will depend on the string being converted from a GUId to a string in the first place.

[Micron]

I came up with the suggestion by creating a query on a guid field, ran it (it worked) and switching to sql view. Access wrapped the criteria in {guid...}. Perhaps it doesn't get processed correctly if processed on the vb side, which is where I think you're running this.

A typical trouble shooting method is to scale back until it works and add until it fails, which usually gives some indication of what the problem is related to. In your case, I'd copy the sql output to a new query in sql view then switch to * datasheet view.
- If it's ok but won't run in code, then other methods would be needed - perhaps the function mentioned or building a query def and running that.
- If it's not ok, query sql should get highlighted with the offending part in bold text. If not entirely clear,
- remove guid parts and see if it works (this is the scaling back part)
This is a go-to method for trouble shooting sql that you should be using. If you paste the entire sql (including {guid...}} ) Access might add {guid } around your pasted sql with the already present {guid }} when using a query. In that case another approach will be required as mentioned in point 1.

*datasheet view will process the query without actually running it, which is good for testing action queries. However, don't be fooled by the appearance of blank records when testing update queries as only the rows being affected can be shown; the values are not shown.

I also would not use Yes or No on a yes/no field, but rather 0 or -1.

[shylock]

Micron:

I took your trouble shooting suggestion and created a query and scaled back until I finally got a working query, I had to input real data and when I clicked on Append this is what showed up in SQL view:

INSERT INTO LogicalServer ( LogicalServerID, Name, IPID, CommissionDate, LogicalServerTypeID, DecommissionDate, Status, Physical )
SELECT '' AS Expr1, 'AAA-JackTest' AS Expr2, {guid {34BC6640-FDA1-401A-B9BD-D6CB2A4AE536}} AS Expr3, #3/26/2019# AS Expr4, {guid {A331FB98-E7FE-E555-74FA-AC3CD4221013}} AS Expr5, #12/30/1899# AS Expr6, 'Testing' AS Expr7, 0 AS Expr8;

I am not sure of what delimiters to use or where to put them. I've been playing around with them to no avail. Here is what I have:

 sqlAppend = "INSERT INTO LogicalServer ( LogicalServerID, Name, IPID, CommissionDate, LogicalServerTypeID, DecommissionDate, Status, Physical )" _
              & " SELECT {guid(" & LSID &"}} AS Expr1, & '" & SNm & "' AS Expr2, {guid{" & vIP & "}} AS Expr3, # & CD & # AS Expr4, {guid{" & vType & "}} AS Expr5, " _
              & #" & Ddate& " # AS Expr6, '" & Stat & "' AS Expr7, & PSvr & AS Expr8 & ";"

Unfortunately, no matter what I do the script stays red in my VBA code. Am I on the right track? Also, why does the query design use SELECT instead of VALUES?

[Micron]

Some things don't look right. Red is for what I think shouldn't be there, green for what's missing - except for spaces I added (can't color those). Hard to see green (it's after Expr 3, # ). Omitting the line continuation characters (I don't use them) to make it easier to read, note what doesn't look right to me:

"INSERT INTO LogicalServer ( LogicalServerID, Name, IPID, CommissionDate, LogicalServerTypeID, DecommissionDate, Status, Physical )"
  & " SELECT {guid(" & LSID &"}} AS Expr1, & '" & SNm & "' AS Expr2, {guid{" & vIP & "}} AS Expr3, #" & CD & # AS Expr4, {guid{" 
& vType & "}} AS Expr5, #" & Ddate & " # AS Expr6, '" & Stat & "' AS Expr7, & PSvr & AS Expr8 & ";"

I wrote this in a sub and the output is below it. I had to presume that Stat is text and Psvr is a number. If not, it's still not correct. I also had to dim guid's as text because I don't have your data, so I have to presume that won't have any effect on how the braces {} are added by Access.

sqlAppend = "INSERT INTO LogicalServer (LogicalServerID, Name, IPID, CommissionDate, LogicalServerTypeID, DecommissionDate, Status, Physical) "
sqlAppend = sqlAppend & "SELECT {guid" & LSID & "} AS Expr1, '" & SNm & "' AS Expr2, {guid" & vIP & "} AS Expr3, "
sqlAppend = sqlAppend & "#" & CD & "# AS Expr4, {guid" & vType & "} AS Expr5, "
sqlAppend = sqlAppend & "#" & Ddate & "# AS Expr6, '" & Stat & "' AS Expr7, " & PSvr & " AS Expr8;"

INSERT INTO LogicalServer (LogicalServerID, Name, IPID, CommissionDate, LogicalServerTypeID, DecommissionDate, Status, Physical)
SELECT {guid{123456789}} AS Expr1, 'abcdefg' AS Expr2, {guid{987654321}} AS Expr3, #3/01/19# AS Expr4, {guid{001122334455}} AS Expr5,
#5/02/19# AS Expr6, 'cat' AS Expr7, 88 AS Expr8;

If you're still stuck after this, I think it's time to post a db for analysis.

[shylock]

Micron:

I tried your suggestion and received a different error: "Run-time error: 3075 Malformed GUID in query expression '{guid}'." This is because I won't know the LogicalServerID (LSID) until the new server is saved. I believe the GUID is assigned automatically upon saving in SQL Server. I am inheriting these tables from SQL Server and am not permitted to change them. How do I upload the table and how much data do you need? I showed the table definition in my original post #1.

My code:

Private Sub cmdSaveRec_Click()
    Dim sqlAppend As String, SNm As String, Stat As String, PhysS As String
    Dim vIP As Variant, vType As Variant, varItm As Variant, LSID As Variant
    Dim CD As Date, Ddate As Date
    Dim PSvr As Integer
    
    ''' Set up all of the values to insert into the LogicalServer table
    LSID = Me.lstLSID.Column(0, 0)
    SNm = Me.txtServerNm
    For Each varItm In lstIP.ItemsSelected
        vIP = Me.lstIP.Column(0, varItm)
    Next varItm
    CD = CDate(Me.txtComDate)
    For Each varItm In lstServerType.ItemsSelected
        vType = Me.lstServerType.Column(0, varItm)
    Next varItm
    If IsNull(Me.txtDecommDate) Then
        Ddate = Empty
    Else
        Ddate = CDate(Me.txtDecommDate)
    End If
    Stat = Me.txtStatus
    For Each varItm In lstPhysSvr.ItemsSelected
        If Me.lstPhysSvr.Column(0, varItm) = "Yes" Then
            PSvr = -1
        Else
            PSvr = 0
        End If
    Next varItm
    ''' Append a new record to the table
'    sqlAppend = "INSERT INTO LogicalServer ([LogicalServerID],[Name], [IPID], [CommissionDate], [LogicalServerTypeID], [DecommissionDate], [Status], [Physical]" & _
'                 " SELECT ('" & LSID & "', '" & SNm & "', '" & vIP & "', #" & CD & "#, '" & vType & "', #" & Ddate & "#, '" & Stat & "', " & PSvr & ");"
    
    sqlAppend = "INSERT INTO LogicalServer ( [LogicalServerID], [Name], [IPID], [CommissionDate], [LogicalServerTypeID], [DecommissionDate], [Status], [Physical] )"
    sqlAppend = sqlAppend & "SELECT {guid " & LSID & "} AS Expr1, '" & SNm & "' AS Expr2, {guid " & vIP & "} AS Expr3, "
    sqlAppend = sqlAppend & "#" & CD & "# AS Expr4, {guid " & vType & "} AS Expr5, "
    sqlAppend = sqlAppend & "#" & Ddate & "# AS Expr6, '" & Stat & "' AS Expr7, " & PSvr & " AS Expr8;"
'
Debug.Print sqlAppend
    DoCmd.RunSQL sqlAppend
End Sub

The DeBug.Print shows:

INSERT INTO LogicalServer ( [LogicalServerID], [Name], [IPID], [CommissionDate], [LogicalServerTypeID], [DecommissionDate], [Status], [Physical] )SELECT {guid } AS Expr1, 'A1A-Test' AS Expr2, {guid {B6A411F9-5056-9F6E-4C02-2BD26D3B6455}} AS Expr3, #3/29/2019# AS Expr4, {guid {A331FB98-E7FE-E555-74FA-AC3CD4221013}} AS Expr5, #12:00:00 AM# AS Expr6, 'Active' AS Expr7, 0 AS Expr8;

I don't think dimming the GUID's as Variants should make any difference, do you?

I think that we are very close to solving this, if it can be solved at all. The only doubt I have is I don't know if Access can automatically assign the LogicalServerId GUID.

[Micron]

 sqlAppend = "INSERT INTO LogicalServer([Name], [IPID], [CommissionDate], [LogicalSerTypeID], [DecommissionDate], [Sttus], [Physical]" _
 & " VALUES (" & SNm & ", '" & vIP & ", '" & CD & ", '" & vType & ", '" & Ddate & ", '" & Stat & ", '" & PhysS & "');"

If you don't know the LSID (which is for the first field?) until the record is saved, then how did your sql migrate from
sqlAppend = "INSERT INTO LogicalServer([Name], [IPID],....

to what you have in post 21??
INSERT INTO LogicalServer ( LogicalServerID, Name,

GUID as a variant wouldn't hurt, but it seems like you've introduced a different issue by trying to set the value of a field that you shouldn't be.
I don't know if Access can generate what you want. Replication ID's are still a field size option for number type and those are GUID's. That must mean that one can use replication id (GUID) fields without having to use replication in the db itself. I say this because I recently discovered that Access 2016 won't open a db that uses full blown replication. However, I don't have a way to experiment.

Even if you could just upload the table (you can't AFAIK) it wouldn't help as I have no where to put it. Nor do I want to build what you already have when you could upload the db itself. You should compact/repair it first, then zip a copy. I believe the max upload file size is 2Mb.
I think you need to figure out why you're now trying to insert the 1st field when you weren't before.