Linking from Access

[NeoPa]

My setup

I have a SQL Server (2000) back end database, which I access via Access (among other methods). I use an ODBC link and Windows authentication. As an administrator I am mapped onto sa level access. I have full rights to manage everything via this link.

My SQL Server is available across the LAN (100Mbps link), and for the most part appears to be behaving as expected. It runs on a two processor (2 chips) server running Windows 2003 Server and has one processor dedicated to its use.

SQL Server 2000 software is installed onto the C drive (17GB 40% free), but the data is stored on the D drive (275GB 11% free).

The database gets weekly maintenance (standard maintenance and integrity checking jobs, as well as backing up the database while shrinking the log).

The current sizes of my files are :

OpalTHist.Mdf - 26,844,672 KB
OpalTHist.Ldf -  6,307,840 KB

My Problem

Generally none, but every now and again, I find that writing data to a SQL Server based table (I'm trying to write 703 records with only four fields per record) fails with the error :
ODBC--Insert on a linked table 'tblCustList' failed.
[Microsoft][ODBC SQL Server Driver]Timeout expired (#0)

When it fails though, it fails consistently. It does no good to try it again. Even stopping and restarting the SQL services doesn't help. Just to be clear, this is a process which has been working consistently without any problems for some years.

Comment

It happened before for critical, once-a-month data, and I had to arrange an elaborate system whereby the data was loaded directly into an Access database which was made available directly to the server that runs SQL Server. This was then accessed via DTS. This works reliably, but is a bit of a palaver, and would be totally impractical for the table I'm currently having difficulties with.

I haven't done much design work on my SQL Server recently, so feel free to mention even quite basic suggestions. It could easily be something I haven't considered.

[Denburt]

1 possibility would be to open a connection to SQL server and avoid using the linked table altogether. You might also create a stored procedure in SQL Server then insert it from that end. Maybe you could try to split up the records so only a few go at a time.

[ADezii]

You can modify the ODBC QueryTimeout Configuration Option in the System Registry. You'll find it at the

\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\ODBC\

Registry Key. This Key provides the Value for Query-Processing Timeouts is Seconds with the Default = 60. If you have a Value of 0, which your Timeout Error may/may not seem to indicate ([ODBC SQL Server Driver]Timeout expired (#0)]), the Query will never Timeout and will wait forever for data which could conceivably be causing problems to your System. I also agree with Denburt in that you should at least try to drop the Linkage in favor of programmaticall y accessing the SQL Server DB.

[Krandor]

I agree with Denburt.

Try to insert the data into a table with no constraints, no referential integrity and no triggers. If it still doesn't work, you will have at least eliminated a major possiblity.

[NeoPa]

Thanks for all the hints guys :) I'm pondering some of them.

I should say that I'm coming around to the opinion that the 30GB of space left on the data drive is probably the heart of the issue, but I'm still exploring all avenues.

I'll try to respond to each idea raised. All ideas, however received, come with my gratitude, for your time if nothing else. All help is appreciated.

A connection rather than a link.
I can't see what that adds. It would mean a redesign of acres of code. I don't want to go there without understanding why it may be considered a positive move. I must admit I only see it forcing me to work a way that I don't want to, but there may be something I'm missing.

SQL Stored Procedure.
As the SQL Server has no access to the data I'm starting with I'm not sure how I might do this.

Splitting up the data.
This I've done. I was hoping to get more from this, but it took the first batch of data ok, then when I dropped the next batch in it failed in a similar way and locked up the link. I was hoping at least for a quick fix that would allow me to run the job and take the pressure off.

Definitely worth a try, but no real joy :(

ODBC Query Timeout value.
I'm at home now, so not in a position to test this atm. Sounds very promising though, at least to get past this specific instance, if not a fundamental fix of the underlying problem.

Drop Constraints, Triggers etc.
This is good thinking.
As it happens, I don't believe there are any for this table, but that's certainly a good approach.

Again, thanks to all, and I'm interested in any discussions arising from anything I've said. It's often good to hear other opinions, even (or especially) when they differ from your own.

[MrDeej]

Have you tried to use ADO programming to write to the table?

[NeoPa]

Hi MrDeej.

I would see this very much as a connection. See my comment below on that general idea. I'm still open to hearing how it may change things.

[MrDeej]

I have also experienced several connection errors.

When using querys the whole operation stops, but when using ADO only 1 row stop and this is more easly to make error handling.

[NeoPa]

Ah. That makes some sense.

Unfortunately, I think the drawbacks still outweigh the benefits for long-term planning. It may be worth using to get over this particular hurdle though.

The drawbacks, as I see them, to connection based processing of data are :

1. In my experience, SQL code runs many times more quickly and efficiently than processing through a dataset. This is not connection based code specific as far as I'm aware (I've also seen it's effects when using local tables), but would obviously apply equally to those.
2. Maintainability .
   I try to keep my projects as visible as possible. If I'm using query based code then I like to maintain the SQL in a compiled QueryDef object rather than as a string in code. Equally, if I ever need to update SQL on the fly, I invariably start with a known set in a QueryDef which I will extract, modify and execute. This way it is much easier to follow what is going on (in my view at least).
3. It may be possible, but I'm unfamiliar with how I could use an action query using a connection.

Your explanation helped my understanding anyway. Thanks :)

[MrDeej]

No problem

Would have been nice to see how you have programmed your QueryDef object though, sound interresting:)

[Denburt]

Are you familiar with connection pooling I found some very interesting information here that you may look into I think it may pertain to the issue you are having. I am not sure how much of an effect if any it might have for your circumstance but I do know that MS Access has it turned off by default.

http://msdn.microsoft.com/en-us/library/ms810829.aspx

[NeoPa]

Thanks for that Den. I don't know how much this will effect things as, when I looked at it I immediately suffered from the Late on Friday effect. It meant nothing whatsoever to me.

I'll have another look when my brain's a little better rested and freed from the stresses of a fairly heavy day. I'm quite interested to see what it can offer.

[Krandor]

Let me see if I understand you correctly.

You have data in your Access database that you push to an Access table which is then linked to SQL Server table. This table is the one that is giving you headaches.

I’ve been doing Access/SQL Server combinations since ’94 (yes I’m ancient). In all that time, I have seen very few instances where it was better to do the processing on the Access side rather than the SQL Server side.

SQL Server is generally orders of magnitude faster in manipulating data than Access. You can create complicated stored procedures which are not really available using Access queries or VBA and SQL Server can handle tables with much larger numbers of records than can Access without choking.

I usually use Access only as the front end where the user enters or manipulates the data. That data (or the changes) are then posted to the SQL Server database either through stored procedures or by directly affecting the tables.

This I accomplish through ADO connections only.

Access has multi user issues when tables are tied directly to forms/screens. The first person to a screen can do edits, the second (or later) cannot.

And finally, Access apps that store the data in Access tend to start slowing down a lot after about 10 concurrent users. Store the data (and manipulate it) in SQL Server and that problem goes away.

Sorry for the long rant. I don’t know if this helps you, but it could help someone later who stumbles across this thread.

[NeoPa]

Thanks for this Krandor.

Let me see if I understand you correctly.

You have data in your Access database that you push to an Access table which is then linked to SQL Server table. This table is the one that is giving you headaches.

Fundamentally yes. Though my original Access data grab is from a separate ODBC link.

I’ve been doing Access/SQL Server combinations since ’94 (yes I’m ancient). In all that time, I have seen very few instances where it was better to do the processing on the Access side rather than the SQL Server side.

Well, you won't be able to say that any more (I suppose technically one extra instance doesn't stop it still being very few) :D

In my case, it now seems clear that the SQL server was suffering from space restriction problems.

In essence though, what my process is doing is determining the list of accounts that need to be processed by the generally more powerful SQL Server. It was so unwell last week though, that it was unable even to take the list of accounts into the table.

I was ideally hoping for some SQL Server tips on which DBCC commands may help either to determine problems or to fix them (not from the Access experts so much of course).

I usually use Access only as the front end where the user enters or manipulates the data. That data (or the changes) are then posted to the SQL Server database either through stored procedures or by directly affecting the tables.

This I accomplish through ADO connections only.

Apart from using connections to move the data (I explain earlier why I'm not a great fan of that approach), this is fundamentally how I'm working too.

Access has multi user issues when tables are tied directly to forms/screens. The first person to a screen can do edits, the second (or later) cannot.

And finally, Access apps that store the data in Access tend to start slowing down a lot after about 10 concurrent users. Store the data (and manipulate it) in SQL Server and that problem goes away.

I haven't been doing even Access work that long (late nineties properly) and SQL Server only since the mid noughties (4 or 5 years).

Much of my design started before SQL Server even came into the picture for me, and I wasn't about to fix a design that was working quite well thank you. I work in a commercial environment anyway, so even if I'd wanted to, I doubt it would have been sanctioned. That all said, I'm sure if I were to do it again I would work along lines more similar to what you suggest. I get that the points make sense (although they hardly effect my situation at all as it happens).

It should be pointed out though, that the delays you refer to only effect databases which store the data internally. A similar setup to a SQL Server linked table, but with Access as a back-end instead, doesn't suffer from these problems. Don't get me wrong. SQL Server is a faster and more powerful engine, but these specific problems are design related in Access rather than engine related.

Sorry for the long rant. I don’t know if this helps you, but it could help someone later who stumbles across this thread.

Not at all. As you quite rightly say, even if it doesn't pertain directly to my problem, it may well help others looking for answers in a similar area.

PS. Just reread the last paragraph and wanted to clarify I was not trying to suggest this was not on topic. It was a perfectly valid attempt to assist with the problem, and shed light on matters surrounding it.