473,657 Members | 2,597 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

VBA versus Query

Hello,

I have hit the "Cannot open any more databases" and "System resource
exceeded" errors. Knew this was coming, but as I got closer to finishing the
database, I hoped it wouldn't be an issue.

My DB consists of hundreds of unique pieces of information (in atomic tables
of course). However, I have many multiple queries that are required to
compile information and conduct various calculations to put it in a workable
format for reports and forms.

My posed question:

1) Should I have the "queries" execute on the forms/reports themselves using
VB code or
2) Should I rework my database design to improve effeciency

Right now a particular form may refer to a query that refers to multiple
queries and tables.

Thanks for your help!

B

--
Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200708/1

Aug 15 '07 #1
12 2099
bhipwell via AccessMonster.c om wrote:
Hello,

I have hit the "Cannot open any more databases" and "System resource
exceeded" errors. Knew this was coming, but as I got closer to finishing the
database, I hoped it wouldn't be an issue.

My DB consists of hundreds of unique pieces of information (in atomic tables
of course). However, I have many multiple queries that are required to
compile information and conduct various calculations to put it in a workable
format for reports and forms.

My posed question:

1) Should I have the "queries" execute on the forms/reports themselves using
VB code or
2) Should I rework my database design to improve effeciency

Right now a particular form may refer to a query that refers to multiple
queries and tables.

Thanks for your help!

B
You can have 32,768 objects in a A97 database. You can have 1024 open
tables open at a time. (Go to help, type Specifications)

Are you opening connections to a database
set dbs = Currentdb
or recordsets
set rst = dbs.Openrecords et
and not closing them or setting the object to nothing?
Aug 15 '07 #2
Where I am stuck right now is with a query. If I right click on it and
select design, I get "System resource exceeded." I can get it in datasheet
view and SQL, however. It is a messy query that uses other queries so I can
populate a report. (A2000)

Untangling this could be a mess. I was think I may just have the report make
queries for itself.

Any thoughts?

B

--
Message posted via http://www.accessmonster.com

Aug 15 '07 #3
Ben
On Aug 15, 8:31 am, "bhipwell via AccessMonster.c om" <u30281@uwe>
wrote:
Hello,

I have hit the "Cannot open any more databases" and "System resource
exceeded" errors. Knew this was coming, but as I got closer to finishing the
database, I hoped it wouldn't be an issue.

My DB consists of hundreds of unique pieces of information (in atomic tables
of course). However, I have many multiple queries that are required to
compile information and conduct various calculations to put it in a workable
format for reports and forms.

My posed question:

1) Should I have the "queries" execute on the forms/reports themselves using
VB code or
2) Should I rework my database design to improve effeciency

Right now a particular form may refer to a query that refers to multiple
queries and tables.

Thanks for your help!

B

--
Message posted via AccessMonster.c omhttp://www.accessmonst er.com/Uwe/Forums.aspx/databases-ms-access/2007...
I've run into that quite a few times as well. Since you already have
it almost done the easiest solution would be to replace your queries
with VBA code, as this does not really count toward your object limit.
Remember that tables and queries count more then once toward the
number limits imposed, once just to exist, and an additional count for
every active reference.

You should be able to eliminate just about all of your queries with
VBA through executing any queries or setting rowsources for report
queries.


Aug 15 '07 #4
bhipwell via AccessMonster.c om wrote:
Where I am stuck right now is with a query. If I right click on it and
select design, I get "System resource exceeded." I can get it in datasheet
view and SQL, however. It is a messy query that uses other queries so I can
populate a report. (A2000)

Untangling this could be a mess. I was think I may just have the report make
queries for itself.
I would agree it's a mess.
Any thoughts?

B
Goto google groups and search for your errors. Search in *access* group
textbox.
Aug 15 '07 #5
Ben,

As I understand it, you cannot use SQL in the control source of a field. Do
you suggest populating the text boxes on my report using VBA during an OnOpen
event?

B

--
Message posted via http://www.accessmonster.com

Aug 15 '07 #6
I'm sorry, but I don't understand what you have, or what you are trying to
accomplish, starting with "hundreds of unique pieces of information in
atomic tables." Perhaps you could clarify just a bit...

Databases are great for storing and retreiving information... they aren't
necessarily all that great for performing complex calculations. Sometimes
it's better to do the calculations in a spreadsheet, and get the data from a
database, or sometimes it's better to do those calculations in code.

If we are given something more than a vague description of what you have and
what you're trying to do, it's likely that someone could offer some useful
suggestions. As you can guess, "Can't open more databases" and "System
resource exceeded" are so generic that there isn't a specific solution
anyone can offer.

Larry Linson
Microsoft Access MVP
"bhipwell via AccessMonster.c om" <u30281@uwewrot e in message
news:76bcb99651 2ed@uwe...
Hello,

I have hit the "Cannot open any more databases" and "System resource
exceeded" errors. Knew this was coming, but as I got closer to finishing
the
database, I hoped it wouldn't be an issue.

My DB consists of hundreds of unique pieces of information (in atomic
tables
of course). However, I have many multiple queries that are required to
compile information and conduct various calculations to put it in a
workable
format for reports and forms.

My posed question:

1) Should I have the "queries" execute on the forms/reports themselves
using
VB code or
2) Should I rework my database design to improve effeciency

Right now a particular form may refer to a query that refers to multiple
queries and tables.

Thanks for your help!

B

--
Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200708/1

Aug 16 '07 #7
Larry,

Thanks for your reply. I was able to fix my problem by going back to a saved
backup and rebuilding up to the point where the error occured. By hundreds
of pieces of information, I meant fields. By atomic, I meant that I have a
bunch of smaller tables to ensure no duplication of data.

I am in the process of reworking some of my queries as they are quite over
the top with too many fields and complex calculations.

I had just never seen the "System Resource Exceeded" error before and
couldn't understand why the error was only occuring when I wanted to view a
query in design view only since the query would run find as a datasheet or in
SQL view mode.

Thanks agains! (I have picked up alot of great info from posts you have on
other threads, thanks for those too)

B

--
Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200708/1

Aug 16 '07 #8
Ben
On Aug 15, 10:22 am, "bhipwell via AccessMonster.c om" <u30281@uwe>
wrote:
Ben,

As I understand it, you cannot use SQL in the control source of a field. Do
you suggest populating the text boxes on my report using VBA during an OnOpen
event?

B

--
Message posted viahttp://www.accessmonst er.com
Not really, I guess I misunderstood the structure of your application.
My suggestion was to reduce the number of objects by replacing all
possible queries with VBA based queries. Your right that the control
source can't be sql but there are other ways around that. The simplist
being to store all your query sql in a table and then create the
queries when the report is selected from a form and delete them when
the form is closed. Not a robust solution by any means, but if you are
working with a large number of tables to prevent data duplication(?)
then it doesn't seem like it would need to be a robust solution. Just
something to get the job done.

Aug 16 '07 #9
I went back to a recently saved filed and worked through the error I was
having. The error has since disappeared, however I need to address the
structure of my queries. I think I may take your approach when I begin
cleaning them up.

Quick question, I am just fine at writing VBA code, but a little weak in SQL.
I can struggle through it for awhile until I get the hang of it. Any advise
to get a cliff notes version of writing SQL?

B

--
Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200708/1

Aug 16 '07 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
6180
by: Dan | last post by:
I am working with the C-API to MYSQL Ver 12.16 Distrib 4.0.5a-beta, for pc-linux (i686). I have noticed the following problem. Within my C program I issue --- conn = mysql_init(NULL); conn = mysql_real_connect(conn,"localhost",...etc...); if (conn == NULL) {
1
7404
by: Kevin Frey | last post by:
Hello, I have a test database with table A containing 10,000 rows and a table B containing 100,000 rows. Rows in B are "children" of rows in A - each row in A has 10 related rows in B (ie. B has a foreign key to A). Using ODBC I am executing the following loop 10,000 times, expressed below in pseudo-code: "select * from A order by a_pk option (fast 1)"
3
3834
by: Vithar | last post by:
I have a database that is being used as sort of a reports data warehouse. I use DTS packages to upload data from all the different sources. Right now I have it truncating the tables and appending with fresh data. I was considering using updates instead and my question was which is more efficent?
33
5543
by: Joshua D. Drake | last post by:
Hello, I think the below just about says it all: http://www.commandprompt.com/images/mammoth_versus_dolphin_500.jpg Sincerely, Joshua Drake
0
1048
by: Bigal | last post by:
If I want to create a ListBox filled by a database query, it is not recommended to enable the viewstate of the form, because it does not make sense to reconstruct the whole and costs performance. But what if I want to react on the onChange event, I do have to set autoRepost to true to get my selectedItem. It seems I need the viewstate to retrieve my selection. What is the best practice to do this: - fill list by database query
2
4176
by: Jon Lapham | last post by:
I have a table that stores TEXT information. I need query this table to find *exact* matches to the TEXT... no regular expressions, no LIKE queries, etc. The TEXT could be from 1 to 10000+ characters in length, quite variable. If it matters, the TEXT may contain UNICODE characters... Example: CREATE TABLE a (id SERIAL, thetext TEXT); SELECT id FROM a WHERE thetext='Some other text'; One way I thought to optimize this process would...
3
3035
by: MP | last post by:
context: vb6/ ado / .mdb format / jet 4.0 (not using Access - ADO only) - creating tables via ADO (don't have access) - all tables have a primary key (PK) - many of the PK will become FK(Foreign Key) in other table(s) - record entries will be made via ADO I am soliciting opinions on the pros and cons of using AUTOINCREMENT versus code generated GUID or LONG value (as far as my limited understanding goes, if I enter a record, and need...
13
5018
by: JayCallas | last post by:
I know this question has been asked. And the usual answer is don't use cursors or any other looping method. Instead, try to find a solution that uses set-based queries. But this brings up several questions / senarios: * I created several stored procedures that take parameters and inserts the data into the appropriate tables. This was done for easy access/use from client side apps (i.e. web-based).
4
3384
by: raylopez99 | last post by:
I'm reading a book that talks about how some operations, like LINQ queries, are "deferred execution" while other queries are "immediate execution". What is the difference? RL
0
8305
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
8823
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8730
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8605
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7321
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6163
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5632
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4151
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
2726
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.