Switching ADO Connection from SQL Server to MS Access and Back Again
Hello,
I have an application built with a MS Access 2K3 front-end and a SQL 2K5 back-end. I have created an ADO data layer for all the connectivity, so there are no tables in my Access database whatsoever. This application was built with the understanding that all data would be stored on the server.
However, my client threw me quite the curve ball today. He also wants to be able to use the application when he is not on the network and can't connect to the server. So, my question is what is the best/easiest way to go about doing this? I've thought of a couple of possibilities:
1) Give the user a "Copy Local" button that manually goes through all the tables and refreshes the data to the local Access DB. Then, when a connection to the server is not available, change the connection string to point to the Access DB.
2) Build in some sort of linked table functionality?
Though i'm not sure how either of those will really work as there are some inherent data type (and other) differences between the two databases. All of my SQL statements are geared towards SQL Server right now, and I don't really fancy writing a second data layer to deal with the way MS Access deals with dates, booleans, LIKE statements, etc.
Any ideas?
Thanks,
-Torrwin
I have an application built with a MS Access 2K3 front-end and a SQL 2K5 back-end. I have created an ADO data layer for all the connectivity, so there are no tables in my Access database whatsoever. This application was built with the understanding that all data would be stored on the server.
However, my client threw me quite the curve ball today. He also wants to be able to use the application when he is not on the network and can't connect to the server. So, my question is what is the best/easiest way to go about doing this? I've thought of a couple of possibilities:
1) Give the user a "Copy Local" button that manually goes through all the tables and refreshes the data to the local Access DB. Then, when a connection to the server is not available, change the connection string to point to the Access DB.
2) Build in some sort of linked table functionality?
Though i'm not sure how either of those will really work as there are some inherent data type (and other) differences between the two databases. All of my SQL statements are geared towards SQL Server right now, and I don't really fancy writing a second data layer to deal with the way MS Access deals with dates, booleans, LIKE statements, etc.
Any ideas?
Thanks,
-Torrwin
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Have you considered exporting the SQL to something like SQL Express or SQL CE.
You can use the DB Publishing Wizard to get the structure of the database. Put it on the workstation. Then create an SSIS package, SPs or whatever to sync it back to the server.
You code the ADO/ connection strings into a table on the FE. Then whenever he wants to switch you have a button that re-points the ADO connections.
Microsoft SQL Server Database Publishing Wizard 1.1
http://www.microsoft.com/downloads/details.aspx?familyid=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en
SQL Server Compact 3.5
http://www.microsoft.com/Sqlserver/2005/en/us/compact.aspx
You can use the DB Publishing Wizard to get the structure of the database. Put it on the workstation. Then create an SSIS package, SPs or whatever to sync it back to the server.
You code the ADO/ connection strings into a table on the FE. Then whenever he wants to switch you have a button that re-points the ADO connections.
Microsoft SQL Server Database Publishing Wizard 1.1
http://www.microsoft.com/downloads/details.aspx?familyid=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en
SQL Server Compact 3.5
http://www.microsoft.com/Sqlserver/2005/en/us/compact.aspx
>That would be nice, but it's not an option. It has to be MS Access.
I think I really wasn't clear.
ms access as front-end, and the sql express behind via linked tables.
I think I really wasn't clear.
ms access as front-end, and the sql express behind via linked tables.
ASKER
I think I wasn't clear either. =P Thank you for suggesting that guys, it would definitely be a very easy way out.
However, installing a SQL Server (whichever version) on the user's local machine isn't an option I have. Somewhere around 50 (very non-technical) people will be using this app at various stages and I know managing all that is something the client doesn't want to do.
However, installing a SQL Server (whichever version) on the user's local machine isn't an option I have. Somewhere around 50 (very non-technical) people will be using this app at various stages and I know managing all that is something the client doesn't want to do.
sql express can be installed silently:
http://msdn.microsoft.com/en-us/library/ms143441(SQL.90).aspx
http://msdn.microsoft.com/en-us/library/ms143441(SQL.90).aspx
start /wait setup.exe /qb ADDLOCAL=SQL_Engine INSTANCENAME="OneExpress" SQLACCOUNT="<domain\user>" SQLPASSWORD="<strong password>" SQLBROWSERACCOUNT="<domain\user>" SQLBROWSERPASSWORD="<strong password>" SQLAUTOSTART=1 SQLBROWSERAUTOSTART=1
ASKER
Does anyone have a suggestion on how to successfully use MS Access as the temporary (read only) back end?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
>Using an Access BE is going to force you to recode or double code.
>The differences between the syntaxes is just enough that it is a pain in the butt.
Well, as I mentioned earlier, I thought as much. I just hoped someone might see it differently. =P
I'll leave this question open for a little while longer in case anyone can think of anything before awarding points. Otherwise, I'll just tell him he can't have it both ways. I'll either generate some reports he can print, or he can tether his cell phone to his laptop.
Thanks for the help,
-Torrwin
>The differences between the syntaxes is just enough that it is a pain in the butt.
Well, as I mentioned earlier, I thought as much. I just hoped someone might see it differently. =P
I'll leave this question open for a little while longer in case anyone can think of anything before awarding points. Otherwise, I'll just tell him he can't have it both ways. I'll either generate some reports he can print, or he can tether his cell phone to his laptop.
Thanks for the help,
-Torrwin
Wish there was a better solution for you.
Glad to be of assistance. May all your days get brighter and brighter.
Glad to be of assistance. May all your days get brighter and brighter.
<<Otherwise, I'll just tell him he can't have it both ways. >>
You really need to do that<g> Access is really not the tool with which to be implementing a n-tier design (because in a nut shell, you can't). If it was a little more flexible (something like VFP or .Net for example) and you *started* development with the idea that BE's might change (you did a true 3-tier design with business objects), then you could pull it off. But not at this point.
Personally, with Access, any time I have someone tell me they need something outside of the Office, I go the route Jim P suggested; remote in via Citrix. If you use Citrix Access Essentials, all you need is a browser and a internet connection; no VPN required even. It's works slick and is a drop dead easy setup. A heck of a lot more cost effective then handling something like this through the app.
I'd only go the route your trying to do it if it's something like field work where they needed the database in the field. Even that's starting to change though with broadband Internet access. And if it is something like this, then I would try very hard to persuade them that using SQL Express is a no-brainer and they should let you do it. Then it's simply a matter of changing the server your pointing to.
FWIW,
Jim.
You really need to do that<g> Access is really not the tool with which to be implementing a n-tier design (because in a nut shell, you can't). If it was a little more flexible (something like VFP or .Net for example) and you *started* development with the idea that BE's might change (you did a true 3-tier design with business objects), then you could pull it off. But not at this point.
Personally, with Access, any time I have someone tell me they need something outside of the Office, I go the route Jim P suggested; remote in via Citrix. If you use Citrix Access Essentials, all you need is a browser and a internet connection; no VPN required even. It's works slick and is a drop dead easy setup. A heck of a lot more cost effective then handling something like this through the app.
I'd only go the route your trying to do it if it's something like field work where they needed the database in the field. Even that's starting to change though with broadband Internet access. And if it is something like this, then I would try very hard to persuade them that using SQL Express is a no-brainer and they should let you do it. Then it's simply a matter of changing the server your pointing to.
FWIW,
Jim.
ASKER