MS Access 2003 - Having trouble with buttons not working in mde file: list box .rowsource

Question

So I have a form that has a list box and several buttons that run sqk statements for the list box.

sub on_onClick()
' error checking

me.listbox.rowsource = "SELECT tblMain.First, tblMain.last FROM tblMain ORDER BY tblMain.Last;"

so this kinda thing is what I use for the list box. it works fine for me in the .mdb, and after i have converted the database, split it, made the .mde I go into the mde and it works fine for me still. This is placed on a shared drive

HOWEVER, anyone else that tries to use this, none of the buttons are having an effect. Can't tell if they are not working, or the VBA is not returning any results for them. It works for me, but it is not working for them. So...this is for my local LAN team, I think they all have full control permissions??? The button clicks that get them to that form are working fine also.

please help!

Answer 1

Do they have Macro security disabled? By default, MAcro security is enabled in Access and none of your VBA code will run. To disable: open Access, Tools -> Macro -> Security and set to low.

Answer 2

I think it is generally best to use self-certified projects or a secure location rather than change security levels.

Some information on macro security:

Self Certify projects

General information on security principals for Office code and macro security

Trusted location defaults for Office 2003

Macro security in Office 2003

Answer 3

A couple of things come to mind:

1. It is not clear from your question whether the mde file itself is on a shared drive or whether the back-end database is on the shared drive and each user has a copy of the mde file on their PC. If the users are using the same mde file on the network share then I would strongly suggest not taking this approach. (It probably isn't the cause of the issue, but it will get you into trouble down the road.) Give each user a copy of the mde (front end) and have the application point to the back-end database on the network share.

2. Make sure that the mde has the ability to relink the tables to the back-end mdb file.If you search SO you'll probably find examples of how to do this in code.

3. I suspect you may be running into an issue where the mde file is not able to find the back-end tables. In this case you won't receive an error message in your VBA code when you're setting the listbox's rowsource and it will appear as though nothing is happening.