By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,996 Members | 892 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,996 IT Pros & Developers. It's quick & easy.

Novice Mistakes that could be crashing Access

100+
P: 171
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
May 16 '07 #1
Share this Question
Share on Google+
4 Replies


dima69
Expert 100+
P: 181
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.
May 17 '07 #2

NeoPa
Expert Mod 15k+
P: 31,349
  1. Opening a table will generally not load all the data. This is a non-issue. Let Access handle it.
  2. 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.
May 26 '07 #3

NeoPa
Expert Mod 15k+
P: 31,349
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.
May 27 '07 #4

Denburt
Expert 100+
P: 1,356
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.
May 28 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.