ODBC Problem From Access97 to SQL 6.5

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

shoebobAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kuk010998Commented:
"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
KirkGrayCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
shoebobAuthor Commented:
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
shoebobAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.