Front-end Access alternative relying on ODBC connection?
I've been tasked to take an Access 2007 application that relies on an ODBC connection and share it with other institutions with the same ODBC connection. Please forgive me if I don't communicate this very well. I'm not a developer, but have been tasked with this project since I've gotten it this far. I'm sure that's never happened before...
First I'll give a layout of our structure:
- I work for a college that shares an database via ODBC with 31 other schools.
- The system office that maintains the database for all campuses only allows us to access the read-only data through a VPN of a Common Access Point server (CAP) that then connects via ODBC
- The CAP server (the only location that can link to the ODBC) has Microsoft Office and does not have internet access.
- Each campus has a unique ODBC connection that requires relinking tables when the accdb is placed on their CAP server.
- With each launch of Access, the user must also login to the ODBC connection.
- The CAP server can can read-write on a network drive, but not vice-versa.
- We can safely assume that no other software can be installed on the CAP server, but files may be placed (which is why we can distribute an accdb file)
The Access application pulls student course activity from the ODBC and and applies logic to determine if/when the student stops attending all courses. At this time, this logic is a series of queries tied to a macro. The database then generates a report (with more information from the ODBC) of the students. An active tracking process is in place so a record can be cleared from the report unless a change occurs, which will then cause the record to reappear with the changes. This requires data to be stored locally as well since the ODBC is read-only. There are various forms and reports backed by VBA as well.
The goal is to package the software and distribute for launch at all other campuses. So far we've done small distribution by simply sending them the accdb file and having a button that launches the linked table manager. After initial distribution, I will continue developing the software and distributing updates, having to preserve the data locally stored in the accdb.
The catch is that I only have experience with Access and enough knowledge of VBA to be able to google solutions individually as they come up.
My question could be simple or complex, I'm not sure. Basically I'd like to know if there is a more appropriate approach other than what I've been doing: send accdb and the user copies and pastes the only table that needs to be carried over.
Would it be practical to convert the accdb to an executable with each version that is distributed? Is this even possible when the ODBC requires reconnecting and the ODBC is unique between campuses?
Requiring the end user to copy and paste the table(s) that store local data from one accdb file to another for each upgrade will eventually lead to data loss - someone somewhere will forget this step during an upgrade.
A more reliable approach would be to create a second accdb database. Call it "YourAppName_data.accdb" or something to that effect and either place it in the same directory as the front-end client or in a subdirectory called "Data". Link the tables from the "data" accdb file to the front-end client.
You can add startup code to your front-end client that can attempt to automatically relink these tables by looking for the data accdb file in the known location. If the program can't find it you can then prompt the user to find it for you. Incidentally, you should be able to do something similar for the ODBC tables as well. You can use code similar to what ChrisPadgham wrote in his answer for this step.
What you have done at this point is separate the data for the application (both the read-only data and the data each school needs to be able to maintain on their own) from the application front-end (the forms, queries, logic and reports).
This will make it easier to distribute updates to the front-end client. End users simply need to copy the front-end client to the correct directory, overwrite the existing file in the directory and run the program.
This will work, but it is still not as robust as it could be since anyone who has access to the CAP server could potentially delete the data files off of the server. (Hopefully each school takes regular backups of this machine to safeguard against data loss.)
As HansUp suggested, you might eventually be better served by moving the data stored in the Access accdb files to a SQL Server database at each location, which will offer a bit better control of who can access the information and would be a bit better at safeguarding the data since SQL Server database files are "locked" on the machine when the server is running. (This would prevent someone from accidentally deleting a file). The downside to SQL Server is that there is a learning curve and it would need to be installed at each school either on the CAP server or some other machine that the CAP server can access on the network. This might be something to work towards over time once you have better information to go off of.
You can add a relink button that loops through the tables in your database and reconnects them
dim tdf as tabledef dim db as database db = currentdb db.tabledefs.refresh for each tdf in db.tabledefs with tdf if len(.connect)>0 then ' this is a table that has a connect string if left$(.connect)="ODBC" then ' this is an ODBC connection .connect = newconnectstring .refreshlink end if end if end with next