sql (1 Viewer)

[DevAccess]

Hello

I have a database which has been linked odbc table in the database and database takes lots of time to open and view report etc.

What would be best approach in this, does linking odbc table from sql server makes it uneasy and hampers performance ? what alter solution would be done in this case ?

Thanks
MAN

 

[isladogs]

I don't use ODBC but I see no reason why that should be an issue in itself.
I prefer using connection strings. You could try that approach instead.
This site has just abut all you need to know ...and more https://www.connectionstrings.com/

 

[DevAccess]

I don't use ODBC but I see no reason why that should be an issue in itself.
I prefer using connection strings. You could try that approach instead.
This site has just abut all you need to know ...and more https://www.connectionstrings.com/

Thats true but the linked ODBC table are used in query for reference: I know connection strings to used SQL mostly in unbound forms.

Please advise.

Thanks

 

[isladogs]

Tables linked by connection strings can be used in exactly the same way as those linked using ODBC. In fact, whichever method you use, they can be used like any local table in queries, forms, reports and modules.
I prefer connection strings as I find them far more straightforward.
Others prefer ODBC.

I don't understand your comment about unbound forms. If a form is unbound it has no record source so no connection of any kind.

 

[DevAccess]

Tables linked by connection strings can be used in exactly the same way as those linked using ODBC. In fact, whichever method you use, they can be used like any local table in queries, forms, reports and modules.
I prefer connection strings as I find them far more straightforward.
Others prefer ODBC.

I don't understand your comment about unbound forms. If a form is unbound it has no record source so no connection of any kind.

Ok, I have linked ODBC table [sql] in the access db and those tables are being referenced in queries and few of this tables and queries are bound to forms and report that makes database lots of slower.

Comming back to your solution of connection string, can connectionstring would help me to over come linked oDBC tables in the database ?

As far as I know connection strings are helpful in unbound forms when you want to save the records in the database or to retrieve but can it be bound to the forms in the view or can be reference in queries ?

 

[isladogs]

Please read my previous reply again as I've already answered most of the points.
Also please read the link I gave originally as it may help your understanding of what the phrase means.

It's a different method of managing linked tables. If used, it completely replaces ODBC. You use one or the other, not a combination of both.

 

[DevAccess]

Please read my previous reply again as I've already answered most of the points.
Also please read the link I gave originally as it may help your understanding of what the phrase means.

It's a different method of managing linked tables. If used, it completely replaces ODBC. You use one or the other, not a combination of both.

How to configure this connection string ? is this used in coding or it can be linked in same way as we do click on ODBC linking of table ( i mean configuration wise )

 

[isladogs]

How to configure this connection string ? is this used in coding or it can be linked in same way as we do click on ODBC linking of table ( i mean configuration wise )

No you don't use the ODBC wizard.
A typical method is to create a local table which contains a list of all linked tables together with their 'location'. The location is one or more sql server BE databases or Access BEs or Excel spreadsheets (or anything else you want to link to)

The connection strings are instructions that allow Access to loop through and reconnect these tables. For example in a large split networked database with 320+ linked tables from a mixture of sources the relinking takes less than 10 seconds. It only needs to be done once, not on each computer. It really is easy but like anything else looks complicated the first time

This link gives VERY detailed instructions but it may be too complicated for you and confuse you further.
http://www.accessmvp.com/DJSteele/DSNLessLinks.html

I'll try and find you a simpler explanation as well.
Suggest you google 'DSN less connection strings Access'

 

[Pat Hartman]

Colin, when you run Access queries against linked tables in SQL Server (for example), you ARE using ODBC. When you run pass through queries against tables in the RDBMS, you bypass ODBC because the queries are sent directly to the server for processing. It doesn't matter whether you link the tables using the ODBC manager or via code using DAO/ADO and connection strings. The result is the same - you can now write a query using Access syntax against a non Jet/ACE database and the ODBC driver will convert the Access syntax to whatever the target database requires which in the case of SQL Server is T-SQL. When you write pass through queries to SQL Server, they are written using T-SQL syntax.

DevAcccess,
Is this a database that started out being linked to Jet/ACE tables? Did you do any optimization as part of the conversion such as eliminating any VBA and UDF queries from anything except the SELECT clause of your queries? How about changing your forms so that instead of being bound to tables or queries without criteria, you instead use a query with selection criteria to minimize the rows returned from the server.

 

[isladogs]

Pat,
You are of course correct. In fact, the first part of the connection string is often ODBC!

Its still true that the approach I'm suggesting doesn't use the ODBC wizard to create a DSN.
I accept that my explanation wasn't as accurate as it should have been.

 

[DevAccess]

Colin, when you run Access queries against linked tables in SQL Server (for example), you ARE using ODBC. When you run pass through queries against tables in the RDBMS, you bypass ODBC because the queries are sent directly to the server for processing. It doesn't matter whether you link the tables using the ODBC manager or via code using DAO/ADO and connection strings. The result is the same - you can now write a query using Access syntax against a non Jet/ACE database and the ODBC driver will convert the Access syntax to whatever the target database requires which in the case of SQL Server is T-SQL. When you write pass through queries to SQL Server, they are written using T-SQL syntax.

DevAcccess,
Is this a database that started out being linked to Jet/ACE tables? Did you do any optimization as part of the conversion such as eliminating any VBA and UDF queries from anything except the SELECT clause of your queries? How about changing your forms so that instead of being bound to tables or queries without criteria, you instead use a query with selection criteria to minimize the rows returned from the server.

Thanks Pat, In fact I am using access query for the bound forms, in turns query uses linked table [ which is odbc linked table ].

Can you please explain me bit litte here on your below text:
Did you do any optimization as part of the conversion such as eliminating any VBA and UDF queries from anything except the SELECT clause of your queries
How about changing your forms so that instead of being bound to tables or queries without criteria, you instead use a query with selection criteria to minimize the rows returned from the server.

Also what is difference between ms access query and pass through queries ?

Thanks

 

[DevAccess]

Thanks Pat, In fact I am using access query for the bound forms, in turns query uses linked table [ which is odbc linked table ].

Can you please explain me bit litte here on your below text:
Did you do any optimization as part of the conversion such as eliminating any VBA and UDF queries from anything except the SELECT clause of your queries
How about changing your forms so that instead of being bound to tables or queries without criteria, you instead use a query with selection criteria to minimize the rows returned from the server.

Also what is difference between ms access query and pass through queries ?

Thanks

I got to know what are pass through query now, it directly runs the query on sql server instead of access, will this eliminate use of linked ODBC table ? I know I can directly refer sql table here in pass throgh query ?

Will it help me in imporving performance if I use pass through query ?

 

[Pat Hartman]

You didn't answer the questions about how the app was structured. Was it originally linked to Jet/ACE tables? Are you using VBA or UDF functions in the queries that are slow? Are you joining heterogeneous tables (ie joining a SQL Server table to a Jet/ACE or Excel table)?

Pass through queries are not a panacea. They are slightly faster because they bypass ODBC but in most cases, you won't notice. Some places it does make a difference though. I develop applications primarily for use with SQL Server and I always use linked tables and bound forms. Occasionally, I create a view to optimize a join (views are compiled on the server so an execution plan has already been created and saved). When you send a query to the server, the server must first create an execution plan and that adds a small bit of time. If I have to do bulk deletes, I use TRUNCATE TABLE in a Pass through query. This eliminates all logging so the operation is very fast. If I am not deleting all rows, I use a normal DELETE query with criteria but if I'm deleting lots of records, I still will send it as a pass through query. And infrequently, I have created stored procedures to gather data for a complex report or dashboard form.

Access attempts to "pass through" ALL queries. You can defeat Access by doing some of the things I mentioned in the first paragraph. In that case, Access will request the entire table be brought down from the server and then apply the join and/or criteria locally if ODBC determines that it cannot replicate your query with T-SQL.

Forms bound to pass through queries are not updateable and subforms can't be bound to pass through queries.

Make sure that all your tables have primary keys. Use autonumbers/identity columns or if you have natural keys, you can use them but there are factions that think natural keys are the devil's spawn. I actually prefer them since they sometimes eliminate the need for a join but most tables don't have single field natural keys and I prefer a single field PK to a multi-field natural key. Joins are simpler and combos and listboxes don't work with multi-field unique IDs. They need single field primary keys so that has tipped me to using autonumbers/identity columns in almost all cases.

Make sure you have the necessary indexes defined. When you create tables in Jet/ACE, Access automatically creates indexes for foreign keys. SQL Server does not do this for you so you have to do it yourself. Don't go crazy with indexes because while they speed up searching and joins, they slow down updates.

 

[DevAccess]

You didn't answer the questions about how the app was structured. Was it originally linked to Jet/ACE tables? Are you using VBA or UDF functions in the queries that are slow? Are you joining heterogeneous tables (ie joining a SQL Server table to a Jet/ACE or Excel table)?

Pass through queries are not a panacea. They are slightly faster because they bypass ODBC but in most cases, you won't notice. Some places it does make a difference though. I develop applications primarily for use with SQL Server and I always use linked tables and bound forms. Occasionally, I create a view to optimize a join (views are compiled on the server so an execution plan has already been created and saved). When you send a query to the server, the server must first create an execution plan and that adds a small bit of time. If I have to do bulk deletes, I use TRUNCATE TABLE in a Pass through query. This eliminates all logging so the operation is very fast. If I am not deleting all rows, I use a normal DELETE query with criteria but if I'm deleting lots of records, I still will send it as a pass through query. And infrequently, I have created stored procedures to gather data for a complex report or dashboard form.

Access attempts to "pass through" ALL queries. You can defeat Access by doing some of the things I mentioned in the first paragraph. In that case, Access will request the entire table be brought down from the server and then apply the join and/or criteria locally if ODBC determines that it cannot replicate your query with T-SQL.

Forms bound to pass through queries are not updateable and subforms can't be bound to pass through queries.

Make sure that all your tables have primary keys. Use autonumbers/identity columns or if you have natural keys, you can use them but there are factions that think natural keys are the devil's spawn. I actually prefer them since they sometimes eliminate the need for a join but most tables don't have single field natural keys and I prefer a single field PK to a multi-field natural key. Joins are simpler and combos and listboxes don't work with multi-field unique IDs. They need single field primary keys so that has tipped me to using autonumbers/identity columns in almost all cases.

Make sure you have the necessary indexes defined. When you create tables in Jet/ACE, Access automatically creates indexes for foreign keys. SQL Server does not do this for you so you have to do it yourself. Don't go crazy with indexes because while they speed up searching and joins, they slow down updates.

Thanks Pat here is the answer :

You didn't answer the questions about how the app was structured. Was it originally linked to Jet/ACE tables? Are you using VBA or UDF functions in the queries that are slow? Are you joining heterogeneous tables (ie joining a SQL Server table to a Jet/ACE or Excel table)?

Answer :
1> Existing structure has ODBC linked SQL tables into the database,
2> VBA is being used but not in terms of UDF and all it is very straightforward,
3>Access query does the join on linked ODBC sql table.
Also, You mean to say using pass through query does not make any difference right ? since they are not updated in forms and can not be used in subforms and linking ODBC sql tables through connection string would make any difference as such in terms of performance.

 

[Pat Hartman]

I'm saying that in over 20 years, I've written fewer than a dozen pass through queries so they are NOT the place to start. You may end up there with a pass through or even a stored procedure for the report but you would NOT start there.

I know you are using SQL Linked tables. The question is - Was the database ALWAYS SQL Linked tables or was it developed initially using Jet/ACE tables meaning that we might have old Access techniques to undo.

I was referring to VBA and UDF (user defined) FUNCTIONS. Every application needs some VBA code. The question is - Are you attempting to use FUNCTIONS in your QUERIES? SQL has a set of functions that it supports and many Access VBA functions have direct conversions. For example Nz() gets converted to IIF(). SQL Server doesn't know anything about VBA so any VBA or UDF function you use CANNOT be sent to the server. Access has to ask for all the data to be returned to memory on your PC where Access will complete the process there. There is no problem using VBA or UDF in queries against Jet/ACE tables because Access is the query engine and Access understands VBA.

Do your tables have indexes and primary keys?

I feel like we are not communicating.