From Excel Get Data > From Database which is what I thought the users would be doing. If you're saying it can be done via code, I'm not sure if that's a fair comparison given the info in the first post. I'm saying that the likelyhood that users are going to write their own code to circumnavigate the lack of ribbon function is probably nil.
That's what I assumed you had done
Whilst using Get Data > From Database in Excel, you cannot view tables of any open database opened exclusively, it seems somewhat irrelevant as, you can of course do so when the database is closed. For example the files can be copied to a USB stick and 'hacked' at a later date/time
I also very much doubt that anyone will write code to import the data in Excel especially as its probably easier to do this from Access.
However, as already stated the fact remains that if the data is valuable enough, a skilled and experienced hacker with sufficient time & determination will be able to do so for any 'standard' Access database.
In which case, data that is that valuable shouldn't normally be stored in Access
I don't follow your logic and don't see how it matters if everyone has their own fe (as they should). Remember that I said
- if the db (fe) is opened exclusively (that is a method of opening a db in case you're not aware) then they cannot connect from Excel
- if it isn't open, there are no table links returned to Excel because they would be deleted on close and rebuilt on open
Not saying this is the best approach, just that I followed up with a test to connect from Excel when a db was opened exclusively and it turned out that I could not. Therefore I don't see how Excel can connect to a fe db with no links to the be.
I don't follow your logic and don't see how it matters if everyone has their own fe (as they should). Remember that I said
- if the db (fe) is opened exclusively (that is a method of opening a db in case you're not aware) then they cannot connect from Excel
- if it isn't open, there are no table links returned to Excel because they would be deleted on close and rebuilt on open
Not saying this is the best approach, just that I followed up with a test to connect from Excel when a db was opened exclusively and it turned out that I could not. Therefore I don't see how Excel can connect to a fe db with no links to the be.
You guys seem to be overlooking the fact that the method I suggested destroys the links on close. Not sure what happens when you copy this db if it is open or even if you can when it was opened exclusively. I'm not going to test that or the other assumptions because I don't think my idea has any traction here.
Well if you do, think it through and design accordingly. How are you going to rebuild the links - from a table of stored paths and code in the fe? Then a user could access that info from Excel right? I'm assuming you'd either have to hard code the path (in the code project) or store them in the be and get them from code with an ADO connection. As soon as you do that, you have to provide the password in code. Your code project must not be accessible then.
Then there is what Isladogs inferred - what happens if someone copies the accde fe when it is open? I don't know and didn't consider it at the beginning. There is only so much you can do to protect Access tables from snooping, and the level you go to might be more than you need given the data and the ability of users as hackers.
there is with a bit of a rewrite of your FE to have all your queries written in VBA or in the BE and assign recordsets to forms and reports - Even that is not 100% secure from someone who knows their way around access.
Very simply:
At the moment you have a form with a recordsource of 'Table1' or 'SELECT * FROM Table1' or similar
or perhaps you have vba code along the lines of
sqlStr="SELECT * FROM Table1"
me.recordsource=sqlstr
Instead, what you do is as follows
When the FE is first opened, create a private db variable in a module with a couple of functions - something like this
Rich (BB code):
Option Compare Database
Option Explicit
Private BEdb as dao.database
Private function openBE() as boolean
if BEdb is nothing then 'open the backend
set BEdb=dbengine.opendatabase("C:\databases\BE.accdb",0,0,";PWD=password")
end if
end function
Public Function BErst(ByVal sqlStr as string) as dao.recordset
OpenBE 'in case not already open
set BErst=BEdb.openrecordset(sqlstr, dbfailonerror)
End Function
then in your form open or load event or however you are doing it
sqlStr="SELECT * FROM Table1"
set me.recordset=BErst(sqlstr)
now you can delete all the linked tables from your front end
the above is freetyped so check for typo's and you will no doubt want to add in error handling
Note as mentioned above with this method you can put queries in the BE that can be treated as views or action queries - providing the queries do not have parameters referencing forms. So you might have in the BE a query called 'vwTable1' with the sql "SELECT * FROM Table1"
and in your form you might have
sqlStr="SELECT * FROM vwTable1 WHERE ID=" & me.ID
set me.recordset=BErst(sqlstr)
For action queries you can just use for example
BEdb.Execute "DELETE * FROM Table1 WHERE ID=" & me.ID
which also gives you the benefit of being able to use the recordsaffected value
BEdb.Execute "DELETE * FROM Table1 WHERE ID=" & me.ID
msgbox BEDb.recordsaffected & " records deleted"
It still leaves the password accessible to those who know what they are doing but I see Colin has just posted something so he is probably covering that detail
And as others have said, Access cannot be made 100% secure, but there is a lot you can do to discourage 99% of potential hackers
When i call the function i get an error, Invalid argument. dbFailOnError = 128.
When i goto the fucntion and hover BErst it says nothing. While the sqlStr contains the string it should.
Am i missing something?
Well if you do, think it through and design accordingly. How are you going to rebuild the links - from a table of stored paths and code in the fe? Then a user could access that info from Excel right? I'm assuming you'd either have to hard code the path (in the code project) or store them in the be and get them from code with an ADO connection. As soon as you do that, you have to provide the password in code. Your code project must not be accessible then.
Then there is what Isladogs inferred - what happens if someone copies the accde fe when it is open? I don't know and didn't consider it at the beginning. There is only so much you can do to protect Access tables from snooping, and the level you go to might be more than you need given the data and the ability of users as hackers.
I do not think i need to go to really great length. But i found the option where u can click new query and then get all the info you want a bit to easy.
So i am going to do some research on how to link and delink tables and test if that works for me. Thank you for your input
So to get back, i tried your option and it didnt work as i would like to have it work. I would rather not do it with opening a recordset.
I just want to set the recordsource. So i tought why not use your idea but then apply it to the recordsource.
But how was i going to do this. With a function ? Then i started looking at how Isladogs did his security on the file he send in this thread.
Then i saw he was setting the recordsource from the code while connecting to another database. So i opted for that solution and that works great. I was already setting the recordsource from vba. So i only needed to edit a small portion.
So in my SQL statement in the From line i added the part:
" FROM tblBEData IN '' [MS Access;PWD=isladogs;DATABASE=" & CurrentProject.Path & "\BE.accdb];"
@bthomsin
Glad you have a solution that works for you.
Just for information, although the earlier version on my website used that code, there are ways of bypassing the security which I have since dealt with.
The ACCDE version uploaded in this thread had various changes to the code.
However, it is still the case that neither forms nor reports have a saved record source or recordset.
BTW Although I was aware that the password could be removed, that should have triggered code to automatically close the database.
Although it didn't help you crack my app, I'm still hoping you would let me know via email or PM exactly what you did.
I'm hoping to look at my app this evening (UK time) so it would be appreciated if you could let me know later today if possible.
So in my SQL statement in the From line i added the part:
" FROM tblBEData IN '' [MS Access;PWD=isladogs;DATABASE=" & CurrentProject.Path & "\BE.accdb];"
agree this is another way - I use it myself for temporary connections. The only issue is you are opening a connection each time which takes time. Using the BEdb method uses the same connection
agree this is another way - I use it myself for temporary connections. The only issue is you are opening a connection each time which takes time. Using the BEdb method uses the same connection
Ah i know what you mean, since it has to reopen the backend everytime it is slower.
To solve this i added a recordsource to the main form directed to an empty table. This keeps the database connection open but it wont show the all the tables. So for my problem this works.
I don't know the answer but you might want to run some tests as to whether the query uses that connection or makes it's own. From a straight coding perspective, I don't see how it can but access might recognise it already has a connection 'under the hood' somewhere. Don't forget if you have a form with say 3 combo's, that is potentially 4 connections.
I don't know the answer but you might want to run some tests as to whether the query uses that connection or makes it's own. From a straight coding perspective, I don't see how it can but access might recognise it already has a connection 'under the hood' somewhere. Don't forget if you have a form with say 3 combo's, that is potentially 4 connections.
Hmm i dont know if it uses the same connection. But when i do not have a open link to the database and i make two buttons and make it swap between two diffrent tables on two diffrent backends it takes 2 seconds to load between them. Now i do the same but with a form that has a link to both backend with an unimported table linked and in the recordsource, just to keep the conneciton open. When i click these same buttons now, it loads instant, instead of the 2 seconds on each click.
So i figured when loading the main form, it opens the backend with useless forms and when i click a button to load specific data it does it faster cause it doesnt have to open the whole backend. Well this is just a guess on how it works. But i see a big diffrence in load time.
I hope my explanation makes sense. I understand english well, but putting my thoughts on paper is diffrent story.
How do you encrypt the BE using RC4 encryption? I would be interested to know as without this it's easy to find the password of a normal password protected BE.
