Novice Mistakes that could be crashing Access

iheartvba

Hi guys,

Thanks to a lot of luck and help from you great people, I am on the verge of completing my first Database/Application.

but at the moment the Jet Engine back end is not able to handle the load

here are some specs re my database:
Back End: Access -> Jet Engine
Front Ends: Access -> Jet Engine
usually not more than 2 users accessing the database and the back end is stored on one of the users computers which is quite a new computer.

Here are some structural mistakes I believe that could be causing the problem:

1. Whenever I add a new record, I use ADO to connect to an entire table as a recordset, I would think that would be putting unnecessary load on the Jet back end, what is the most streamlined way to add a new record to a recordset.

2. I have external query objects (in the front ends), will external query objects put more load on my front end then SQL strings in VBA would?

Thank You Very Much

dima69

Quote:

Originally Posted by iheartvba

Hi guys,

Thanks to a lot of luck and help from you great people, I am on the verge of completing my first Database/Application.

but at the moment the Jet Engine back end is not able to handle the load

here are some specs re my database:
Back End: Access -> Jet Engine
Front Ends: Access -> Jet Engine
usually not more than 2 users accessing the database and the back end is stored on one of the users computers which is quite a new computer.

Here are some structural mistakes I believe that could be causing the problem:

1. Whenever I add a new record, I use ADO to connect to an entire table as a recordset, I would think that would be putting unnecessary load on the Jet back end, what is the most streamlined way to add a new record to a recordset.

2. I have external query objects (in the front ends), will external query objects put more load on my front end then SQL strings in VBA would?

Thank You Very Much

I am not sure what the problem is, however I think you do take one thing wrong.
Access database Back End never uses Jet Engine or anything else - it's just a file server. All data is processed at the Front End. So when you have a big table and you run a query to select a few records, the whole table must be transfered to the Front End anyway.

NeoPa

Opening a table will generally not load all the data. This is a non-issue. Let Access handle it.
If you mean QueryDefs in your front end, then it's actually better to use them than a VBA SQL string. The former are 'optimised' on first use. A SQL string needs to have optimisation guesses done every time it's run. The Jet Engine will work quite happily on the Back End database.

PS. I don't like to contradict other Experts (so apologies for that Dima) but I think I'm right on this occasion. I would be happy to be proved wrong if there's something I need to learn. I may ask some other Experts to have a look in to check.

NeoPa

The Back End Access DB & Jet Engine discussion is continuing, but not in here please.
This thread is now dedicated only to the original topic.

Denburt

Quote:

Originally Posted by iheartvba

Hi guys,

Thanks to a lot of luck and help from you great people, I am on the verge of completing my first Database/Application.

but at the moment the Jet Engine back end is not able to handle the load

here are some specs re my database:
Back End: Access -> Jet Engine
Front Ends: Access -> Jet Engine
usually not more than 2 users accessing the database and the back end is stored on one of the users computers which is quite a new computer.

Here are some structural mistakes I believe that could be causing the problem:

1. Whenever I add a new record, I use ADO to connect to an entire table as a recordset, I would think that would be putting unnecessary load on the Jet back end, what is the most streamlined way to add a new record to a recordset.

2. I have external query objects (in the front ends), will external query objects put more load on my front end then SQL strings in VBA would?

Thank You Very Much

I think the guys got a little off the beaten path here (no offense).

1. noneed to use a an ado recordset when a simple insert statement or an append query would do the job. This would be much more efficient for adding a record to a table. This would also lead us to question 2.

2. Very good question, I would think a saved query would be the best bet for most circumstances, although this is something I would prefer to back up with documentation or a test with some Extremely large additions.

Novice Mistakes that could be crashing Access

re: Novice Mistakes that could be crashing Access

re: Novice Mistakes that could be crashing Access

re: Novice Mistakes that could be crashing Access

re: Novice Mistakes that could be crashing Access

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/other

/bytes/about