Microsoft Access - odbc, oracle, and access

Yes, I'm just getting around to this, and have the following questions (so far):

An Access db relies upon a good number of Oracle tables.  In order to query them and dump the results to local tables, I've created a general-case pass-through and nested it inside a select-into, and I do this on the fly in vba (I have found some performance gains - 50%-60% it seems - over the legacy method which opened an ado recordset and then wrote it to a local table line-by-line).   I use dao.

This has worked famously, but a couple of questions come to mind.  At one point when testing this, every pass-through threw a "ODBC fail" message.  I have no idea why, as each query specifies its connection via the qdef property.

It's important for me to understand why the queries perform as designed on 99.9% of the test, but on that .1% there is a wholesale failure to connect.  I must be able to deal with this possibilty one way or another, and knowing what causes it would be a help.

Don't be afraid that you'll insult my intelligence or lack thereof.  Connections are a mystery to me and I admit it.  I follow instructions and get results, but that isn't exactly mastery. 

There are times when the network is completely unresponsive, but I'm not sure if that would cause this sort of behavior, specifically.

It is my impression that as each query is executed, it establishes its own relationship with the source and that therefore there is no general connection routine necessary in the procudure.  When it's done, the queries are deleted the next table is processed.

I've also noticed that the "Records Affected" property of the qdef returns zero.  I've googled this, read the KB, and it's no help.  Interestingly, as I began to implement this routine, I was actually creating queries in the designer and intended to do one for each table.  I did about four, tested them, and they all performed beautifully, including returning "RecordsAffected."  It was only when I shifted gears and replaced all those queries with about 8 lines of vba that the property became useless -- and yes, I did attempt to programatically set it -- no dice.

Guess I'm rambling here. What I want to do is create a nice, bulletproof data processing routine for the user.  But I can't have Access "failing to connect" willy-nilly.

Is there concensus on whether it would be advantageous to maintain permament links to these off-board tables and simply refresh them; then create local tables by querying them?  Advantageous in anyway, but specifically as regards reliablilty of connection and performance.

Thanks in advance.

Doesn't Oracle have an export mechanism that you could just dump the entire set of tables out to Access?  I know SQL Server has this capability.

I've been developing with Access since 1994. I fell in love with Access because of its ability to "Access" various ODBC data sources. Very few of my applications use Jet/ACE databases. The majority use SQL server because most shops have that database installed these days. But over the years, I've worked with Oracle, DB2, Sybase, and names you wouldn't even recognize. The one common thread is that in ALL cases, I used linked tables. In only one situation did I find that I needed to use pass-through queries and that was to delete rows in a temp table. I ended up using TRUNCATE because that was the most efficient method of clearing a table.
It is a little known fact that in ALL cases, Access attempts to "pass-through" every single query to the server for processing so creating specialized pass-through queries saves little processing time and creates significant work on your part. You can certainly defeat the query processor and make it bring back bazillions of unnecessary records so it is important that you know what causes the problems. When you use Access querydefs against linked tables, there is a little overhead involved because the query processor needs to figure out what it needs to send to the server. With a pass-through query, the query processor simply sends the query without further ado. If you use saved querydefs rather than dynamic SQL, Access "compiles" the querydef the first time you run it and saves the query plan which saves time on subsequent executions. When you use dynamic SQL (SQL strings in VBA code), Access has to "compile" the query each and every time it runs because it has no way of saving the execution plan. This slows down the application unnecessarily if a query is run repeatedly in a session and it is also responsible for database bloat. It takes a lot of workspace to calculate an execution plan and Access has no way to recover free space until it is compacted. So if you use this technique (SQL strings in VBA), it is imperative that you compact frequently. In fact I would suggest compact on close to reduce the bloat.

Every case is different so you need to determine if it is better to summarize data on the server and bring the result set into an Access table for further summarization or use with multiple reports or to simply bind the reports to queries on linked tables.  One application I developed for a regional bank processed millions of transactions.  What I found was, I could summarize the data to a certain point and download it where it was used in a dozen reports.  Other parts of the application that just used current transactions just used queries against linked tables.

One thing I would suggest if you stick with the download method, is to use a separate database to hold the downloaded tables.  That eliminates the bloat caused by creating/deleting tables or appending/deleting rows.  At the start of the download, you either copy a pre-defined empty database or create one on the fly.  Then download all the data to the separate database.  That way, you start every session with a clean slate.

Nesting the pass-through into a select-into obfuscates the messages send by the server.  That’s why you no longer see the affected records count.  Regarding your other question, Access is extremely sensitive to network blips.  If Access loses touch with the network frequently, you need to get your network people motivated to find the problem or live with it.  One nasty problem that is difficult to find is a faulty nic card which may not even be on your computer.

Among other concerns, a major one was that the primary operator of this app can't babysit an import routine that was sporadically prompting for odbc credentials. That is, queries executed against linked tables apparently suffered interruption. Either they prompt for credentials and proceed or they bug out and the next query prompts for them -- I'm not sure.

So I decided to "modularize"  the operation, ensuring a connection with each table in the update roster.  I opted for queries to replace the legacy procedure in part because of advise you gave earlier (and again today) that a saved query object processes faster.  However, my programmer's instinct and maintenance concerns caused me to setup the VBA; I tested it and it was 3 times faster than the legacy method -- so how could I complain about that?  Still, I could create the queries once and leave them in place, if persuaded.  And given a bit more time, I'll test it.

I don't all things about all things so I have to leverage what I do know and this solution seemed to do that effectively.  But of course, I'm always learning, so here we are.

Reiterating, then, network hiccups are a problem.  So I tried to find a way to minimize the network traffic into packets, I guess you'd say. I can error-trap each cycle and feedback information pinpointing individual download failures (or retry them automatically) so that the entire process doesn't have to be re-run for lack of that knowledge.

Anyway, very helpful as always and I'll reread your answer tomorrow after the first pass sinks in a bit.

It very well might, but I'm not sure how I'd make that happen on the client side; this is a big, understaffed place and getting anything done is like pulling teeth.  I'll have to look into what's possible in Oracle so I at least know what to ask for..

When you use linked tables against a secured database, you should not store the user Id and password when you link the tables.  You should use the logged in user's credentials when he opens the database to refresh the links.  There are pros and cons with this.  The pros being the app is more secure (Access stores the userID and password for linked tables in plain text and so anyone who can open the database can see them, even in an .accde) and since you will be forcing a refresh everytime you open the database, you are unlikely to run into the problem of the schema changing and Access not knowing because refresing the link will also refresh the info Access stores regarding the schema.  The con is that refreshing the links takes a little time on startup.

One thing you might also try is to open a hidden form that links to a recordset containing a single record and leaving that form open throughout the session.  This keeps the connection with the server "open" so Access doesn't have to keep opening and closing it when you run queries.  It does take an extra thread though.  I have never used this with a non-Jet/ACE database so I may be wrong about the effect it has.

Robbe,

One thing about this is that the tables in question are pretty hefty, so they'd have to be queried beforehand to make them manageable.  Not sure what my options are in that regard (if I can talk the mgr into getting me permissions etc).

Noted.  Given time, I'll try them side by side.  But the "given time" part is the operative phrase.

Access can be a real trial to work with in a situation such as the one I'm in but I can see why a developer would appreciate it.  I do, but I'm also squarely up against its limitations (that's a management issue) and my own (but I'm used to that) and that can be frustrating.

But I can see I've found no silver bullet.  If Access is "sensitive to network connections", and I'm dealing with many sizeable source tables, then I'm going to have issues whether the tables are linked or queried via passthroughs. 

(As an aside, I have been advised that "the network" re-prioritizes applications that access it routinely; it was suggested that Access apps tend to be the object of some prejudice in the competition for bandwidth -- pretty creepy, if you ask me.  Also, this organization just saved gazillions of dollar by switching to VOIP, so there are that many more electrons squeezed into one cubic nanometer of copper at any given moment).

Let's say I loop through a dozen tables, running a query against each -- regardless of whether the table is linked or remote.  Is there a way to establish a connection, then test it at the beginning of each successive cycle in the loop, and reestablish it if necessary?  If I could do this, than at least if the connection fails, I will know exactly where it fails and can take other steps to correct the data gathering.

What I'm unclear on is whether to use ADO or DAO and the whole idea of "best practices" where maintaining a connection is concerned.  Again, I could just grab some code - but I'd really like to undertand what's going on.

"Testing" the connection to see if it is live doesn't solve your problem.  Just because it was Ok when you submitted the query, doesn't mean it won't drop during the transfer.  If you want to track when problems arise, create a log that you update at the start and the end of each query.  You may find a pattern.  The blips happen around 11:22 every day!  You investigate and find the guy in the next cube powers up his personal microwave and it browns out your power just enough to break the network connection.  You'll also be able to isolate the longest running queries and tune them if possible.

Access is considered a band width hog because people who don't know any better bind forms directly to tables or to queries without where clauses.  Access responds immediately with the first few records so the developer doesn't see a problem, and then the connection just sits there in the background sucking data over the lan until every last bit resides locally.  You might also investigate using stored procedures to create the temp tables on the server and then when the temp tables are done, transferring the data either with Access queries or some batch method.

I am told by people who know that ADO is faster than DAO for non-Jet/ACE databases.  But if your network isn't up to snuff, you may not see much improvement.  DAO/ADO would only matter if you were processing recordsets in code.  My understanding of your process is that you are just running queries and storing the data locally.  At that point, the data is Jet/ACE which has a definite bias toward its native DAO.

actually, the "legacy" method (inherited) queries a linked table and puts the results in a local table line-by-line, using ADO.  I thought I could best that, and in some cases can, but but apparently not all cases.

I thought "testing" the connection beforehand might prevent these occasional episodes when an access query (actually a series of them) which contain a connection string as part of their definitions, get completely blown by without ever making a connection. i.e. if I could test at the beginning of the query sequence, and found no connection, I could create one.  If the queries still act like there's no connection, then clearly the method should either be perfected or abandoned in the interest of expediency.

[edit]

this "problem" of queries getting "blown by", which I referred to twice, appears to be a phantom issue.  Apparently I was juggling too many methods while parrying with management etc. and neglected to...well, let's just say the process wasn't ready for prime-time then.  But it's all good now.

[edit] 

I thought I'd post up the results of the side-by-side I did.  This is with A2007 (2002-2003 format, if that matters) pulling data from Orca of unknown vintage, probably on a room-sized mainframe in Armonk:

Import Method Comparison -- from Oracle Tables
	ADO Recordset Query		Access Queries
			saved		dao @ runtime
	Linked Tables		Linked Tables		Unlinked Tables
	records	seconds	records	seconds	records	seconds
totals	857001	793	857213	595	856967	621
secs/recs*10000	9.25		6.94		7.25
total time, min	13.22		9.92		10.35

a note on the queries:

the "ADO Recordset" uses Oracle functions, so I presume it's a pass-through.  Both the saved Access query and the DAO qdf @ runtime use Access syntax.

Thanks for sharing your results.  In summary, it is always faster to use a query to do the work rather than opening a recordset and looping through it.  Using linked tables saves some time because Access caches information about linked tables and so doesn't need to request it from the server each time a query runs.

that's actually a very concise summary.

Thank you.  It is a pity my teachers and professors never appreciated my minimalist style.

their loss.  what do they know, anyway?