How to reference open SQL database

[andydinger]

I think I have a problem that must have a simple solution but I can't find any online examples to help me.
Scenario:
I am using Access as the front end to an Azure SQL database. The tables are therefore an External Data Source connected via ODBC. I have a form which has an On Dbl Click Event Procedure in VBA:

Private Sub Number_DblClick(Cancel As Integer)
    
    If IsNull(Number) Then Exit Sub  ' If user double clicks an empty record then quit
    If Not IsNull(DLookup("[Part number]", "dbo_Suppliers", "[Part number]='" & Number & "'")) Then
        ' If the Part number double clicked on exists in the Suppliers table then open the Supplier Entry form to edit it
        DoCmd.OpenForm "Supplier Entry", , , "[Number]='" & Number & "'", acFormEdit, acDialog
    Else
        ' If the Part number double clicked on does not exist in the Suppliers table then ask user whether to add it
        message = "Part number " & Number & " has no supplier info" & vbNewLine & "Do you want to create one?"
        If MsgBox(message, vbYesNo, "New supplier?") = vbYes Then
            ' If user wants to add Part number to Suppliers table then insert a new record and open the Supplier Entry form to edit it
            
            ' How do I reference the currently open database to execute this append query?
            DATABASE.Execute " INSERT INTO dbo.Suppliers ([Part number],[Manufacturer part number]) VALUES (" & Number & ",'');"
            
            DoCmd.OpenForm "Supplier Entry", , , "[Number]='" & Number & "'", acFormEdit, acDialog
        
        End If
    End If
            
End Sub

So the question is how do I execute this query: "INSERT INTO dbo.Suppliers ([Part number],[Manufacturer part number]) VALUES (" & Number & ",'');" on the currently open database? If I use the Execute method as shown then how do I reference the external SQL database? I can't use the CurrentDb method as it thinks that Access is the back end rather than external SQL database. Or is there a better way to execute external queries?

 

[GPGeorge]

You have linked the SQL Azure tables to this accdb. As linked tables, you handle them as you would any other table, local or in a linked accdb.
Why can't you use the CurrentDB method again?

Note: In one place you correctly reference the linked table: "dbo_Suppliers" with the underscore replacing the . which is how Access references linked tables in SQL Azure or SQL Server. Later, your VBA incorrectly references it as "dbo.Suppliers" with the original ., which is not relevant in the context of a linked table in Access. Use the same, correct, reference in Insert statement as you do in the DLookup.

 

[theDBguy]

Hi Andy. I agree with George. It should work as the Current database should be connected to the SQL Server table, if you are using linked tables.

 

[andydinger]

You have linked the SQL Azure tables to this accdb. As linked tables, you handle them as you would any other table, local or in a linked accdb.
Why can't you use the CurrentDB method again?

Note: In one place you correctly reference the linked table: "dbo_Suppliers" with the underscore replacing the . which is how Access references linked tables in SQL Azure or SQL Server. Later, your VBA incorrectly references it as "dbo.Suppliers" with the original ., which is not relevant in the context of a linked table in Access. Use the same, correct, reference in Insert statement as you do in the DLookup.

Thanks very much for your help. I think I was previously using CurrentDB incorrectly and gave up on it too soon. Because with your encouragement to try it again and fixing the query string, I now have it working:

Set dbs = CurrentDb
QueryStr = " INSERT INTO dbo_Suppliers ([Part number],[Manufacturer part number]) VALUES ('" & Number & "','');"
dbs.Execute QueryStr

 

[GPGeorge]

Thanks very much for your help. I think I was previously using CurrentDB incorrectly and gave up on it too soon. Because with your encouragement to try it again and fixing the query string, I now have it working:

Set dbs = CurrentDb
QueryStr = " INSERT INTO dbo_Suppliers ([Part number],[Manufacturer part number]) VALUES ('" & Number & "','');"
dbs.Execute QueryStr

Congratulations on solving the problem. Continued success with the project.