Link to home
Start Free TrialLog in
Avatar of rjander77
rjander77

asked on

Odd Access 2003 / SQLServer 2000 / ODBC Problem

I'm not sure whether this should go here or in the SQLServer area, but since we're using both, I'll post it here.

We're using Access 2003 to link to SQLServer 2000 tables via an ODBC connection.

When adding the connection, all tests complete successfully.  We can then link in the tables correctly.  I can open any one of the four tables in question and view all the data my little heart desires.

The trouble comes when we want to query the data in the SQLServer tables.  If we run the query with no criteria, it shows the contents of the whole table just fine.  If we set the criteria, then it produces an "ODBC call failed" error.  This happens within about 5 seconds of executing the query, so I don't think it has to do with any timeout settings.

After the error occurs, no ODBC action of any sort can get through.  It just reproduces the call failed error.  The connection has to be deleted and set up again, or the tables have to be deleted and relinked.

Any ideas?


500 points.
Avatar of Jim P.
Jim P.
Flag of United States of America image

>>  "ODBC call failed" error.

Any error number?

In the ODBC call:
Do you have the ANSI warnings and nulls boxes checked?
What about perform translation for charcater data?

These are just the quick off the top to glance at.
Avatar of MikeToole
Are the linked tables updatable? Can you add records via the table datagrid?
Avatar of rjander77
rjander77

ASKER

The linked tables are not updatable.  We intentionally do not want our users to change the data as it is given to us by the client.  We are strictly querying and producing reports from the data.  Users have Public and Datareader access.

The "Use ANSI quoted identifiers", "Use ANSI nulls, paddings and warnings" and "Perform translation for character data" boxes are checked.

The error message is:  "Fatal error 823 occured at [Today's date and time] (21)."


Some background info:

We have a copy of this static database from the client.  When the client sends an update, they give us the entire database.  We detach the existing database, back it up, and reattach the database using the new file from the client.  This worked at our main location, where I am located.  The problem came after I sent the exact same file from the client to our satellite office, 4 states away.  The detach / reattach worked fine for them.  The only problem is what is mentioned above.  I cannot remote in to their network as both our networks are entirely self-contained with no Internet access (due to the client being the Federal government, and their data being confidential).

Thanks for the quick responses!
>> The "Use ANSI quoted identifiers", "Use ANSI nulls, paddings and warnings" and "Perform translation f....

Try unchecking all three and see what happens.
Same results.  I'm confused as to why we can query the data here, but the manager at the satellite office cannot.  It's the same database.

When I check / uncheck the ANSI and translation options, it makes no difference on my end.  Unfortunately, not on his end either.

It now appears that it may be just one of the tables that produces this error for him.  I would think that the data may be corrupt, but again, it works with the same set of data here.  It's literally a copy / paste of our version.

?
Same Windows and Access versions/sp?
Same MDAC on all?
Same user ID's?
What's the authentication method? (Win Authent or mixed mode)
When building the ODBC Call what is the client configuration button say? (Named Pipes or TCP/IP)
How are you moving the data? CD/FTP/Carrier Pigeon
Can you have him run a DBCC CHECKDB on the DB? (I think he'll have to be able to get the DB into single user mode.)
Wow.  Okay, here goes.

Same version of Windows and Access.  Not sure about the service packs.

What is a MDAC?

Not the same user IDs.  He has his own account on his copy of SQLServer.  I just asked him to completely delete his SQLServer account, delete all his linked tables and ODBC connections on the workstation attempting to perform the query, and start from scratch.  I will post back here shortly with the results.

SQLServer authentication.

How do I find out about the named pipes / TCP/IP client configuration?

I put the copy of the database on an external hard drive and overnighted it to him via FedEx.  Lots of bubble wrap, and no damage to the drive.

I don't know anything about running a DBCC CHECKDB on the DB.  He is the only user authorized to use this data, so single user mode is not a problem.

Thanks!
Okay, two new things.

Deleting and rebuilding the user's account and ODBC connection did nothing to remedy the problem.

The user reported a new error message that appears after attempting to run the query multiple times:

"Communication link failure #0".  This only appears after multiple attempts and multiple "Call failed" messages.
ASKER CERTIFIED SOLUTION
Avatar of Jim P.
Jim P.
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Nope, that's me!  I know he has connectivity because he can access other files on the network with no problem.  He's left for the day (after 5 in his time zone), so I'll pick up with this tomorrow.

First thing in the morning, he's going to detach and then delete the database, delete his user account, delete the workstation ODBC connection, delete his linked tables, and do a rain dance.  Then he's going to try the whole process over again.

Then when it doesn't work, I'll get me arse on an airplane and go frown at it in person.

I would up this to a 1000 points if I could!

I'll check the other stuff you mentioned above with him tomorrow.

I really appreciate your help!
About the MDAC - the link you sent me said that it contains the same components as SP2.  We're running SP2 here; he's not.  I'm sending him SP2 on tonight's FedEx shipment.  He'll install it in the morning.  I'll post back with the results.

Thanks again!
When I say connectivity, he may be able to connect to network resources, but have other problems. Such as,
Chattering NIC (his or someone on the network)
DNS Resolution
End of a long network
The SQL server is pegged
Dropping his DHCP lease
The authent to his AD is timing out

Just a few of the things to look at. Also how many columns in the SQL table?
And if you do the Access as an ADP do you still have the same problem?
>When I say connectivity, he may be able to connect to network resources, but have other problems. Such as,
>Chattering NIC (his or someone on the network)
He's tried it from a couple computers.  If it was someone else on the network, how do we find out who?
>DNS Resolution
If you just mean having the right DNS server address, then it's fine.  If you mean something else, then I'm ignorant.
>End of a long network
There's about 10 other computers on this network.  Shouldn't be a problem, I would think.
>The SQL server is pegged
Not sure what you mean.
>Dropping his DHCP lease
Static IP
>The authent to his AD is timing out
Not the problem, as this happens even after a reboot and a fresh login.

>Just a few of the things to look at. Also how many columns in the SQL table?
Table 1: 12 columns
Table 2: 8 columns
Table 3: 16 columns
Table 4: 6 columns

Table 2 is the most problematic.

>And if you do the Access as an ADP do you still have the same problem?
How do I do this?

I'm sorry for my ignorance, but very appreciative of the quick and patient help.  Thank you.
>>He's tried it from a couple computers.  If it was someone else on the network, how do we find out who?
You go into the switch on his network and look for errors or any port that has a high number of packets.

>>>The SQL server is pegged
>>Not sure what you mean.

That the SQL server has enough CPU, memory, NIC that it has resources avalable.

>>>>DNS Resolution
>>If you just mean having the right DNS server

Try building the ODBC call using the server's IP address.

>> > Access as an ADP How do I do this?
You can create a Microsoft Access project (Microsoft Access project: An Access file that connects to a Microsoft SQL Server database and is used to create client/server applications. A project file doesn't contain any data or data-definition-based objects such as tables and views.) and connect it to a Microsoft SQL Server  database.

You would File--> New Database from the menu Select Project(Exixting Data) and connect the DB to the database.  Essentially it's a pure VB front-end to SQL Server with the look and feel of Access. Then you can import the forms and reports from the existing DB.

>> > Access as an ADP How do I do this?

I just checked, you can actuall do it with the upsizing wizard. Tools --> Database Utilities --> Upsizing Wizard.
SP2 didn't work.

FYI, the Upsizing Wizard converts and Access DB to a SQLServer DB.

We created a new project using existing data successfully.  We can see the tables.  How do we run queries on the data?  We can only do stored procedures.  When I try to get into the SQL view of the procedure, I get a message that there is a syntax error near the line "From".  It won't let me in.  That was here, not at the manager's location.

As far as importing goes, how can we import the queries from the original Access DB as well as the forms and reports?  There doesn't appear to be an option.

The resources are fine for the server.  There don't appear to be any networking issues that we could find.

Thanks.
Okay, - here's the solution:

Have the firm's partner freak out because members of Congress are coming for an inspection in 72 hours.

Fly your network admin (me) 4 states away on 3 hours' flight notice to look at the problem in person.

Have him frown at the problematic computer in person.

Have him delete anything he doesn't like, which when in a foul mood is a lot (30 GB of crap).

Start the whole process from scratch.

Have it work immediately.

Keep him out there for an extra day to putz around on stuff because it's more cost effective than changing the flight back to a convenient time.


I am so sorry to have wasted your time on this.  I appreciate your help, and gladly award you 500 points.  Thanks again.
Glad to be of assistance.  What you need to do is put a policy on the machine that restricts the avail disk space and take away his admin rights to the machine.