Migration to SQL Server

[MP]

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

 

[Tony Toews [MVP]]

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

 

[MP]

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..

 

[Tony Toews [MVP]]

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