Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

New admin for MS Access, how to modify existing macro

Tags:

ms-access

I'm a new admin for an MS access database. I'm very new to MS Access so please forgive me if I ask something that does not make sense here....

The current staff has a small (5MB or so) .mdb file on their desktop. Clicking that file opens access and lists queries on the left hand side of Access. I've copied this file to my laptop in an effort to start working/maintaining the databaser. However clicking any query yields in an error (vice loading/running) that essentially says the network location (file) cannot be found.

The .mdb file on their desktops includes macros to load the main .mdb file (much bigger at 33MB) via a network share. Yet when I open this .mdb file from my desktop and view the macros via the macro tab I do not see any macro that tries to load the .mdb file from the network share. Additionally some of the macros fail. One in particluar is trying to load the main .mdb file over the network share. I have the main .mdb file on my desktop and would like to change, amongst other things, that macro to point to the local copy vs. the network copy to which the macro points. What is the best way to view all macros in an .mdb file of this nature with the intent on modifying them as needed?

EDIT:

I should also mention that I have a new version of Access on my laptop (2010). Everyone else is using 2003. Unfortunatley I am unable to install the old version on my laptop due to corporate policy. As people use the database regulary I ideally need to modify the .mdb file on my laptop which runs 2010...

like image 435
WildBill Avatar asked Dec 03 '12 06:12

WildBill


2 Answers

I doubt you're having macro or VBA problems.

What is far more likely is that the tables you see listed on the left hand of the screen are actually "Attached" tables which reside in the other database on the network.

If this is the case, the icon just to the left of each table name will have a small arrow.

You say you now have a copy of the larger database locally on your PC, so what you need to do is to is go to the smaller DB, and under "External Data" choose "Linked Table Manager."

Select all of the tables which show they are attached to the server version of the larger DB, choose "Always Prompt for New Location" and click "OK."

You will be given an "Open File Dialogue" which will allow you to browse to your local copy of the larger database. Simply click on that file name and click "OK" or "Done" or whatever button means you're done.

like image 84
tbur Avatar answered Oct 13 '22 08:10

tbur


It is common to split an Access application into front-end (code, forms, etc) and back-end (data). The back-end tables are then linked to the front-end. This can be done at run time.

When you say macros, do you mean macros or code modules? If you mean macros, they will be listed under macros in the database window, if you mean code, you may see modules listed, which you can open and modify. A good place to find the code to link tables will be in an autoexec macro or a start-up form.

like image 43
Fionnuala Avatar answered Oct 13 '22 08:10

Fionnuala