Protecting VBA from user changes to code (1 Viewer)

E

Earl C Phillips

Volunteer Programmer

Local time

Yesterday, 19:21

Joined

Jul 18, 2008

Messages

40

• Apr 17, 2013

• #1

I volunteer at a food bank writing VBA systems for them. In the last year my code has been changed at least twice, even though the Current Database, Display Navigation Pane has been left unchecked.

I need some way to prevent users from having access to VBA code so they cannot change anything. Does anyone have guidance for how this can be done? I have seen such a project, but did not have access to the author to learn how it was done.

 

D

DJkarl

Registered User.

Local time

Yesterday, 19:21

Joined

Mar 16, 2007

Messages

1,028

• Apr 17, 2013

• #2

Open a VBA module, on the tools menu you should see several options, there should be one like YourDatabasename Properties...
Click this, go to the second tab called protection. Click the Lock project for viewing, then enter a password below.

 

E

Earl C Phillips

Volunteer Programmer

Local time

Yesterday, 19:21

Joined

Jul 18, 2008

Messages

40

• Apr 22, 2013

• #3

I have done as you say and placed a password on the VBA code. Is there a way beside not showing the Navigation Pane to prevent someone from altering a query or a report? The latest sabatage was a change of a query parameter to draw the wrong group of data so the report was wrong.

I want to neck this down so only programmers can alter the code, reports or queries. Any help would be much appreciated. I hate chasing after false errors.

 

D

DJkarl

Registered User.

Local time

Yesterday, 19:21

Joined

Mar 16, 2007

Messages

1,028

• Apr 22, 2013

• #4

I have never found a very reliable way to keep people out of the queries. You can keep them out of forms and reports by making the database an MDE or ACCDE, this would also keep them out of the code as it is compiled at that time.

What I've done to keep people from making changes to queries is keep the SQL for the query either in a hidden table, or directly in the code and at start-up I recreate the queries. This eliminates the guess work, of course this approach only work if each user has their own front end.

 

E

Earl C Phillips

Volunteer Programmer

Local time

Yesterday, 19:21

Joined

Jul 18, 2008

Messages

40

• Apr 22, 2013

• #5

Making an MDE or ACCDE is easy enough, as is puting a copy of the SQL into a table, but will that allow the SQL to source parameters from report pages? Most of my SQL contain parameters taken from fields on Report pages to match the needs of the current user. I also have stacked queries to refine data as it approaches the report it will be used to create. How would I call stacked queries from a table?

Thanks for any help you can render.

 

E

Earl C Phillips

Volunteer Programmer

Local time

Yesterday, 19:21

Joined

Jul 18, 2008

Messages

40

• Apr 22, 2013

• #6

I forgot, is there a way to prevent the use of the tool bar or similar way to get at queries?

 

M

mdlueck

Sr. Application Developer

Local time

Yesterday, 20:21

Joined

Jun 23, 2011

Messages

2,631

• Apr 22, 2013

• #7

Earl C Phillips said:

How would I call stacked queries from a table?

Click to expand...

I would suggest the approach of defining all of the query objects in code and thus not rely on any saved QueryDef objects.

Example of DAO.QueryDef objects downloading records from a SQL BE DB via Pass-Through query and populating a FE temp table with them
http://www.access-programmers.co.uk/forums/showthread.php?p=1119605#post1119605

 

E

Earl C Phillips

Volunteer Programmer

Local time

Yesterday, 19:21

Joined

Jul 18, 2008

Messages

40

• Apr 22, 2013

• #8

Most of my code is written in ADO versus DAO. What is the comparable ADO version of DAO.QueryDef?

 

M

mdlueck

Sr. Application Developer

Local time

Yesterday, 20:21

Joined

Jun 23, 2011

Messages

2,631

• Apr 22, 2013

• #9

Earl C Phillips said:

Most of my code is written in ADO versus DAO. What is the comparable ADO version of DAO.QueryDef?

Click to expand...

rrrrrrrrrr???????

QueryDef objects in the Navigation Pane are only able to be manipulated with the use of DAO objects. I have never heard of a way to adjust them through ADO objects.

ADO Command and ADO Recordset objects are defined in VBA source code, not in the Navigation Pane. If you were only using ADO objects, you should not have any Query objects in the Navigation Pane to be concerned about.

Thus: "rrrrrrrrrr???????"

 

C

CJ_London

Super Moderator

Staff member

Local time

Today, 01:21

Joined

Feb 19, 2013

Messages

16,610

• Apr 23, 2013

• #10

What I've done to keep people from making changes to queries is keep the SQL for the query either in a hidden table

Click to expand...

I do something similar, I have code call the query text but rather than keeping them in a hidden table (which can be unhidden) I put them in another db which is password protected.

I get round the parameter problem by using a parameter name and when the SQL is called from the table I can simply use replace to replace the parameter name. e.g

Query = SELECT * FROM MyTble where ID = [Parameter1]

when called I use rst.openrecordset(replace(callsql("QueryName"),"[Parameter1]",me.ctrl)

 

E

Earl C Phillips

Volunteer Programmer

Local time

Yesterday, 19:21

Joined

Jul 18, 2008

Messages

40

• Apr 24, 2013

• #11

Your suggestion may have merit but I think it is beyond my skills. I am a converted COBOL programmer from mainframe background (eleven years ago when I worked for money). I will research your technique and see if I can duplicate the process, but it will take me some time to read up on the process.

Thanks for pointing me in a positive direction.