Connectivity Options - selecting datasources on the fly
-
I currently have an application that connects to a local Access 2007 database. The application itself is written within Access (VBA) and uses DAO connectivity. I connect to the data source and work with linked tables (queries done in similar manner) so that the user interface and VBA code is separate from the data to promote sw maintainability. What is now desired is the ability for my user to quickly change between data sources. That is, the user can browse his local machine, select an .ACCDB file, and then change his data source for my app - not exiting out of the app. I currently have a kludgy way to do this (still all in DAO) that requires exiting out of the application for the change to take effect. Would an ODBC configuration provide an easier solution? In ODBC, you establish the connectivity ahead of time, BUT you have the ability to change the Database (data source) under User DSN, Configure, Select Database. FURTHERMORE, the data may migrate to a server-based repository (SQL Server, Oracle, etc...) This may/WILL dictate me switching to ADO. Any suggestion on how to simplify the selecting of databases? Thank you.
-
I currently have an application that connects to a local Access 2007 database. The application itself is written within Access (VBA) and uses DAO connectivity. I connect to the data source and work with linked tables (queries done in similar manner) so that the user interface and VBA code is separate from the data to promote sw maintainability. What is now desired is the ability for my user to quickly change between data sources. That is, the user can browse his local machine, select an .ACCDB file, and then change his data source for my app - not exiting out of the app. I currently have a kludgy way to do this (still all in DAO) that requires exiting out of the application for the change to take effect. Would an ODBC configuration provide an easier solution? In ODBC, you establish the connectivity ahead of time, BUT you have the ability to change the Database (data source) under User DSN, Configure, Select Database. FURTHERMORE, the data may migrate to a server-based repository (SQL Server, Oracle, etc...) This may/WILL dictate me switching to ADO. Any suggestion on how to simplify the selecting of databases? Thank you.
I would migrate to .net and make it DB agnostic using DBProviderFactories[^]. Then you can choose your poison in the app.config section.
Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions