Preventing tables,queries, forms and reports to be imported into a new database

[Emmanuel]

I have an access database which I have successfully split into FE and BE.

I have also disabled the shift bypass so users can’t access the queries, forms and other relevant information to protect the data.

But I noticed that when I open a new access database, am able to easily import the forms, queries, including all my vba codes into a new access database file

Is there a way to block that feature so no unauthorized person can import my data to create another copy of my database?



Any help with this will be greatly appreciated

[isladogs]

Convert the FE to an ACCDE and encrypt it using a password.
Forms & reports can no longer be imported to another database. Nor can the code in VBA modules/macros
However tables & queries can still be imported
There are ways of making databases more secure but a skilled and determined 'hacker' will still be able to gain access to your tables & queries.

BTW it isn't difficult to re-enable the shift bypass externally

[Emmanuel]

Convert the FE to an ACCDE and encrypt it using a password.
Forms & reports can no longer be imported to another database. Nor can the code in VBA modules/macros
However tables & queries can still be imported
There are ways of making databases more secure but a skilled and determined 'hacker' will still be able to gain access to your tables & queries.

BTW it isn't difficult to re-enable the shift bypass externally

I don’t want a situation where users will have to enter the database password before they login

It it could be password protected such that when you try to import into a new database, you will be prompted with a password to enter before importing, I believe that will be cuul

Users already have a password login to do that

[isladogs]

Your choice but encryption does add extra security to your ACCDE FE.
Suggest you include a number of measures outlined in my 2 part article: Improve Security in Access databases including removing the navigation pane & ribbon so users have no easy method of viewing tables & queries. Also lock down Access options so the nav pane/ribbon can't easily be restored.
You could also use VBA code instead of saved queries as the code will not be accessible in an ACCDE

[Emmanuel]

Your choice but encryption does add extra security to your ACCDE FE.
Suggest you include a number of measures outlined in my 2 part article: Improve Security in Access databases including removing the navigation pane & ribbon so users have no easy method of viewing tables & queries. Also lock down Access options so the nav pane/ribbon can't easily be restored.
You could also use VBA code instead of saved queries as the code will not be accessible in an ACCDE

So I convert all queries to vba codes?

[isladogs]

That’s one option but there’s no point unless you convert the FE to an ACCDE. The tables will still be accessible from another database unless they are made ‘deep hidden’. See my article for more info

[Emmanuel]

That’s one option but there’s no point unless you convert the FE to an ACCDE. The tables will still be accessible from another database unless they are made ‘deep hidden’. See my article for more info

Thanks so much for your time man
Though am finding difficulties converting queries to vba codes

But all codes are hidden after compiling.
The tables and queries will be useless without the vba codes.

And I think that should be much better than before

[Welshgasman]

Try Colin's SQL2VBA converter?
https://www.access-programmers.co.uk.../#post-1693394

[moke123]

Most importantly, make sure you have plenty of backup copies.

[Emmanuel]

Alrite
Will give it a try

[Emmanuel]

Is there another way to this? Because I seem not to understand how SQL2VBA converter works

[Micron]

Sure - do it manually but why not figure out how to use the tool? You copy a sql statement from a query and paste it into the sql tab. Click 'convert to vba' and look at the vba tab. Copy that and use in your code.

EDIT - I have a question for the creator though. Why does it do only action queries? .Execute or RunSql will generate an error with a simple SELECT query, no?

[isladogs]

You're of course correct that you cannot use Execute or RunSQL with SELECT queries.
I released this app 4 years ago and had planned to do a later update to create query defs code for SELECT queries...if there was a demand for it.
Until today nobody has mentioned it in 4 years!

For info, the app was an extension of a utility originally created by Allen Browne.
For more details see my website article: SQL to VBA and back again
The intention is really to get people started with doing the conversions (in either direction) until they are confident in doing it manually.
Therefore, after a while, the utility becomes superfluous for most developers. IMO, by that time, its done its job.

[Micron]

Will take a look at your article(s). Have been looking at the code behind the form, trying to figure out why I'm not getting line continuation characters in the vba result when copying/pasting from query sql view. Also have noticed that if I choose to create a query, it does so and opens to sql view according to the code. However, it doesn't show up in the objects pane and 'all objects' is selected for viewing. It must be there though because the code doesn't error if it attempts to delete it.

It seems that in later versions, one must use Application.RefreshDatabaseWindow as DB.QueryDefs.Refresh doesn't do it now if it ever did.

EDIT - I closed and reopened db. Now I'm getting line continuation characters??

[isladogs]

Thanks Micron

Prompted by the comments in this thread I'm in the process of making a few belated changes to this utility:
1. The CreateQuery procedure was originally by Daniel Pineault - I know that line used to work but Application.RefreshDatabaseWindow is definitely a better alternative - UPDATED
2. I've significantly expanded the list of SQL keywords to capitalise in cmdSql2vba_Click. Let me know if I've missed anything

Version 3.1 attached. This is still work in progress.
I haven't yet done QueryDefs code to handle non-action queries but may do so in the next day or so
There are also a few other archaic code lines from the era of A97 that I may update whilst I'm at it

Any other comments welcome.

NOTE: @Moke123 also has a nice CodeGen utility which he may be willing to upload