If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Migration to SQL Server
Hi everyone,
We have Desktop Application developed in Access 2003/2007. We have more than 50 clients using this application and now we want to convert back end to SQL Server while keeping Access as front end. I could move all the tables to SQL Server and link to MS Access but my question is we don't know the location of sql server at client's location, so it needs to be dynamic. My program should ask for server information and link all the tables. In addition, every time it should check if connection is valid or not and if not valid relink all the tables. In some instances we have 15 users working off same backend database, so need to take care of locking issues as well. Any guidance would be appriciated. Thanks, MP |
#2
|
|||
|
|||
Migration to SQL Server
MP wrote:
We have Desktop Application developed in Access 2003/2007. We have more than 50 clients using this application and now we want to convert back end to SQL Server while keeping Access as front end. I could move all the tables to SQL Server and link to MS Access but my question is we don't know the location of sql server at client's location, so it needs to be dynamic. My program should ask for server information and link all the tables. In addition, every time it should check if connection is valid or not and if not valid relink all the tables. You have this exact same problem with an MDB backend though. So whatever solution you have for MDBs should work with SQL Server. I like using an INI file in the same folder as the FE MDB/MDE. However I've never had a client who has needed to actually open the INI file. So I'm getting tempted to change that to the registry so it's one less file sitting around. Tony -- Tony Toews, Microsoft Access MVP Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ Granite Fleet Manager http://www.granitefleet.com/ |
#3
|
|||
|
|||
Migration to SQL Server
Thanks Tony. That helps. So you would ask user to specify all the connection
properties and then save the connection string on INI file or registry, correct? Currently, we keep server path in registry and check that every time if it's connected or not, if not then we relink all the tables..Unfortunately, when I did same thing with SQL Server, somehow my database was locked and I couln't access anything.. |
#4
|
|||
|
|||
Migration to SQL Server
MP wrote:
Thanks Tony. That helps. So you would ask user to specify all the connection properties and then save the connection string on INI file or registry, correct? Yes, but I don't know if anything needs to be saved other than the instance and name. Currently, we keep server path in registry and check that every time if it's connected or not, if not then we relink all the tables..Unfortunately, when I did same thing with SQL Server, somehow my database was locked and I couln't access anything.. Then that I don't know. I haven't done a lot of work with SQL Server. Tony -- Tony Toews, Microsoft Access MVP Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ Granite Fleet Manager http://www.granitefleet.com/ |
Thread Tools | |
Display Modes | |
|
|