speed problems accessing large MS Access database

  • Thread starter Thread starter Patrick Fryer
  • Start date Start date
P

Patrick Fryer

Hi,

I'm doing some work on a project which involves accessing a large
Access database (about 850Mb)

The problem is when I'm doing an update/insert into the database the
oleDbCommand.executeNonQuery() method seems to return before Access has
committed the change - This means that a display I have on screen
which reads back what has just been 'inserted' doesn't work
correctly.

At first the problem was only intermittent but as the database has
grown it seems to happen all the time. It does work okay if I step
through the program in Visual Studio (thus introducing an artificial
delay). Does anyone know a way around this? Someway for the code to
block until the operation has been committed? I can't seem to find
info about this online. If I make the thread sleep for 500 MS after
performing the insert it's okay as well?

Is there a connection string parameter or something I can use? Our
customer wants to stick with MS Access for now although they want to
upgrade to SQL Server later.

Thanks. Patrick.
 
Patrick,

Just a thought, that you might switch to MSDE which is free and it should
work for you - access isn't really good at handling large database and/or
many users.
The only drawback for you might be database size restriction of 2Gb for MSDE
if I remember correctly.
Sorry of not providing you a direct solution to your problem...
 
Sahil Malik said:
Switch to MSDE, it's a free stripped down SQL Server. There's really no
good
reason for using Access in that situation.
Click to expand...

Customers demand could be a good reason :-)
 
Yeah, when you go to a doctor and the doc says "You need a bypass" and you
insist "No give me an asprin" .. Well in that case, the patient will be
dead, so it won't matter. In computer consultants case, the patient will
come back to bitch about it.

Such is life :)

I wish I could say - "I don't teach you how to do your business and you
don't teach me how to program".

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
 
Hello, I need immediate help with this problem.
From C# code I am trying to call a stored procedure, which is in a
Click to expand...
oracle 8i db.





First of all here is the definition of the function(oracle function).

//==================================================================

FUNCTION INSERT_COMMENTS( iv_acc_id IN VARCHAR2,

iv_user_id IN VARCHAR2,

iv_comment IN VARCHAR2 )

RETURN BOOLEAN;

//==================================================================









My Catch catches this error code coming for Oracle side.

//==================================================================

:In insertComments2 method => ORA-06550: line 1, column 7:

PLS-00306: wrong number or types of arguments in call to
'INSERT_COMMENTS'

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

//==================================================================











And here is my C# code that tries to make the call to the oracle.

//==================================================================

public static void insertComments2(string accID)

{

try

{

string useridv = "DAYBREAK";

string STR_COMMMENT = "Wellcome letter has been
generated";


//==============================================================================

//create an instance of the command object
giving the procedure name

OleDbCommand sqlCmd2 = new
OleDbCommand("ACCP50.INSERT_COMMENTS",myConn) ;



// Define the command type u r executing as a
Stored Procedure.

sqlCmd2.CommandType =
CommandType.StoredProcedure ;




sqlCmd2.Parameters.Add("iv_acc_id",OleDbType.VarChar,20);

sqlCmd2.Parameters["iv_acc_id"].Direction =
ParameterDirection.Input ;




sqlCmd2.Parameters.Add("iv_user_id",OleDbType.VarChar, 20);

sqlCmd2.Parameters["iv_user_id"].Direction =
ParameterDirection.Input ;




sqlCmd2.Parameters.Add("iv_comment",OleDbType.VarChar,40);

sqlCmd2.Parameters["iv_comment"].Direction =
ParameterDirection.Input ;



sqlCmd2.Parameters.Add("RETURN
BOOLEAN",OleDbType.Boolean);

sqlCmd2.Parameters["RETURN BOOLEAN"].Direction
= ParameterDirection.ReturnValue;



detailLog(accID,w);



//sqlCmd2.Parameters["RETURN
BOOLEAN"].Direction = ParameterDirection.ReturnValue;

//sqlCmd2.Parameters["RETURN BOOLEAN"];



sqlCmd2.Parameters["iv_acc_id"].Value = accID;
;



sqlCmd2.Parameters["iv_user_id"].Value =
useridv;



sqlCmd2.Parameters["iv_comment"].Value =
STR_COMMMENT;



// execute the stored procedure

sqlCmd2.ExecuteNonQuery();



// if ((string) (sqlCmd2.Parameters["RETURN
BOOLEAN"].Value.ToString()) == "true")

// detailLog(" Success. Comments has been
inserted successfully.", w);

// else

// detailLog(" FAILED. Comment insertion
failed.", w);



}

catch (Exception error)

{

detailLog("In insertComments2 method => "
+error.Message, w);

System.Console.Write(error.Message);

}

}



//==================================================================
 
Back
Top