Access Runtime via USB Memory stick

[ghloid]

Hello all,

I've been reading up on developing my Access application via runtime so I can distribute it to users who may not have Access installed on their computer. I realize there are all sorts of neat tricks to getting the application to run in the runtime mode even if the user DOES have Access installed (via shortcut commands), and I realize the basics of how runtime licensing works (currently with the purchase of Visual Studio 2005 Tools for Office)

However, what I am so far UNABLE to find out is if I can get MY runtime scenario to work. I would like to bundle the app with the runtime files, but when the user runs the installer, I don't want the MDE to be placed on the users computer. The reason for this is security. The appication itself, and the data that will be put into it by each single user who uses the app should be portable, and not have to remain on the computer. I have found several fingerprint ID memory sticks, which are ideal for maintaining the security of the system. I could have the installer put on the stick, as well as the MDE file itself, and there would still be plenty of room left over (the app is made for a single user, and I don't see it ever getting bigger than about 10 MB at tops).

So, that's my question - can this runtime file be created so that the MDE is placed on the memory stick, or it just uses the MDE already on the stick, or some combination of those ideas?

If anyone has any thoughts on this, I'd appreciate hearing them.

Thanks!!

 

[Ed2020]

If I remember correctly you can specify the default installation path for your MS Access database file(s), but the users may well have the option to choose a different path. I'm also not sure how this would work if the database is split into a frontend/backend as the mapped drive letter of the USB drive may change dependent on what PC it is plugged into and what other drive letters are already taken.

If you're considering having the Access Runtime files installed on the USB stick as well then this could run into all kinds of similar problems, not to mention difficulties with the amount of space you'd need on your flash drive (I'm nto sure how much space the runtime files take up, but my guess is it's a lot) and potential performance problems.

In short:

Database files on the USB stick - definitely do-able.
Access runtime files on the USB stick - I wouldn't bother.

It has been a while since I've setup Access runtime database installation files though, and I haven't used the latest version, so others may have more valid opinions!

Ed Metcalfe.

Please do not feed the trolls.....

 

[ghloid]

Thanks for the response Ed,

That's pretty much what I thought (about the ability to install the MDE wherever you want with the installation package program). I was also thinking about the other issue of what exactly the drive letter would be for the stick (different on any computer that uses it). So, I remember seeing that the install package had some options to run batch files once the install was complete. I might have it install the MDE with the runtime files on the user's computer, then run a batch to then delete it, and have some sort of shortcut created to point to the mem stick MDE instead. Seems doable.

I also agree with the difficulty of the split database. There MAY be some way to have it split, and develop some code in the MDE to check for the backend database on the mem stick drive (after first driving code to find out exactly WHAT the memstick drive is). I think that's possible, just pretty complex. But if that gets too crazy, I may have to keep the full MDE with tables and everything (easiest, but maybe not the best approach).

Finally, I agree as well with keeping the runtime files on the computer. The installer file for all of this would be on the stick, but the runtime files would have to be installed locally (if that could be changed to the stick, I'm sure there'd be all kinds of issues).

Right now, I'm just wondering if you can make the installer file with some sort of options to install the runtime files ONLY if needed (if the user doesn't have Access). It would seem kind of pointless to install those regardless.

Once I actually get the software from MS, I'll start playing more with it. Thanks for the insight in the meantime though. It helps me decide what the best approach may be.

If you have any other ideas, I'd be happy to hear them.

ThankS!

 

[Ed2020]

You can retrieve the path of the frontend database using CurrentDB.Name

Assuming your backend will be in the same directory on the memory stick you can easily enough code the system to relink its tables every time it is opened. Let me know if you want some code.

Yes, you can have the installer only install Access runtime files if they are required (I think the user is given the option).

A few other points to ponder:

1. What will you do about database backups?
2. What will you do if the USB stick is lost (both in terms of getting your database and system back and in terms of data security)?
3. What will you do if the flash drive becomes corrupt or damaged?

Ed Metcalfe.

Please do not feed the trolls.....

 

[ghloid]

Thanks again for the valued insight here.

I DO in fact know of a little code snippet to get the current drive of the database (got it here):

Dim strPath As String
strPath = Application.CurrentProject.Path
MsgBox ("It is " & strPath)

Works nice, and I also have some code to relink tables, but I believe it uses menu commands. It was something I picked up awhile back that works, but I'm not sure if it would work on runtime. Therefore, if you DO have any code samples you can share, which show how to relink tables to a database once opened, I'd sure appreciate seeing that.

Also, I have a code snippet which grabs the drive of the memory stick. It is VERY good, but it currently will work to get the drive letter of ANY removable drive source on the computer. That could be bad if the user has some sort of USB CD drive or something. I can't remember if I've tested that scenario yet, but I will look into that some more.

I am somewhat worried about the corruption of the drive and database backups. Not too worried though, but it is an issue. With this application, the users will be securely emailing the data (via certificate ID), back to our office on a regular basis (usually at least once a week). If I can figure out how to keep this thing split on the stick, I think that will really help with keeping corruption possibilities low. Maybe I'll have the user email the entire data file to our office once and awhile too. The front end portion then could be reinstalled from another installer package on the stick (we use something like that now for this application), if it ever got corrupted.

As for the security of the system (since it's all on the stick), that will be ideal. We are purchasing fingerprint ID sticks which means only select users can even view what's on the stick. I believe that's even better than having a user install this on their personal computers (there are 21 people this would be distributed too, and all have different computer setups). I'm sure many of the users home PC's would be the most frequently used type of PC, which I'm also sure is completely unsecure (no logons). Therefore, the stick idea comes in nicely for these scenarios.

Still waiting on the software here, but I'm eager to start. I'll keep the posting going with any interesting issues I run into. Hopefully not many now (talking this out in the forum REALLY helps), so maybe just a summary on how things went then.

Thanks again for all the help. Let me know if you can provide some linking tables code. I can show you the code I have to get the USB mem stick drive then too (it's rather long).

Appreciate it!

 

[Ed2020]

ghloid,

The following code assumes that the database backend is stored in the same directory as the current frontend.

Run it from the frontend. Pass the file name of the database's backend (not the full file path) as a parameter.

Code will first delete all linked tables in the frontend and then relink all tables from the backend.

Apologies for lack of error handling - I've just knocked this together, so you'll have to add it yourself!

Public Sub LinkAll(ByVal strBackendName As String)
    Dim strBackendPath As String
    Dim ThisDB As DAO.Database
    Dim dbsBackend As DAO.Database
    Dim TDef As DAO.TableDef

    Set ThisDB = CurrentDb()

    For Each TDef In ThisDB.TableDefs
        If TDef.Connect <> "" Then 'Only drop linked tables
            ThisDB.Execute "DROP TABLE " & TDef.Name & ";", dbFailOnError
        End If
    Next TDef

    strBackendPath = Application.CurrentProject.Path & "\" & strBackendName
    
    Set dbsBackend = OpenDatabase(strBackendPath)
    
    For Each TDef In dbsBackend.TableDefs()
        If Left(TDef.Name, 4) <> "MSys" Then 'Ignore system tables
            DoCmd.TransferDatabase acLink, "Microsoft Access", dbsBackend.Name, acTable, TDef.Name, TDef.Name
        End If
    Next TDef
End Sub

Ed Metcalfe.

Please do not feed the trolls.....

 

[ghloid]

Ed,

Nice code there. Even ignoring the system tables -- nice.

Just curious, where could I pass a password parameter for the backend database? The backend has a password on opening (as does the front end). This was done as one more security measure, primarily in case the users had to email the backend to me via a normal email account.

I have a code snippet from my last routine that used:

tdf.Connect = ";DATABASE=" & strDatabase & ";pwd=" & "pwrd"

In this example the password was "pwrd" (as you can see).

I'm fairly certain that in your routine above, the password parameter could be supplied somehwere in that OpenDatabase method you are using. In looking at the help file for the method, seems the connect parameter is used for this as shown below:

"FoxPro 2.5; pwd=mypassword"

Looks like the first part would be the TYPE of database (FoxPro or ODBC or something). Guess I would have Access in that part. It's the "pwd" syntax that follows that definition which seems to be what I'm looking for. I've just started looking at that, but that sounds right. Don't know, I'll keep checking.

Thanks again for the help. Don't worry about the error handling, I'm pretty familiar with putting in tons of code for that . You've helped plenty so far.

I'll let you know how it goes when I actually start implementing more.

Thanks!

 

[Ed2020]

You need to specify it in the connect string.

Although the two arguments that proceed it ar optional you still need to pass them if you pass the connect string arg:

Public Sub LinkAll(ByVal strBackendName As String)
    Dim strBackendPath As String
    Dim ThisDB As DAO.Database
    Dim dbsBackend As DAO.Database
    Dim TDef As DAO.TableDef

    Set ThisDB = CurrentDb()

    For Each TDef In ThisDB.TableDefs
        If TDef.Connect <> "" Then 'Only drop linked tables
            ThisDB.Execute "DROP TABLE " & TDef.Name & ";", dbFailOnError
        End If
    Next TDef

    strBackendPath = Application.CurrentProject.Path & "\" & strBackendName
    
    Set dbsBackend = OpenDatabase(strBackendPath, False, False, ";PWD=fff")
    
    For Each TDef In dbsBackend.TableDefs()
        If Left(TDef.Name, 4) <> "MSys" Then 'Ignore system tables
            DoCmd.TransferDatabase acLink, "Microsoft Access", dbsBackend.Name, acTable, TDef.Name, TDef.Name
        End If
    Next TDef
End Sub

Ed Metcalfe.

Please do not feed the trolls.....

 

[Ed2020]

One other suggestion - I'd seriously consider setting full workgroup security on your system rather than just a database password.

Database password's don't provide particularly good security. They won't protect the design of your system in any way once the user is in, and all users will have the same level of access.

Ed Metcalfe.

Please do not feed the trolls.....