Unanswered: Help with linking tables at OnLoad, please
I found the following code, and need some advice implementing it:
I have the following concern:
Private Sub form_load()
Dim Cnn As ADODB.Connection
Dim Rst As ADODB.Recordset
Dim strConnect As String
Set Cnn = New ADODB.Connection
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Password=" + DBPwd + ";User ID=" + CurrentUser()+ ";" & _
"Data Source=\\DreamMachine1\SocialShare1$\Wgroup1Folder\FantasticProductivity1.mdb;" & _
"Persist Security Info=False;" & _
"Jet OLEDB:System database=\\DreamMachine1\SocialShare1$\WgroupsCommonFolder\IncrediblySecureWgroup1.mdw"
I do not want the users to be able to find out where the back-end db is stored, so I do not want to connect using a network share that is mapped to a drive letter.
So, I am thinking of actually doing just that (...) - linking all tables to an empty (possible with read-only?) mdb in that location (mapped dummy drive) where they can't do/find anything.
Then, when the main form of the db opens automatically (MDE front-end with all options/access turned off other than accessing the form that automatically pops up, and the forms/reports that its buttons connect to.
I want to place code on that form's OnLoad event so that it relinks the tables from the dummy to \\DreamMachine1\SocialShare1$\Wgroup1Folder\Fantas ticProductivity1.mdb
Then, mind you, they are gonna make LOTS of money :-)
So here is where my insecurity problem starts:
Which currency should they use when earning all this money ? ;-)
I need to make sure that that particular connection is UP during the whole session this MDE is open. What is the best way to do that? For example I can disable closing that form, so the user must close the Access window to close it. Would that be sufficient to ensure that all other forms using CurrentDB() and queries pointing to the linked table nams will work?
Any particular way I should use/establish the cnn connection?
My other forms/modules use the following to establish recordsets:
Dim cnn As ADODB.Connection
Dim rst1 As ADODB.Recordset
Dim rst2 As ADODB.Recordset
Set cnn = CurrentProject.Connection
Set rst1 = New ADODB.Recordset
Set rst2 = New ADODB.Recordset
I am not very experienced with ADO/Recordsets:
Can I take the following part out of all forms/functions/etc., and only establish it with persistency in the main form?: (so that it can be used by all other functions etc,. without being re-established eash time, or is it actually best to keep closing and reopening the connection for each form/report?
Dim cnn As ADODB.Connection
If I should close/reopen, must I make sure they are using different connection names, or can they all in the private modules use cnn?
I have some public functions, I guess they must all use different connection names as they might be run simultaneously called by several independent (mostly) forms?
I guess there is something else to take into consideration as well.
And how does this sound regarding performance?
This goes for a handful of databases, none of them big, only some hundred, then some thousand records. Mostly 5 concurrent MDE users, soon 5 more querying and ADDING data through the browser (DataPages)... (I am considering moving the back-end onto a local MySQL for that.)
The db is not exessively used, all using Windows XP pro workstations, the DB backend residing on one of them, each having their own copy of the MDE.
Win-XP pro, Access 2002, ADO 2.7, DAO 3.6. English versions of apps/OS.