We help IT Professionals succeed at work.

MSAccess ODBC Bizarre Problem

andymurd
andymurd asked
on
I am using ODBC to read the Northwind sample database via MSAccess 2000 and a bizarre problem occurs, but only when ODBC tracing is turned off!

Those tables which have primary keys based on string fields (e.g. Customers) report "memory allocation failed" when executing a select statement such as the following:

select * from Customers where CustomerID > ?

Some testing has shown that I can read the data OK from an exact copy of the table but not the original Northwind version. Its also really odd that it works with ODBC tracing turned on.

I have all the patches for MDAC 2.5, VC++ 6.0 and Windows 2000.

Does anybody have any suggestions?

    Andy.
Comment
Watch Question

AxterSenior Software Engineer

Commented:
Did you make sure that the Northwind sample you're using is a MSAccess 2000 version.
Just because it came with 2000, doesn't mean it's been upgraded to the 2000 version.  MS has done this with other software packages, where they failed to upgrade their sample code or documents.
CERTIFIED EXPERT
Author of the Year 2009
Commented:
>> select * from Customers where CustomerID > ?

This looks like you are using a parameterized query.  I have heard reports of strange failures with this.

Do you get the same effect if you just use a literal?

  SELECT * FROM Customers WHERE CustomerID > 'SMITH'

-- Dan

Author

Commented:
Axter, MSAccess 2000 does not complain that it needs to modify the sample DB so AFAIK its the right version.

Dan, you're right that its the parameter binding that the problem, using a literal it goes through OK. I'll try messing around with the SQLBindParameter call.

    Andy.
CERTIFIED EXPERT
Author of the Year 2009

Commented:
I can't find the Q, but one person claimed that they needed to actually change the MFC source code to make parameterized query work.

I say, what's the point?  Just build the query using existing tools:

CString s; // or use string or char* or whatever
s.Format("SELECT * FROM Customers WHERE CustomerID > '%s'", (LPCSTR) sName );

-- Dan
AxterSenior Software Engineer

Commented:
>>Axter, MSAccess 2000 does not complain that it needs to
>>modify the sample DB so AFAIK its the right version.
I would use the save-as button, and save it to the same name, and make sure the version is correct in the type file field.

Author

Commented:
The problem was that when I bound the parameter, the variable that contained the parameter size/precision went out of scope before the query was executed. Pretty bizarre behaviour though.

DanRollins was closest so he gets the points.
CERTIFIED EXPERT
Author of the Year 2009

Commented:
Hi andymurd,
I believe that you think you were doing me some sort of favor by awarding me a C.  But that is a serious insult.  It screws up my answer history and my "GPA"

I suggest that in the future, you either delete a question that is not answered or be "generous" because the difference between an "A" and a "C" costs you nothing.

-- Dan

Explore More ContentExplore courses, solutions, and other research materials related to this topic.