ODBC Linking

[LEXCERM]

Hi there,

I've started to delve into the world of ODBC and I have hit my first problem.

I have a BE which stores two ODBC links to our SQL server, but I want to query these in the front end. Linking tables option does not show the ODBC connections.

Could someone give me a little nudge in the right direction please as this is a start of a very long learning curve!

Many thanks,
Paul.

 

[boblarson]

1. The backend has the access tables.

2. The frontend should have links to the Access backend and then also the links to the SQL Server tables (or Oracle, mySQL, etc.). You don't link them in the backend for those.

 

[LEXCERM]

Hi Bob and thanks for the reply.

Okay, so the ODBC link goes in the FE. So if I have 15 FE's I could potentially have 15 simultaneous ODBC connections. I queried 2.4 million records in just one FE and it took a fraction of a second to run. Would 15 of these queries, run at the same time, cause major performance issues over a Citrix network?

Bearing in mind these queries will only be used to read data and nothing else, what considerations should I be made aware of?

Sorry for the naive questions but as mentioned before this is my first dabble with ODBC.

Many thanks,
Paul.

 

[Banana]

Consider taking a read of Beginner's Guide to ODBC which should discuss how ODBC work as well containing links to other articles discussing about optimizations. You may want to take note of "Using Jet Intelligently" section.

Hope that helps.

 

[LEXCERM]

Thanks for the link Banana. Will read with interest.

Rgds,
P.

 

[LEXCERM]

Okay, so I've read the link (some of it over my head at the moment), but need some advice.

I've got a continuous form for which it's record source is linked to a query (based on the ODBC link). When querying the 2.4 mill records normally, it produces the data in under a second. As soon as I add a criteria to one of the fields i.e.="SomeRefNumber", it takes upto 90 seconds. So I've tried DAO, Pass-Through Query, added a filter to the form itself, but I can't reduce the time. The field I am using the criteria on is set as a primary key.

Is it that I am tied by the servers I am working on or is there a method to reduce the time.

Thanks again,
Paul.

 

[Banana]

If it's really just simply "=SomeNumber" with no function calls, then it suggests to me that the column isn't indexed. Since it's on the backend, you have to create the index there.

Also, I wouldn't pull 2.4 million keys into a form, ODBC or not. If you can restrict to only "active" records that's appropriate for your purposes... last 30 days? hard top limit of 1000 records? It will help a lot. Be aware that just because it takes a second to pull 2.4 million is due to some tricks Access plays:
1) It pulls only keys, not the actual records.
2) It loads only the records needed to fill records.

If you were to wait until it finished pulling the last record, it'd be very long time and is another reason why adding WHERE clause with nonindexed column or function calls can suddenly slow since it requires evaluation for the whole 2.4 million records. Best to limit to a small set.

 

[LEXCERM]

Hi Banana,

I get what you're saying and yes there is no index set for the column I require.

The ODBC link is in the front end, but if I try and create an index I get an error message saying that I can only create indexes from the source data i.e. directly from the tables on the sql server, I presume.

Does that mean I need to get someone in IT to index the field from the source data, or can I do something in Access which will allow me to do so.

Sorry to keep bugging, but I need to get this correct in my mind.

Thanks again,
Paul.

 

[Banana]

As I said, the index has to be created at the source... in your case, at the SQL Server.

You could send a passthrough DDL statement but that also assumes you have elevated privileges and besides, IT may not be too happy if you tried to do it. Best to work with your DBA in getting this done the right way - for all I know, DBA may have other considerations to weight in creating indexes.

Generally speaking, Access can't & shouldn't be used to "manage" the backend - it can only consume the data based on what queries you send but beyond this, it has no control over how data is indexed, structured, locked or anything. This is entirely up to how the backend's engine handles it and thus you have to use the tools for the backend with the access to those tools (e.g. you're a DBA yourself and not just an user).

HTH.

 

[LEXCERM]

Since it's on the backend, you have to create the index there.

Sorry, but when you said this in a previous post I thought you was implying the Access BE, rather than the server as the BE.

Anyway, spoke to IT and they said an index could be created to that column but it's not as simple as just changing a flag. As I am not familiar with sql, I'll take there word for it.

The other thing I have read is about creating indexes using DAO/SQL (I think). So basically, create a recordset based on the ODBC records, create an Index for the desired field, then query that.

No?

Rgds,
Paul.

 

[Banana]

I do apologize for my poor choice of words - Yes, I meant ODBC backend, not just an Access backend. It's just typical that when I use ODBC source, it's usually the only backend. An application with both ODBC backend and Access backend is comparatively rare but not unreasonable.

Unfortunately, you can't use Index in a DAO Recordset. This only work for Table-Type Recordset which is only available when you're using Access local tables. You can't even open a table-type recordset against a linked Access table, less an ODBC linked table. And even so, it has to use an existing index so it doesn't actually create a new index; just basically declare "use this to help us do this and that faster."

I have to confess, though, what your IT has told you sound somewhat odd but maybe it's just me not knowing the whole story. Maybe we can try and see what we can do here... what is the SQL you were trying to use? Maybe we can see how to improve on this.

 

[LEXCERM]

No need to apologize. You've been a great help for which I am thankful for.

I'm not at work at present, but my set up at present is thus.

1) a query (not sql as yet) which pulls in 5 of the 50+ columns from the ODBC backend.
2) colA has a criteria: ="HOC" (this executes the 2.4 mill records in .7 seconds I suppose because this criteria can be one of only 6 options)
3) when I apply a secondary criteria in ColB, this is where the probs start. An example of the data in this field is ABC1234567 (10 characters, alpha-numeric). In this instance I am specifically trying to find a single unique string in colB. This takes nearly 90 seconds to execute at worst.

Maybe I need to do something to the string first.

Cheers,
Paul.

 

[Banana]

1) FYI - all queries are written in SQL - when you use the builder, Access is actually writing the SQL behind the curtain. To see the SQL, View -> SQL View.

2) I'm guessing we're talking only one table? No Joins? You provide a filter ="HOC" which returns 2.4 millions? Do you actually need all records matching ="HOC" and ="ABC1234567", or do you have any filter that would reduce the records? What you are going to use it for?

3) You say it's unique... Could colB actually have duplicates or would you be prohibited?

 

[LEXCERM]

1) Was aware of that, but I presume the a stored query returns records quicker...so I've been told!
2) Well, "HOC" relates to our offices only so would reduce the data initially. I thought (maybe wrongly) by reducing the records in the first parse might speed things up
Don't really need to apply "HOC" though.
3) Yeah, there could possibly be duplicates but not always. May have led you astray on the unique bit.

BTW, do you work on here full-time?!

rgds,
Paul.

 

[LEXCERM]

As a follow up to this, I executed a make-table query and transferred the 2.4 million records to an Access table. The problematic field was then indexed and the search completed in 1.5 seconds as opposed to over a minute.

I now have to persuade IT to index the field from the main source in the SQL table.

 

[Banana]

Just mention to them that Access is beating the pants off their beloved SQL Server. That should get their attention.

Why they didn't go and index it is beyond me.