473,322 Members | 1,401 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Linking from Access

NeoPa
32,556 Expert Mod 16PB
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 :
Expand|Select|Wrap|Line Numbers
  1. OpalTHist.Mdf - 26,844,672 KB
  2. 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.
May 7 '09 #1
13 5550
Denburt
1,356 Expert 1GB
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.
May 7 '09 #2
ADezii
8,834 Expert 8TB
You can modify the ODBC QueryTimeout Configuration Option in the System Registry. You'll find it at the
Expand|Select|Wrap|Line Numbers
  1. \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 programmatically accessing the SQL Server DB.
May 7 '09 #3
Krandor
50
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.
May 7 '09 #4
NeoPa
32,556 Expert Mod 16PB
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.
May 7 '09 #5
MrDeej
157 100+
Have you tried to use ADO programming to write to the table?
May 8 '09 #6
NeoPa
32,556 Expert Mod 16PB
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.
May 8 '09 #7
MrDeej
157 100+
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.
May 8 '09 #8
NeoPa
32,556 Expert Mod 16PB
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 :)
May 8 '09 #9
MrDeej
157 100+
No problem

Would have been nice to see how you have programmed your QueryDef object though, sound interresting:)
May 8 '09 #10
Denburt
1,356 Expert 1GB
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
May 8 '09 #11
NeoPa
32,556 Expert Mod 16PB
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.
May 8 '09 #12
Krandor
50
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.
May 8 '09 #13
NeoPa
32,556 Expert Mod 16PB
Thanks for this Krandor.
@Krandor
Fundamentally yes. Though my original Access data grab is from a separate ODBC link.
@Krandor
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).
@Krandor
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.
@Krandor
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.
@Krandor
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.
May 11 '09 #14

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: John South | last post by:
I have an Access 2000 front end that I wish to work with a SQL Server 2000 database by means of Linked tables. Do I have to use an ODBC connection to SQL Server? It seems to be the only option...
2
by: Jeff Pritchard | last post by:
Some time ago I am sure I came across something that said this was possible, though it doesn't seem to work. A client wants to replace an Access back-end with SQL Server tables. We have tried...
0
by: gasturbtec | last post by:
please help im new at access programming and i just got this project dropped in my lap because the old programmer quit. i've been doing ok so far but now i need to add code to an existing database...
18
by: Mark P | last post by:
I have a bit of vb code that uses Tables.Append to programatically link tables from Oracle and DB2 datasources. The problem I am having on some client machines is that the link will take anywhere...
2
by: Matthew Wells | last post by:
Good morning... I have an Access front end that uses SQL Server linked tables. SQL Server uses Windows authentication. I have one Windows group that all Access users are a member of. I added...
1
by: GregZ | last post by:
I have an Access 97 database that I have uploaded to the internet. I have created a DSN for it with the hosting company and can now access it using .asp code. Everything here works fine. But, I...
7
by: Salad | last post by:
I am converting an application from A97 to A2003. I have 2 tables created by another application as a Foxpro.dbf. The table has no index. The connect string in A97 is FoxPro...
0
by: hivdb2 | last post by:
Hi all, I have an app developed in Access 2000/2003 (2000 compatible). I have a routine to refresh table links. Both files are MDB files (the front end and back end). The code I use is as...
1
by: agarwasa2008 | last post by:
Hi, I have a perfect file called "Products.xls" that I link to my MS Access 2003 database and everytthing looks good in that file. When I view the same file after linking in my database the...
7
by: coolsti | last post by:
I have the task to set up an application at work,using MS Access as a front end to a MySQL database. This will be done using an appropriate ODBC driver, and linking the MySQL database to Access. ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.