CODEWITHSUNDEEP
databasems-accessvbaodbc
Andy Levesque
Andy Levesque

Reputation: 580

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:

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.

Clarification

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?

Upvotes: 0

Views: 2457

Answers (2)

Tim Lentine
Tim Lentine

Reputation: 7862

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.

Upvotes: 1

ChrisPadgham
ChrisPadgham

Reputation: 870

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

Upvotes: 2

Related Questions