We help IT Professionals succeed at work.

In Access 2003, which is better, Marcos or procedures? DTS?

Sandra Smith
Sandra Smith asked
on
338 Views
Last Modified: 2010-04-17
I have an Access 2003 database that each day creates links to four other databases, several tabbles in each, updates data and then closes the links.  However, I am crashing often and it will not let me open the module with the code for the procedure.  Someone told me that the space Access 2003 uses to process is not enough, and I received an Online Crash Analsyis from Microsoft that indicated I some patch was needed.  Problem is that I work for a large corporation and all updates are centralized and they will not do it just for me.

So, which is better?  A macro that calls a series of queries or a procedure that calls a series of queries.  What I have right now is everything in modules, Dims, SLQ statements, do commands, linking, deleting objects, etc.  I did it this way beause there are several users who like to "play" and I have had queries changed.  I cannot input passwords since using code to create links assumes no passwords and I do not know how to get around this.  

Also, I was thinking of use DTS that processes outside of Access.  I really am looking for some opinions as this whole process is due soon and I am stymied as to which direction.  I do not know enought about DTS, but if this is the best solution, I can sure learn it fast.

Any suggestions would be helpful.

ssmith
Comment
Watch Question

Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Sandra SmithRetired

Author

Commented:
The data is stored on a desk-top application, I did not get a server (boy, I tried).  So, I have five desk-top applications:

Sandra SmithRetired

Author

Commented:
There are two off-shore satellite database that have data brought in every monring into a production database.  The data is processed and then completed items are archived off to two "reporting" databases.  I think short-term will be to move the SQL queries defined in the procedures into regualr queies and have a marco process in sequence.  There are a lot of actions/datamovement/updates/delets that take place so I am not sure how much I will be gaining.  

DTS so far looks the way to go, but I know nothing about it yet, so, macro? or have a procedure just call all the queries?  

Sandra SmithRetired

Author

Commented:
they are mdb databases

Commented:
Well you could use MSDE which is microsoft free server that is optimised for 5 people and the access databases could link to it (it can be used for more people)

that would help make your life easier
Sandra SmithRetired

Author

Commented:
That I will investigate, I did not even think of it.  I am madly tring to figure out DTS, but the MSDE may be an alternative.  Thank you for that suggestion.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.