Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ODBC Problem From Access97 to SQL 6.5

Posted on 1998-02-12
4
Medium Priority
?
373 Views
Last Modified: 2010-05-18
I continually receive ODBC Error 3146 in an Access97 app linked to SQL 6.5. I recently had to reinstall SQL after a struture change. I am afraid I missed some configuration option. I get this error when running INSERT INTO state ments to local temporay tables within the Access App.
DB - 4GB

The primary table has 1.4 million rows. 8 of 27 fileds had to be indexed.

256 MB Ram, 2Ppro, 16GB raid 5. 100baseT connections

0
Comment
Question by:shoebob
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 1

Expert Comment

by:kuk010998
ID: 1090245
"ODBC Error 3146" is insufficient info. Go to debug mode after the error and list the Errors Collection: Ctrl-G to get the immediate window, for i=0 to Errors.Count-1:? Errors(i).Number,Errors(i).Description:next - Errors(0) usually contains the interesting stuff.
0
 

Accepted Solution

by:
KirkGray earned 200 total points
ID: 1090246
I Know of an Access - SQL Server Problem that involves The ANSI standard NULL comparision syntax.

The problem is that Access Tests the previous state of an updated
record (Optimistic Locking) to see if the data has changed.  and if it has you get an error.

The BUG is in that if a column is null during this comparision. Access tests Like So:

AField = NULL

SQL Server Will not report an error if you do this, but it will return incorrect results.  ie (afield = NULL) = False even when afield is null.

The correct ANSI syntax is:

afield IS NULL, but I have found no way to force Access to do this.

You will know if this is the problem if only records containing null fields cause the error.

Hope this helps,

    Kirk.

 


0
 

Author Comment

by:shoebob
ID: 1090247
I still feel the problem may lie in a configuration option. I'll look at the lock settings. I have a feeling it is something in indexing. I don't think it's a NULL problem because all valuse are defulated to '0'. Thanks
0
 

Author Comment

by:shoebob
ID: 1090248
Kuk - here are the errors
 3265
jeterr35.hlp
 5003265
DAO.Errors
Item not found in this collection.

Could this be a DAO problem? I run the query via a module with DAO.

The error description does not make sense. I can do this at certain times and with other Indexing structures.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

661 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question