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.com
http://www.accessmonster.com/Uwe/For...ccess/200708/1

re: VBA versus Query

bhipwell via AccessMonster.com wrote: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.Openrecordset
and not closing them or setting the object to nothing?

re: VBA versus Query

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

re: VBA versus Query

On Aug 15, 8:31 am, "bhipwell via AccessMonster.com" <u30281@uwe>
wrote: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.

re: VBA versus Query

bhipwell via AccessMonster.com wrote:
I would agree it's a mess.
Goto google groups and search for your errors. Search in *access* group
textbox.

re: VBA versus Query

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

re: VBA versus Query

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.com" <u30281@uwewrote in message
news:76bcb996512ed@uwe...

re: VBA versus Query

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.com
http://www.accessmonster.com/Uwe/For...ccess/200708/1

re: VBA versus Query

On Aug 15, 10:22 am, "bhipwell via AccessMonster.com" <u30281@uwe>
wrote: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.

re: VBA versus Query

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.com
http://www.accessmonster.com/Uwe/For...ccess/200708/1

re: VBA versus Query

Quick question, I am just fine at writing VBA code, but a little weak in SQL.Use the query designer to create your query, flip over to SQL view,
then copy & paste into the VBA module. You will have to do some
creative quoting and such but it's not that bad.

re: VBA versus Query

On Aug 16, 12:14 pm, "bhipwell via AccessMonster.com" <u30281@uwe>
wrote:I'm sure there are tons of guides and quick learning books available
for ramping up SQL skills. But I think the best way to learn is to
just do it. To that end Bill's suggestion is probably best. Just look
at the SQL your query designer builds, it helps to indent it and
separate out the lines in a text editor to see what you are doing,
then just paste it into VBA.

You probably know all this from your VBA experience but I think it
bears repeating.

If the queries are short enough then just put it all on one line:

currentdb.execute "update mybesttable set status = 1"

if the query is longer you need to keep it all together by adding " &
_ " at the end of each line and making sure each portion of the
statement is enclosed in quotes. The funky quotes come into play if
you have text references in your query criteria because text
references are surrounded by quotes. When you run a query in VBA its
like another level of processing so you need to write the query to
accommodate that. A single quote must be written as a double quote for
it to be read as a single quote:

currentdb.execute "update mybesttable " & _
"set status = 2 " & _
"where first_name = ""Bill"" " & _
"and status = 1"

re: VBA versus Query

Thanks for the kind words. I'm glad you made progress. Questions that are
both precise and concise have the best chance of getting good answers in
newsgroups, and specific questions are more likely addressable than general
ones. There are, as you probably know, good suggestions on effective use of
newsgroups at the FAQ for this newsgroup at
http://www.mvps.org/access/netiquette.htm and the site
http://www.mvps.org/access/ is full of good information from very
knowledgeable Access users and developers.

Regards,
Larry Linson
Microsoft Access MVP