Sql Server Select , With Criteria

[ahmedjamalaboelez]

Good Day Access World,
I use following code to bring data from SqlServer table to access table with same structure columns inside , because I do not like to involve with Passthrough Queries, or record set control, it works fine with me bit
I have two question :
1 - is the following Sql Command fast as passthrough Query , or another way to make TableDef , or QueryDef. ( is it hit directly SqlServer database or not).
2 - How Could I Add Some Criteria to following Sql Command , I want to add Where AccountID = " & StrCriteria & " .

   CurrentDb.Execute "INSERT INTO " & strLocalTable & " SELECT * FROM [ODBC;Description=Test;DRIVER=SQL Server;SERVER=" & strServer & ";UID=" & strUser & ";PWD=" & strPass & ";DATABASE=" & strDatabase & "]." & strServerTable & ";"

    Dim strServer        As String
    Dim strDatabase      As String
    Dim strUser          As String
    Dim strPass          As String
    
    strServer = "192.168.1.194"
    strDatabase = "EasyCount"
    strUser = "sa"
    strPass = "@MyPass"
    
    Dim strLocalTable       As String
    Dim strServerTable      As String
    
    strLocalTable = "lc_account_sql"
    strServerTable = "A02AccountsMaster"
    
    CurrentDb.Execute "INSERT INTO " & strLocalTable & " SELECT * FROM [ODBC;Description=Test;DRIVER=SQL Server;SERVER=" & strServer & ";UID=" & strUser & ";PWD=" & strPass & ";DATABASE=" & strDatabase & "]." & strServerTable & ";"

Thanks In Advance ,
A.J

 

[theDBguy]

Hi. Are you saying you're making a local copy of the table, because you don't like the process to run on the server? Some processes might run faster on the server than on the local machine.

 

[ahmedjamalaboelez]

Hi. Are you saying you're making a local copy of the table, because you don't like the process to run on the server? Some processes might run faster on the server than on the local machine.

no sir , i just bring the required record only to my pending table so after edit i send back to database

 

[theDBguy]

no sir , i just bring the required record only to my pending table so after edit i send back to database

So where does passthrough queries get involved in that? You said you don't like them. I was just curious about that.

 

[ahmedjamalaboelez]

So where does passthrough queries get involved in that? You said you don't like them. I was just curious about that.

Sir , Simply Passthrough Query bring data but i can't handle data in record set so i just bring my data to same structured table so i can handle the table as record source for continuous form

 

[ahmedjamalaboelez]

i need good example or tutorial for handling dataset in main form and child sub form

 

[theDBguy]

Sir , Simply Passthrough Query bring data but i can't handle data in record set so i just bring my data to same structured table so i can handle the table as record source for continuous form

Ah, okay, I think I understand. Sadly, it seems you may be comparing it to something it wasn't intended or designed to do. Passthrough queries are usually commands sent to the server to manipulate the data on the server side. It wasn't meant to fetch a data set and return it to Access. I could be wrong though.

 

[ahmedjamalaboelez]

If you link to the SQL Server tables, you can bind your forms and reports to queries that include selection criteria. That gives you the best of both worlds. You are working with bound forms but there is no intermediate table involved. The key to binding directly rather than using the intermediate table is to NEVER, EVER bind a form to a table. Always use a query and always include criteria to limit the rows selected.

Thank you Mr. Pat Hartman
I really appreciate those kind of advices , after more 25Y experience .
But Really My Problem That I Work with company server and data, so I just want to figure out a sample code for:
- Get Invoice Record from database (so i can modify after that i send it back again i use Run Sql query which hit linked table which make problems every time i call it had couple million invoices over Internet IP6 Connection )
- then after edit this specific invoice i return it back to server .
that's mean :
i need to know the process of sending SqlServer query directly and return it back in a record set , and handle the modification and send it back again to server .
thank you so much

 

[ahmedjamalaboelez]

as example ,
IN C#
I Create Stored Procedure On the Server , I call this Proc. and return data in data table = Record Set
after i modify this record i call a stored procedure and loop through data table records and return updated to server .

that's easy to manage in C#
But I can not Manage in Access