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.

 

[Pat Hartman]

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.

When I have complex search criteria, I use a form and build the WHERE clause on the fly. When the criteria has been entered, I use a dCount() to find out how many rows will be selected. If it is just one, I open the edit form to the selected record using the WHERE argument I built (without the "where"). Otherwise, I open a list form that the user can filter and from the list form, the user double clicks to open the specific record. If there are too many records selected, I give the user the option of adding more criteria.

It is up to you to understand how Access works with linked ODBC tables. Access attempts to "pass through" every query you write so you need to ensure that you are not doing something in the query that will cause Access to request all rows from the server tables rather than just the one I selected. The biggest problem would be the use of UDF's (User Defined Functions) or VBA functions in the Where clause. Since these cannot be sent to the server, Access will request all rows from all tables and do the join and apply the where clause locally. If the Where clause is clean, the query is just sent to the server and any UDF or VBA functions in the Select clause will then be applied locally.

There is some overhead to using Access queries rather than pass-through queries but when working with forms, if you are careful, you won't have a problem and the user won't complain about slowness.

I've been developing Access applications since the early 90's and the vast majority of my BE's either start out as RCBMS of various sorts like DB2, Oracle, SQLServer, Sybase, etc or get converted later on. I always develop using client/server techniques even when the app will never be upsized. good client/server techniques work great with Jet/ACE but old style Access like local filtering do not work well when the BE is RDBMS. I can probably count on one hand the number of times I was forced to write a stored procedure. Mostly they were for complex reports that took data from a bunch of tables. I also use Views to optimize frequently joined tables. They are updateable. And I use pass-through queries for certain types of bulk operations where I might be deleting or updating a lot of rows at one time.

 

[Pat Hartman]

Thanks for the "like" but that doesn't mean anything since we're not children here. Did you understand the concept? you have made a lot of unnecessary work for yourself by not understanding how forms actually work. Here's a simple example of form with subform processing. It is also a useful add in to your application if you have a lot of small lookup tables to manage.

Table Maintenance Mini-app

When you have several lookup tables in an application, it becomes a pain to create separate tables and maintenance forms to manage the contents. This sample is an application that I first developed over 40 years ago, long before Access was a twinkle in Bill's eye. It was constructed using...

www.access-programmers.co.uk

The tables can be in whatever your BE is. They should NOT be local if you are going to use the concept.

 

[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

 

[Pat Hartman]

I told you how to do that. Bind your forms to a query that selects the record(s) you want from the linked table. There is no need for you to use intermediate tables or code to manage this process. Access does it all for you. You just have to let it do that.

Having a table with a couple of million rows is not a problem as long as you don't bind the form to the table. I'm sure English is not your native language so I can't tell if I am getting through. You are doing work that you don't need to do. Here is an example to show you the concept. With two million rows, you probably don't want to use a combo box, but I can give you other alternatives once you understand what I'm talking about. Look at the Employees example. Notice that the form opens empty. This is because there is a where clause in the RowSource query that references the combo and selects ONLY the record from the combo. When you select one of the records, the combo's AfterUpdate event does a Requery and that fetches the record you want to work with. Now you have only ONE record bound to the form and Access can do all the work (except for validation. You still need to do that but do it in the form's beforeUpdate event)

Many-to-many example

I added a new version on 1/16/23 so choose that one rather than the old one unless you specifically want the old one. The old zip file contains both A97 and AXP versions of the database. It uses venue booking as the example and shows the relationship from both sides. A venue main form with...

www.access-programmers.co.uk

 

[Pat Hartman]

I see what your problem is. You are not familiar with Access but you do program in other environments.

Programmers are the hardest people to teach My advice is to not think about how you would do this with C# but instead to learn how you would do it with Access. The whole point of a RAD (Rapid Application Development) tool is that it does all the common stuff for you. You only need to worry about custom stuff like validating data. Access has no idea what your business rules are so you have to step in and in the form's BeforeUpdate event,validate your data and cancel the save if necessary. But Access does know how to fetch records, populate form controls and then save records if appropriate.

I think we have a long way to go here. So, you might also want to look at the employee form's BeforeUpdate event to see how to do validation on a bound form. Think of the Form's BeforeUpdate event as the flapper at the bottom of a funnel. If the flapper is open, the record gets saved. If the flapper is closed, the record is not saved. Your code decides whether to let the user try again or to discard what he has typed.

One warning - Access takes it as a personal mission to always save data bound to a form. So, experienced programmers tend to flail around trying to stop access from saving data. This is because, they don't understand the event model. I gave you the most important piece of information you need about the event model - use the Form's BeforeUpdate event for validation and cancel the save if there is an error. Validation code in other events will work sometimes but not all the time. The Form's BeforeUpdate event works all the time. It cannot be bypassed. When you or Access decide to save a record, that event will run. What will surprise you is when Access decides to save the record. But if you have followed my advice, you will be fine. You can scroll away, toggle mainform/subform, close a form, close the database, whatever your heart desires. Your validation code will always run before the record gets saved and Access will never abandon a dirty record willingly.

Just to test this out, import the tables from the Access database to your test server. Leave the [Switchboard Items] table in the FE it is part of the interface and doesn't hold data that can be changed by the user. Then remove the local tables and link to the server versions. You will need to rename the new tables to get rid of the dbo_prefix. There's only a couple of tables but I'll also give you a link that will do the rename using VBA. Then play with the application to see how it works. I haven't tested it with an RDBMS BE so I can't guarantee there aren't any bugs. Just PM me if you find one and I'll fix it.

Useful code AND the effects of using bad column names

This database has several useful code procedures that you can copy into your BE to fix up defaults you don't like: 1. Change the allow ZLS string property to No If you want to make a text field required, it makes no sense at all to have the Allow ZLS property set to Yes. 2. Turn off sub...

www.access-programmers.co.uk

And finally, if you don't want to use bound forms and bound reports, you do NOT want to use Access as the FE. You will be totally unhappy with how it works because you will keep fighting with it to work the way YOU want it to work. You need to understand the the form object is a class and it has a lot of code that runs behind the scenes making the form work. YOU get to write code in specified events for either controls or the form. So, YOU need to understand those events and what fires them to decide on which event is the correct event for the code you want to write. Events are NOT random. There is ALWAYS one event that is best or at least better for everything you want to do. PLUS since YOU ARE NOT IN CONTROL, there are just some things you might not be able to ever get to work correctly. In that case, write yourself an ActiveX control and give it a com interface and stick it on your Access form