Solved Use saved Access query in Sql server statement

[Gaztry80]

Hi !

I want to load some data from an Access form into a sql server table, but i am facing some challenges.
The data I want to load is from/displayeb by a "saved qry in Access", lets call it "qryAccessDemo". This query I use in a form form with some related criteria's. So when I have a form open, the form/query is only displaying the results I need. These result I want to load into a sql server table and I want to use vba and a double click event for it. I have populated the following code for this till now:

Private Sub FieldA_DblClick(Cancel As Integer)

Dim sqlstr As String
Dim objControl As Control

    sqlstr = "Insert into TableA(NamePerson) " & _
    "select NamePerson from qryAccessDemo"
        
    ConnectDatabase
    DBCON.Execute sqlstr
    CloseDatabase
End Sub

*The ConnectDatabase and CloseDatabase is a module which i created with the following youtube tutorial.

My code is not working, which is i think logical since sql server does not know the saved qry in access "qryAccessDemo".
A work arround for me would be to fully write out the query or create a view in SSMS. However, i guess there should be a way to make this work.
Could somebody help me out or guide me in the right direction how i can use the saved Access queries within a sql server related sql statement?

Thank you very much!

 

[arnelgp]

the easiest would be to create a Linked table in ms access from your table in SQL server.
then you can simply use your query to insert records.

 

[sonic8]

Long shot, untested, and it will only work if the SQL of the Access query is compatible to T-SQL:

sqlstr = "Insert into TableA(NamePerson) " & _
    "select NamePerson from (" & CurrentDb.QueryDefs("qryAccessDemo").SQL & ") AS x"

A more conventional approach would be to link TableA and then execute your original insert query in Access.

 

[Gaztry80]

Long shot, untested, and it will only work if the SQL of the Access query is compatible to T-SQL:

sqlstr = "Insert into TableA(NamePerson) " & _
    "select NamePerson from (" & CurrentDb.QueryDefs("qryAccessDemo").SQL & ") AS x"

A more conventional approach would be to link TableA and then execute your original insert query in Access.

Thanks for replying, this one does not work, I think it is because the tables in access are named with "dbo_" and sql does not recognize this.

 

[Gaztry80]

Long shot, untested, and it will only work if the SQL of the Access query is compatible to T-SQL:

sqlstr = "Insert into TableA(NamePerson) " & _
    "select NamePerson from (" & CurrentDb.QueryDefs("qryAccessDemo").SQL & ") AS x"

A more conventional approach would be to link TableA and then execute your original insert query in Access.

This would maybe a good solution, but i have to think about the limitations. Eventually, I want to do some tests before loading the data and i wanted to use the sql server queries for this purpose.

 

[GPGeorge]

Thanks for replying, this one does not work, I think it is because the tables in access are named with "dbo_" and sql does not recognize this.

A lot of people are caught off guard by this mannerism of Access. In SQL Server, tables can be segregated into Schemas, which are containers for groups of tables. The default Schema in SQL Server is called dbo (short for database owner). So, in SQL Server, a table named, for example, "TableA" in Access, is actually named "dbo.TableA" because it is contained in the dbo Schema.

You can create other Schemas and put tables in them, if you feel the need. I've seen, for example schemas for "HR", "Operations", and "Production" etc. in large SQL Server databases, with tables pertinent to those functions in the different schemas. AdventureWorks, which is a sample SQL Server database offered by Microsoft, does that.

When the SQL Server tables are linked in Access, that Schema is carried along for the ride, but Access can't handle the . designator as part of a table name, so it changed it to the underscore. Hence, "dbo_TableA" in Access.

A lot of times, after linking SQL Server tables and seeing this feature, you might run an update function to rename all of the local Access tables the way they were originally, i.e. "TableA". That's just an alias Access uses anyway. The real table is "dbo.TableA" in SQL Server, but the default alias is "dbo_TableA", so calling it by its more familiar name, "TableA" is just fine.

By the way, "TableA" itself seems ambiguous for a table name. I think most people try to use more meaningful names to reduce ambiguity.

 

[bastanu]

Why not make the insert a pass-through query instead?

Cheers,

 

[Pat Hartman]

Forms do not store data. Tables store data. If you have a query that selects the data you want, you can use that query in an append query to append the data to a linked SQL Server table. You need to do two things.
1. Link to the SQL Server table. Rename the table if you don't want the DBO_ prefix. But you might want to leave it if it is going to end up being the same name as the Access table.
2. Open the QBE and select the Select query that has the criteria that selects the data you want.
3. Change the query type to append and pick the target table. Assuming the column names all match, you're done. Although, you may need to remove the autonumber field if you are using one. There may be a duplicate in the server table and that would cause the query to fail. If the column names don't all match, then you have to manually map them.

If the query uses the form to provide selection criteria, then you can only run the query from the form when it is open.

 

[Gaztry80]

Thanks for all explanation and different suggestions ! Learning a lot these days.
I have used the method suggested by arnelgp & sonic8, which was suggested in the begin of the thread.