Ms Access Requery

[inlandchris]

Hi all,
I have a program in C++ that uses MS Access database. When I need to lookup a database entry, I requery the database. When I requery the database a lot, the database gets corrupted, why. Here are some details below:
m_pApbLogSet->m_MyParam = 1;
m_pApbLogSet->m_nParams = 1;
m_pApbLogSet->m_strFilter = _T("Card = ?");
m_pApbLogSet->m_CardParam = CardNumber;
try
{
m_pApbLogSet->Requery();
}
catch(CDBException * e)
{
e->Delete();
}
if(m_pApbLogSet->IsBOF() && m_pApbLogSet->IsEOF())
{
return; //not found
// or sometimes I add a new record because the above was not found...other code in the program (m_pAPbLogSet->AddNew(); )
}
I set the filter to a card number (in this case) and run the Requery(). After doing this for thousands of times each day, the database get corrupted and you have to compact and repair it.
I have seen this many times and its just annoying for the customer.
I am using MS Access 2003 and Microsoft Visual Studio 2005 and using RecordSets
m_pApbLogSet is dynamically allocated memory to a MS Access Table.
Has anybody had problems like corrupted database base before?
Oh, on SQL, there is NO problems the with above code

Thanks in advance
Craig

 

[Old Man Devin]

I know nothing at all about C++, but I do know that Access in general can get corrupted simply by using it a lot, so thousands of requeries could certainly be slowly breaking things just because of Access not being good at heavy duty activities!

 

[jdraw]

Is this a single user or multiuser database?
Is it LAN based set up --certainly there are corruption reports of LAN/WAN/networks where any network errors are common.

Have you tried looking into C++ forum(s) to see if anyone with similar set up has corruption problems?
Not many people, from my experience, who use JET/ACE with C++ (at least not in Access oriented forums).

I'd try Google with "corruption C++ and msaccess database"
Good luck.

 

[inlandchris]

Thanks, did ask many forums includung Code project; nobody seems to have a clue. To bad, wish there was a replacement DB that I can use instead of Access. Yes, heavy duty! These are requeries, and my transaction table has up to 350,000 records before Access slows down. I did test until 2 million records before Access Stopped.

 

[inlandchris]

Oh, I did read that MS Access is NOT a heavy duty database and requries over 40,000 will corrupt the DB. So, I did find my answer, use another DB for heavy use. Currently, I am looking at PostgreSQL and see how easy the customer can download and install. I would prefer MS SQL but the install process is not for the timid or casual user
Thanks all

 

[Galaxiom]

Why don't you use MS SQL Server Express? It is free, and supports 10GB of data.

It is running on one engine instead of a separate engine on each client acessing the same data files.

 

[inlandchris]

Thank you for the reply. I use SQL for the advance people/business because its heavy duty but for the small business owners, SQL, even express, is very intimidating. I just took your advice and installed express on my computer and took screen shots for instructions but I came up with a dilemma, stored procedures. My SQL version software uses stored procedures so when I find an easy workaround to install them, I cant do it.
Since these users are novices, I don't dare give them access to SQL Management studio; they will screw things up for sure. The SQL only DB install would be fine but again, I have a stored procedure install problem.
Thanks

 

[Galaxiom]

Everything in SQL Server should be able to be set up by sending sql commands from a configuration front end so it should be transparent for the user.

 

[jdraw]

I agree with Galaxiom, but I would also suggest you look at some MS SQL forums. Pose questions/seek examples with MS SQL Express and stored procedures.

Don't overlook Google/Bing to investigate stored procedures and SQL EXpress.

 

[inlandchris]

Galaxion and Jdaraw,
Thank you for the advice, looks like I will research the configuration in the install, never thought about that!