I toyed around with the easiest and simplest way to add a connection string. With the multitude of problems that exist between the 32-bit and 64-bit connections to the databases (accdb), it was a challenge. And yes, if you are on a 64-bit system trying to build for an x86 platform with a 64-bit Microsoft Office installation, you will need to load the database engine for 64-bit and the driver package for Office 2007. A quick Google or two on the error that you get while trying to establish a connection for your dataset will reveal a multitude of postings that include the links.
A few links …
Office 2007 System Driver
Micorosft Access Database Engine
The latter two will help you to make the connection to the OLE for the database on a local machine. I suggest placing these in your Read Me file when publishing – just in case.
Let me just begin by saying that often, when using even Visual Studio, you do not want to include the database as part of the project file for obvious reasons. I wanted to install the “windows application” on numerous client stations and store the database securely on a server where multiple users could access. Obviously, in an ideal situation, you would simply use MySQL or transact-SQL, but I have a client that wishes to use the Access database because it is something that they can open and negotiate. I understand this completely. Consider too, this is another reason not to include the database in the build. This does, however, leave you with a dilemma. How do you enable each user to be able to change the connection string and create a database connection inside your “windows application”?
I included the connection in the Application Settings. I know what you are thinking. You cannot alter a Connection String that has Application scope. (Read here.) The user has no rights and this is a ReadOnly property. I managed a route around this and still protected my connection string.
Let me preface the next statement by letting you know that I do have user login and authentication restrictions for each “module”. I specifically set up roles using another database and a custom interface for allowing user access by not only read, write, and delete, but by restricting the areas in which each user is allowed to access.
In Application Settings, I created a string that has user scope. I set the Connection String value to that of the user string. The user string holds the path to the database. I thought that this was ingenious. While it is not the most secure method, consider that this is on a network and that users have their access to the server controlled via Active Directory already. This is not a cloud solution either. If I were going the route of a more publicly opened access, then I would have adapted this and used a configuration or environment variable to manage the connection string. That is not the case, being that this is intra-network access and not open to public use or inspection.
I, basically, left the security up to the network administrators in the end, to control the folder accesses. I only control the front end security, which is also saved in a user database. Neither the application or user authentication and role database are combined.
What do you think? Better solution? Easier solution? Security an issue?
Update: I noticed that I did not explain how I implemented the connection with respect to the Linq2SQL classes and data context.
When declaring the context …
Public dbContext As New DataClassesDataContext(My.Settings.NameOfSetting.ToString)
I created a form that I use for setting “configuration” that includes the custom strings needed so that they can be changed at whim. As well, the application has a configuration file that holds all settings. In this way, they are not directly hard coded into the application. The configuration file is uniquely named based on the data. In order to access any of these files, a user name and password must match the SQL database, which is locked up by the network administrator.