Distributing ACCDE Front Ends - VBA not loading (1 Viewer)

[MichaelSmith]

Hi,

I'm a relative beginner to Access, I've only been working with it for a couple of months and I've run into a snag.

I'm developing a database for a university research project - the database was built in Microsoft Access 2013 with the back end being stored on a Windows Azure SQL Server. It was converted to Windows Azure SQL using SSMA for Access.

I'm now trying to create some front ends for distribution to various users - one front end which has modified forms which only show certain fields and one front end which has full access to all forms/fields. Neither of these front ends have access to tables/queries.

I've set it to open a default main menu form on opening - this form also runs the following VBA:

Private Sub Form_Load()
 ' --- User Deployment : Hide Ribbon
    DoCmd.ShowToolbar "Ribbon", acToolbarNo
    
    ' --- User Deployment : Hide Navigation Panel
    DoCmd.NavigateTo "acNavigationCategoryObjectType"
    DoCmd.RunCommand acCmdWindowHide
    
    On Error Resume Next
    strMDE = CurrentDb.Properties("MDE")                    'If this is an MDE or ACCDE database, disable bypass key
    If Err = 0 And strMDE = "T" Then
    CurrentDb.Properties("AllowByPassKey") = 0
    End If

End Sub

If I run the ACCDB file this works but I do have to hit Enable Content the first time. This is a bit of a problem as the whole point of hiding the Ribbon/Navigation bar is to prevent user access to tables and for the restricted users its to stop them pulling the restricted data directly from the tables.

The problem with the ACCDE file is even more of an issue - with this file I get a security warning and none of the VBA code runs - I don't have an option to Enable Content and they'll be able to unhide the tables to view the data. This is a fairly major issue as the front end is going to be distributed digitally to researchers and I have no control over their computers so can't do stuff like add Trusted Status manually.


Anyone have any thoughts on how I can get around the issues I've described? Happy to provide more information if it's needed.

Thanks,

Mike

 

[CJ_London]

it is chicken and egg - windows requires the user to authorise running the code and the code needs to run to take the required action to hide the window.

The way to get round it is to ensure the db is in a trusted location - This will solve the issue for subsequent opening but the user still needs to authorise the first one.

One way to do this is to have a small db which when opened is authorised by the user and code then runs to add a trusted location, then opens the front end. You can use the same 'vehicle' to also check for updates to the front end and copy to the users an updated version if required. Although the db is 'open' there are no tables or queries for the user to mess with and if you save it as a .accde then they can't see the code either.

 

[MichaelSmith]

it is chicken and egg - windows requires the user to authorise running the code and the code needs to run to take the required action to hide the window.

The way to get round it is to ensure the db is in a trusted location - This will solve the issue for subsequent opening but the user still needs to authorise the first one.

One way to do this is to have a small db which when opened is authorised by the user and code then runs to add a trusted location, then opens the front end. You can use the same 'vehicle' to also check for updates to the front end and copy to the users an updated version if required. Although the db is 'open' there are no tables or queries for the user to mess with and if you save it as a .accde then they can't see the code either.

Hmm, that's a really good idea - thanks!

Quick question though, how do I get around the fact that if they open the main database file from an untrusted location they'll be able to view restricted data in the tables?

My current thinking is I package up both the small database you suggested and the main database front-end in an EXE using DBtoEXE - they run the exe it adds a trusted location to their registry and then opens the ACCDE file from inside the EXE. Not sure if that's possible though, or if you've got any other suggestions?

I'm trying to hide the contact details/emails/addresses for individual researchers we've got working on the project and some data that's only for specific individuals as a heads up.

 

[CJ_London]

how do I get around the fact that if they open the main database file from an untrusted location they'll be able to view restricted data in the tables?

A good point. One way which I have used in the past for a different situation but should work for this is to do away with tables and queries completely and use connections from within you code (which is protected if .accde)

e.g.

dim BEdb as dao.database

Set BEdb=DBEngine.OpenDatabase(DBPath & dbName, 0, 0, "MS Access; PWD=" & dbPassword)
Set me.recordset=bedb.openrecordset("Select * from myTable")

will open a table in the backend and assign it to the form recordsource - put the code in the form load event. Note you may want to put options as to the type of recordset - see this link for more info

http://msdn.microsoft.com/en-us/library/bb243019(v=office.12).aspx

If you declare BEdb as a public object in a module it can be opened when the application starts and closed when you exit the application which keeps the db persistently open which improves performance

 

[MichaelSmith]

A good point. One way which I have used in the past for a different situation but should work for this is to do away with tables and queries completely and use connections from within you code (which is protected if .accde)

e.g.

will open a table in the backend and assign it to the form recordsource - put the code in the form load event. Note you may want to put options as to the type of recordset - see this link for more info

http://msdn.microsoft.com/en-us/library/bb243019(v=office.12).aspx

If you declare BEdb as a public object in a module it can be opened when the application starts and closed when you exit the application which keeps the db persistently open which improves performance

Awesome, thank you. I have to admit this is completely over my head - I have some experience in LUA for coding but VBA I'm completely new to - I usually just copy/paste examples and tweak them if I understand the code - this I kind of understand but I'm definitely out of my depth.

I've got about 15 sub-forms with 4 main forms used for different applications - am I going to have to set the recordsource for each subform?

Afraid I've got a few more questions as well - sorry!

1.) In the code example how would I specify the pathway if it's on a web server?

2.) I'm assuming I change the myTable part to the name of my table i.e. tblResearchers?

dim BEdb as dao.database

Set BEdb=DBEngine.OpenDatabase(DBPath & dbName, 0, 0, "MS Access; PWD=" & dbPassword) 
me.recordset=bedb.openrecordset("Select * from myTable")

Thanks for all the help, it's really appreciated.

 

[CJ_London]

am I going to have to set the recordsource for each subform?

Yes but it is not that difficult - the recordsource will either be a query or a table already stated, so just copy it into the code as a string (i.e. use the quotations)

1.) In the code example how would I specify the pathway if it's on a web server?

The same way you are at the moment. If you look in your MsysObjects table in the Connect and Database columns you will find what you are using at the moment. You may need to research connection strings a bit

2.) I'm assuming I change the myTable part to the name of my table i.e. tblResearchers?

yes! Providing all your tables are in the same db you can write multi table queries as well

 

[MichaelSmith]

Yes but it is not that difficult - the recordsource will either be a query or a table already stated, so just copy it into the code as a string (i.e. use the quotations)

The same way you are at the moment. If you look in your MsysObjects table in the Connect and Database columns you will find what you are using at the moment. You may need to research connection strings a bit

yes! Providing all your tables are in the same db you can write multi table queries as well

If you're ever out in west London and want me to buy you a beer/cake/coffee (depending on preference) I'd be happy too. This is really useful - thank you!

 

[CJ_London]

I see a client in Paddington occasionally ....

 

[MichaelSmith]

I see a client in Paddington occasionally ....

I live out in Ealing so I was thinking a little further west. If you are ever out this way though drop me a PM.

I've been playing around with this for a day or two now and unfortunately I'm still struggling a little bit - hoping you can help me out again if that's ok?

Seem to have most things working now - the only problem I'm encountering is a
Run-time error '91' when I open up the form.

Code is below:

Private Sub Form_Load()


Dim connectString As String
      'connectString = "ODBC;Driver={SQL Server Native Client 11.0};Server=GAXGPSQ05UA;Database=TRIPS;UID=trimsuser;pwd=trimsdata"
      connectString = "ODBC;Driver={SQL Server Native Client 11.0};Server=#######.database.windows.net;Database=###database;UID=####;pwd=#########"
Dim BEdb As dao.Database

Set BEdb = DBEngine.OpenDatabase("", 0, 0, connectString)
Me.Recordset = BEdb.OpenRecordset("Select * from [###database.tblArchives]", dbOpenDynaset, dbSeeChanges)

End Sub

 

[CJ_London]

Have a look at this link

http://msdn.microsoft.com/en-us/library/office/ff193474(v=office.15).aspx

your connection string goes where you currently have

"MS Access; PWD="

MS Access is certainly wrong because you are trying to connect to SQL Server

and I think the name needs to be ""

so something like

connStr="DRIVER=SQL Server Native Client 11.0;SERVER=########.database.windows.net;APP=SSMA ;DATABASE=###database"
Set bedb = DBEngine.OpenDatabase("", False, True, connStr)

You may also find this link useful

http://www.connectionstrings.com/sql-server/

 

[MichaelSmith]

EDIT:

Fixed my Run-Time Error 91 problem - code is below for anyone else experiencing the same problem:

Private Sub Form_Load()


Dim connectString As String
      'connectString = "ODBC;Driver={SQL Server Native Client 11.0};Server=GAXGPSQ05UA;Database=TRIPS;UID=trimsuser;pwd=trimsdata"
      connectString = "ODBC;Driver={SQL Server Native Client 11.0};Server=#######.database.windows.net;Database=###database;UID=####;pwd=#########"
Dim BEdb As dao.Database

Set BEdb = DBEngine.OpenDatabase("", 0, 0, connectString)
Set Me.Recordset = BEdb.OpenRecordset("Select * from [###database.tblArchives]", dbOpenDynaset, dbSeeChanges)

End Sub

All works fine now, thanks so much for the help CJ_London!

 

[CJ_London]

Glad you got there!

 

[MichaelSmith]

Glad you got there!

Oops! Spoke too soon :banghead:

It's very nearly there I've just encountered a few bugs with the forms. I did some combo boxes in a few of the forms which are linked to queries - these are now not working properly.

Example rowsource for a combo box which isn't working:

SELECT tblPersonsQuery.pName, tblPersonsQuery.pBirthDate, tblPersonsQuery.PersonID FROM tblPersonsQuery;

Guessing I have to modify it in the VBA code again?

 

[MichaelSmith]

Oops! Spoke too soon :banghead:

It's very nearly there I've just encountered a few bugs with the forms.

The first problem:

On the subforms which use the junction tables I'm seeing all of the junction tables records rather than the ones which relate to the record shown in the main form. I'm assuming this is because I've set it as SELECT * FROM jctVoyagesPersons etc.

Whereas I need to define the relationship in the SELECT statement - no idea how to do this though - oops! Effectively need to link the primary and foreign keys.

The second problem:

EDIT: This problem is fixed!

 

[CJ_London]

OK - So all done?

 

[MichaelSmith]

Ah, sorry - no. I had a problem with some combo boxes that I've now fixed but still not sure how to only show the related datasets in the forms. My posts are becoming progressively less clear sorry!

 

[MichaelSmith]

Tried this as an example of what I'm trying to do:

Set Me.Recordset = BEdb.OpenRecordset("Select * from [swpdatabase.jctVoyagesSources], [swpdatabase.tblSources], [swpdatabase.tblVoyages] WHERE jctVoyagesSources.[SourceID] = tblSources.[SourceID] AND jctVoyagesSources.[VoyageID] = tblVoyages.[VoyageID]", dbOpenDynaset, dbSeeChanges)

But that doesn't seem to want to work.

 

[MichaelSmith]

A rather lovely friend of mine just spent 3 hours on TeamViewer with me helping me figure this out - with a fairly decent amount of progress. For forms where there's only two tables involved it's now working perfectly using this code:

Set Me.Recordset = BEdb.OpenRecordset("SELECT ts.*, tv.* FROM [swpdatabase.tblShips] AS ts INNER JOIN [swpdatabase.tblVoyages] AS tv ON ts.[ShipID] = tv.[ShipID]", dbOpenDynaset, dbSeeChanges)

Where there are three tables involved though we're running into difficulty - we've tried running INNER JOINS and LEFT JOINS on the two tables concurrently but that doesn't seem to work - just displays a whole load of empty boxes - code used was similar to this:

Set Me.Recordset = BEdb.OpenRecordset("SELECT jvs.[SourceID], jvs.[SourceID] AS SourceID, jvs.[vsYear] AS vsYear, jvs.[vsIssue] AS vsIssue, jvs.[vsPage] AS vsPage, jvs.[vsCopy] AS vsCopy, jvs.[vsNotes] as vsNotes, jvs.[VoyageID], tv.[VoyageID], ts.[SourceID] FROM ([swpdatabase.jctVoyagesSources] AS jvs INNER JOIN [swpdatabase.tblVoyages] AS tv ON jvs.[VoyageID] = tv.[VoyageID]) INNER JOIN [swpdatabase.tblSources] AS ts ON jvs.[SourceID] = ts.[SourceID]", dbOpenDynaset, dbSeeChanges)

Any thoughts on where we might be going wrong?