How to reference open SQL database (1 Viewer)

andydinger

New member
Local time
Today, 12:13
Joined
Jun 21, 2022
Messages
3
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:
Code:
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?
 
Solution
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.

GPGeorge

Grover Park George
Local time
Today, 05:13
Joined
Nov 25, 2004
Messages
1,776
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.
 
Solution

theDBguy

I’m here to help
Staff member
Local time
Today, 05:13
Joined
Oct 29, 2018
Messages
21,358
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

New member
Local time
Today, 12:13
Joined
Jun 21, 2022
Messages
3
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:
Code:
Set dbs = CurrentDb
QueryStr = " INSERT INTO dbo_Suppliers ([Part number],[Manufacturer part number]) VALUES ('" & Number & "','');"
dbs.Execute QueryStr
 

GPGeorge

Grover Park George
Local time
Today, 05:13
Joined
Nov 25, 2004
Messages
1,776
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:
Code:
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.
 

Users who are viewing this thread

Top Bottom