I tried to import tables from an already opened MS Access accdb file into a new accdb file using the built in import menu, but I got an error message.
The error message said that the "admin" user has set the file in an inaccesible state. (this is my translation from hungarian)
So I was not able to import any table or query.
I would like to know, how to move an accdb or accde file into this inaccesible state programmatically, using VBA code.
Reason behind: I would like to distribute a front end accde file wich protects itself from anyone stealing odbc connection info, like MySQL server user and password. But the Msysobjects table can be imported into another access file.
I can create tables and queries at startup and delete them at application close, but how to prevent table & query import from it while it is running?
This inaccesible state would come handy.
If there is another way to block table import into other access files, please let me know.
* Edited section: *
Design view trick:
DoCmd.OpenForm "frmUnused",acDesign,,,,acHidden
Works with accdb, table import locked while editing. As expected, doesn't work with accde.
Connection cache approach:
Works with queries.
FIXED: No matter how hard I tried, user and password has been saved with table. I provided a connect string without user and password, but it appeared in the connect string after appending the tabledef to the collection.
Method A saved the unprovided password, method B works only with manual logon.
Remarks: I know that storing user & password in VBA inside an accde isn't the most secure option, but.. 1. Creating a new user on server side & assigning a role might be painfully slow. (see also: burocracy) 2. Also handing over server credentials to a few thousand users makes social engineering easier.
Const values, even Private Const values are viewable, if you open the accde file in a text editor. Use functions instead to echo values.
Actual user and password has been replaced in the text below.
LOGON PASS/FAIL and .Connect before/after lines were copied from debug.print output.
I have tried two variations:
A: / proposed by Albert D. Kallal /
* Delete all tables and queries, compact & repair, then restart application.
* ? tabletest: testlogon with user and pwd, then AddOneTable(MyCon) without.
* LOGON PASS
* .Connect before = ODBC;DRIVER={MySQL ODBC 5.3 Unicode Driver};Server=localhost;Database=mesterlista
* .Connect after = ODBC;DRIVER={MySQL ODBC 5.3 Unicode Driver};Server=localhost;Database=mesterlista;user=;password=;
* >> User and passsword stored.
* Fix: after .append, reedit .Connect & relink.
B: / works with manual logon /
* Delete all tables and queries, compact & repair, then restart application.
* AddOneTable(MyCon) + MySQL Connector/ODBC data source configuration window
* .Connect before = ODBC;DRIVER={MySQL ODBC 5.3 Unicode Driver};Server=localhost;Database=mesterlista
* .Connect after = ODBC;Driver=MySQL ODBC 5.3 Unicode Driver;SERVER=localhost;DATABASE=mesterlista;PORT=3306
* >> Note that {} disappeared in driver name, also PORT appeared.
* application restart
* ? TestLogon("ODBC;Driver=MySQL ODBC 5.3 Unicode Driver;SERVER=localhost;DATABASE=mesterlista;PORT=3306" & ";user=;password=")
* True
* Table isn't working, MySQL Connector/ODBC data source configuration window pops up.
* ? TestLogon("ODBC;Driver=MySQL ODBC 5.3 Unicode Driver;SERVER=localhost;DATABASE=mesterlista;PORT=3306") + MySQL Connector/ODBC data source configuration window, logging in manually
* True
* Table is working.
* >> So this method is good if you wish for manual logon, but doesn't work with automated logon.
VBA code used for testing:
Public Function tabletest()
Dim MyConWithPassWord As String
Dim MyCon As String
MyCon = "ODBC;DRIVER={MySQL ODBC 5.3 Unicode Driver};Server=localhost;Database=mesterlista"
MyConWithPassWord = MyCon & ";user=***;password=***"
If TestLogon(MyConWithPassWord) = False Then
Debug.Print "LOGON FAIL"
Exit Function
End If
Debug.Print "LOGON PASS"
AddOneTable (MyCon)
End Function
Public Function TestLogon(strCon As String) As Boolean
On Error GoTo TestError
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Set dbs = CurrentDb()
Set qdf = dbs.CreateQueryDef("")
qdf.Connect = strCon
qdf.ReturnsRecords = False
'Any VALID SQL statement that runs on server will work below.
qdf.SQL = "SELECT 1;"
qdf.Execute
TestLogon = True
Exit Function
TestError:
TestLogon = False
Debug.Print Err.Number, Err.Description
Debug.Print strCon
End Function
Public Function AddOneTable(strCon As String)
On Error GoTo myerr
Dim tdfcurrent As DAO.TableDef
Dim LocalTable As String ' name of local table link
Dim ServerTable As String ' name of table on SQL server
LocalTable = "cimke"
ServerTable = "cimke"
Set tdfcurrent = CurrentDb.CreateTableDef(LocalTable)
tdfcurrent.SourceTableName = ServerTable
tdfcurrent.Connect = strCon
Debug.Print ".Connect before = " & tdfcurrent.Connect
CurrentDb.TableDefs.Append tdfcurrent
CurrentDb.TableDefs.Refresh
Debug.Print ".Connect after = " & CurrentDb.TableDefs(LocalTable).Connect
' fix: removing user / password from tabledef.connect:
Set tdfcurrent = CurrentDb.TableDefs(LocalTable)
tdfcurrent.Connect = strCon
tdfcurrent.RefreshLink
Debug.Print ".Connect after RELINK = " & CurrentDb.TableDefs(LocalTable).Connect
Exit Function
myerr:
Debug.Print Err.Number, Err.Description
Debug.Print strCon
End Function
Yes, you can do this.
You do NOT have to include the UID + password in the connection strings.
What this means is that on code start up you can execute a logon. You can either:
Embed the logon in your VBA code. Only a ONE TIME logon at start up is required and then ALL OF your existing linked tables will work, and this works EVEN WHEN the linked tables don’t have the user + password included in those connection string.
And you can use some type of encrypting, or hash code in VBA to convert the embedded user + password if you are worried about someone uses some kind of file HEX editor etc. to parse or view the contexts of the accDE file.
The other simple approach is to prompt the userid + password on start-up (ask the user for id + password). As this point you simply then execute a logon. Once that logon has been executed, then all of the linked tables will work just fine, and those linked tables DO NOT require the UID+PASSOWRD to be included, nor imbedded in those linked tables.
I cannot stress that a re-link of tables is NOT required if you do this.
The reason and trick for above is access will cache the logon ONCE a legitimate logon has occurred. ONCE a logon has occurred (a successful connection), then ALL OTHER linked tables will use this cached connection.
So, I suggest you delete all your linked tables, and then execute a logon, and then re-link all of the tables and LEAVE OUT the user + password when you do this. (Just remember to NOT include the user + password in the connection string used to link the tables. You can do this using the built-in table manager, or you can use code – either way will work.
Once you link this way, if you launch the application, the table links will NOT work, and they ONLY work AFTER you execute a logon.
What this means then if someone attempts to open, or import the table links to another database, the linked tables will not work since the linked tables do not include the user + password.
To execute a logon, use the following code:
strCon = "valid connection string PLUS USERID + PASSWORD"
So take the existing connection string (without userID/password), add to the string the userid/password, and then execute the logon code like this:
TestLogon(strCon)
The code to execute the logon is thus:
Function TestLogin(strCon As String) As Boolean
On Error GoTo TestError
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Set dbs = CurrentDb()
Set qdf = dbs.CreateQueryDef("")
qdf.connect = strCon
qdf.ReturnsRecords = False
'Any VALID SQL statement that runs on server will work below.
qdf.sql = "SELECT 1 as t"
qdf.Execute
TestLogin = True
Exit Function
TestError:
TestLogin = False
Exit Function
End Function
Once you execute the above, then the linked tables without the user + password will now work!
If someone attempts to open, or import the table links, then the user + password will NOT be included, nor do you want to include the user + password in the connection string used to link the table.
So delete your table links. Execute the above logon code. Now re-link the tables – but do NOT choose the option to include the user + password.
If you skip the logon code above, and attempt to open a table, then you will get prompted for the userid + password by the ODBC driver. (So what kind of prompt you get will depend on how the database vendor setup their odbc drivers to handle this.
The above is all you require. The “idea” for the above code and how this works is outlined in the following article:
https://blogs.office.com/en-us/2011/04/08/power-tip-improve-the-security-of-database-connections/
However, the same code and the explain in as per above should suffice.
YOU DO NOT HAVE to include the userid + password in the linked tables, and for obvious reasons of security, you should not.
Keep in mind that near all client software needs some logon, but if you don’t embed the uid/password anywhere in the code, then the huge hole of security in regards to linked tables in Access is eliminated. If someone attempts to disassemble the application, grab the linked table connection strings etc., they WILL NOT get the password. So if one prompts at startup, logs on, and then nulls out those values, once again the uid/password is NOT embedded in the application.
Anyone can dissemble the code, and they not get the uid/password if you follow this approach in Access.
If a user imports those links, and they have the uid/password in plain view, then they will see the uid/password. However, if you link the tables without the uid/password, then there is no requirement to embed the uid/password ANYWHERE in the application to work. All that is required is to execute a valid logon using the above code - once done, then all connections will work without issue.
So before, or after executing the logon, at no point in time will the linked tables show or have the uid/password. In fact, this even includes if one is using a accDB (non compiled application). If you jump into the debug window and inspect the connection strings used for the active working linked tables, you will see that the uid/password IS NOT INCLUDED IN THE CONNECTION STRING!
The vast majority of client software systems do have to connect to a database, and if one does not include the uid/password anywhere in that code or application, then that is a "reasonable" level of security.
Here is some sample code I used:
Sub TEst222()
Dim MyConWithPassWord As String
Dim MyCon As String
MyCon = "ODBC;DRIVER=SQL Server;SERVER=albertkallal-pc\SQLEXPRESS;DATABASE=test3"
MyConWithPassWord = MyCon & ";UID=TEST3;pwd=password"
TestLogon (MyConWithPassWord)
AddOneTable (MyCon)
End Sub
TestLogon used was:
Function TestLogon(strCon As String) As Boolean
On Error GoTo TestError
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Set dbs = CurrentDb()
Set qdf = dbs.CreateQueryDef("")
qdf.Connect = strCon
qdf.ReturnsRecords = False
'Any VALID SQL statement that runs on server will work below.
qdf.SQL = "SELECT 1"
qdf.Execute
TestLogon = True
Exit Function
TestError:
TestLogon = False
Exit Function
End Function
and to add a table link:
Sub AddOneTable(strCon As String)
Dim tdfcurrent As DAO.tableDef
Dim LocalTable As String ' name of local table link
Dim ServerTable As String ' name of table on SQL server
LocalTable = "MyTable"
ServerTable = "dbo.MyTable"
Set tdfcurrent = CurrentDb.CreateTableDef(LocalTable)
tdfcurrent.SourceTableName = ServerTable
tdfcurrent.Connect = strCon
CurrentDb.TableDefs.Append tdfcurrent
End Sub
My example code is for SQL server. However the article that I link to above is rather good, and the example code was for MySQL.
Here is a MySQL example:
Dim MyConWithPassWord As String
Dim MyCon As String
MyCon = "ODBC;DRIVER={MySQL ODBC 3.51 Driver};Server=localhost;Database=test3"
MyConWithPassWord = MyCon & ";User=TEST3;Password=TEST3"
If TestLogon(MyConWithPassWord) = False Then
MsgBox "LOGON FAIL"
Exit Sub
End If
AddOneTable (MyCon)
So you need to test if you actually logged on (that was the WHOLE point of this post).
If you are receiving a prompt during the re-link, then that means what you have is not working. THE WHOLE POINT here is to eliminate the need to logon.
Once this is working. you can launch Access, and double click on a table - the ODBC logon will prompt. However, if you execute your logon code first, then you can click on the table without a odbc logon prompt.
