ODBC Speed 1

[mswilson16]

I have a Database that has around 20 linked ODBC tables in it. I am having real troubles with the speed of the quering. Because the tables are linked I can not create indexes on the required fields like I normally would.

Does anyone know of a way to make the ODBC link more efficient???

It has got to the stage that when I open a table I can not even click to view the last record because access will freeze.

Also how do I make the ODBC connection read-only?

Any help or advise what so ever will be greatly appreciated!

Thanks

WIlson

 

[scriverb]

Are you creating the ODBC link dynamically in your program. If so, there is a problem with queries re-optimizing themselves even if the link that they were created with is the same path and link that you create through code.

That optimization could be happening each time your queries are being run.



Bob Scriver

 

[Rick39]

Something to exclude during your search.....

thread706-493500

 

[scriverb]

Rick39: That sounds really reasonable as a solution to this problem. Have to wait to see what MSWILSON16 has to say. I will tuck that one away for a day when I am pulling my hair out.

Thanks for the redirect. Star for you.

Bob Scriver

 

[mswilson16]

thanks rick but like scriver says I will tuck that one away for future reference but unfortunately it doesn't help my current situation.

Access can cope with all but one table. The system that I am getting the data from is a stock control system. The one table that can not be read is the sale items table. I was thinking that if I could just get the data to be stored in an actual table in the database rather then a linked table then the system may be able to manage quering it. But the problem is that the table is that big that the system can not even transfer the data from the linked table to a mirror of it in the database. also the data will not be as live as when it was a linked table.

Any help will be great and appreicated lots.

wilson

 

[Rick39]

Was worth a shot....actually, the last Oracle ODBC problem that I had with "freezing" (and ODBC Call Fails) was corrected by SP3 (see

http://support.microsoft.com/default.aspx?scid=kb;EN-US;326605

)

I generally don't query linked tables (because of performance) - rather, I've found Pass Through queries to be much quicker

 

[GingerR]

i vote for pass-thru queries as well.

another thing to check is if you have ODBC timeout property set to 0 (instead of default of 60 second). won't help performance, but may make it so it doesnt time out.

another suggestion: is there a reduced number of records from the table that you could live with? if so you could get oracle db owner to create a view for you, so you are starting with fewer records. data will still be live.

another: if nothing else works and you can live with data which is imported, say, every morning at 6 am or something, you can get the oracle db admin to create a text file export for you and plop it on some server for you to pick up and import every day. obviously not the best, but just an option of worse comes to worse.

 

[jrbarnett]

Ensure your queries are set as pass through rather than a select query, as otherwise access has to download all the data and process it itself, whereas with pass through, the database server does the interpreting and only returns the results.
You may need to rewrite some queries to use the database server's dialect of SQL, but this shouldn't take too long.


John Barnett
--
Please consider awarding me a star if you feel my posting has been useful. Thank you.

 

[mswilson16]

when i change the queries to Pass through query via,
Query -> SQL Specific -> Pass Through

I have the Sql statement of:

SELECT *
FROM [Ticket]
WHERE ((([Ticket].[TICKET_NO])="026864&quot);

I am getting an ODBC call failed error. It says something about the syntax being wrong.

Anyone any ideas????

Please help asap, I am running out of time.

Thanks in advance

wilson

 

[Rick39]

Try:

SELECT * FROM Ticket WHERE TICKET_NO = 026864

(I just tried adding some of the punctuation that you had used and got the same ODBC Call Fail messages)

 

[mswilson16]

it still doesn't work, i have tried what you said and one time i got a message saying that there are too many fields selected so i tried putting just the fields that i want and it doesn't work.

I will tell you about the whole situation.

What I have is an old Accounting system that is based in DOS. I know access. I need to get reports from this database. I have got the ODBC link create but it is really slow. It can open all but one table (the biggest one). I need to create queries that bring information from more than one table/query.

If there is anyone out there that can help me that anyhelp what so ever will be great.

My main aim is to be able to open the really large table and to be able to create queries that drag information form many tables.

Thanks in advance

Wilson

 

[Rick39]

Without some more detail (configuration, sql, etc..) it's a little difficult....there are some pretty specific threads @

http://groups.google.com/groups?as_...oup=comp.databases.ms-access&lr=&num=50&hl=en

in reference to Access to Oracle Pass Thru queries that may get you there quicker

 

[DonQuichote]

I think Strings in SQL are usually in single quotes, so:

SELECT * FROM Ticket WHERE TICKET_NO = '026864'

Might work. I do not know if Oracle supports underscores in field names and I also do know know if field names are case sensitive in Oracle.

Best regards

 

[Hap007]

Wilson,

I am sorry to jump in to this discussion so late, but better late than never.

If the other fine suggestions here do not work, then consider querying your old DOS Legacy data and make one or several temporary local tables in your db.
You could create work tables and append data or use a make query table that would then recreate the temp tables each time they are run. To do this, use the doCmd.OpenQuery
Example

On Error Resume Next

'Turn off access message
DoCmd.SetWarnings False
'Step 1) Delete Query - Remove old query Data
DoCmd.OpenQuery "qryWeekRemoveDisplayData"
'Step 2) Append Query - Now get Week 1 Data
DoCmd.OpenQuery "qryWeek1Append"
'Turn Mesages back on
DoCmd.SetWarnings True


Then, run your reports and when you are done, either delete the tables or delete the temporary records.

This has saved me more than once.

Hope This Helps,
Hap


Access Developer

http://www.fcs-software.com/fcsaccessdev.html

-
Specializing in Access based Add-on Solutions for the Developer

 

[BobJacksonNCI]

I'm an Access/SQL novice. (NCR mainframe expert...
what? They don't make them anymore.)

I use Hap's technique and just want to add that
using append queries with designed work tables (I
even added 'twk' to our naming conventions) offers
the added benefit of allowing indexes. Make table
queries don't offer that speed-up flexibility.
One of my applications starts out deleting all records
from the twk tables and then appending records so I
start with updated copies.

Good Luck!
Bob (M.E.A.N.)
Moderately Exasperated Access Novice