VB crashes when performing COUNT query.

Hi there,

I'm currently writing an application which allows users to search and browse a database of 300,000 records at various levels.

Prior to running the main query, a sub-query is run which will return the number of records that will be retrieved in the following query.  This works fine for most of the time but when peforming the following COUNT query the system completely crashes:

Set l_countsnap = g_db.CreateSnapshot("SELECT COUNT(*) AS ALIAS FROM CAMISPMI WHERE [SURNAME] = 'ROBERTS' AND [FORENAME1] LIKE 'J*'")

My question is this;  does anyone know how to get round this problem, or alternatively is there a fast way to retrieve the number of records that the subsequent query will return.  Using the recordcount function is not really an option as this is way too slow.

Thanks for any help

Pete

P.S. When I say the application crashes I mean I get the error message saying 'An error has occurred in your application blah blah blah!!!"

LVL 2
peterwestAsked:
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.

peterwestAuthor Commented:
Edited text of question
0
JediCommented:
What DB are you using ?

0
JediCommented:
If you step through the code , is the "crash" on the create snapshot line ?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

peterwestAuthor Commented:
Hi,

Sorry for my lack of detail; I'll try to explain in a little more detail.

Firstly, the error is being caused by the line which creates the snapshot and the module which is causing the problem is MSAJT200.DLL.  The database I'm using is in Access 2 format.

I've tried performing the same query in Access and it generates the same error message; I therefore tried it on another machine under Access and it worked fine.  I've traced the problem back to the version of MSAJT200 - could this be because I have installed the Compatability Layer???

I tried copying an older version of MSAJT200 onto my machine and now the query works under Access, but if I try it under VB it still crashes but reports the error is in MSAJT112.DLL.

Hope this clears a few things up.....

Thanks

Pete

0
JediCommented:
Are you using VB3 or VB4 ?

Are you developing a 16-bit application ?

0
peterwestAuthor Commented:
Hi,

I'm developing a 16-bit application using VB3.

Thanks

0
JediCommented:
It's a cruel world for VB3 and Jet Engine developers !

The Service Pack for Access 2.0 is worth trying to make sure your Jet Engine version is consistent with all dlls.

availible at Microsoft

I've been in this situation, not the same Error but a "unexplained" crash ,and the Service pack sorted it out for me.

Hope it helps

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
peterwestAuthor Commented:
Hi there,

I actually managed to resolve this problem in the end by simply re-installing the Compatability Layer.

However, I had the same problem on a second machine and tried your answer and this worked too - the points are all yours....thanks....

pete

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
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.