Access Backend to SQL Server
Righty Ho Peeps, I wanna hear from those people only who have tried the upgrade from Access to SQL Server
My app is currently Access Frontend/Access Backend, a mad project were requirements changed weekly, I have now ended up with about 42 tables, and its still growing!
It is already being used internationally but the requirement is now to allow more people, like delivery mgrs etc to use it.
I dont think Access can handle that, so I am looking to upgrade to SQL Server.
Ive already got someone in procurement to look at pricing.
My app uses DAO but the way I have written this is, I have created a wrapper class that contains all the DAO code, and so the code uses the wrapper functions. As part of the upgrade, I will rewrite it using ADO.
What I want to know is
1. How long would you expect to perform a migration like this, I am talking about moving the backend to the frontend. This is for someone who is familiar with DB design, but not SQL Server. I am more interested in the time taken to do a DB Migration, not code change. The way I have written the code, it requires very little tweaking, plus I know VBA/SQL quite well so there is no problem there.
2. There will be the same frontend but different instances of the database for different regions, can you have one SQL Server installation but different instances, different regions have their own data in effect.
3. What problems people have experienced, no matter how trivial
4. Cost issues, I understand to leave Access as the frontend, there is no client license to pay, anybody know about this (though Im trying my hardest to get the company to let me write it in dot net!)
5. Currently using linked tables are no longer required, but just confirm this, its time to move to ODBC, how else can Access talk to SQL Server
6. I take it SQL Server has user security, i.e only registered users can access DB
It may seem like a lot of questions, but this is primarily aimed at those who have already performed this upgrade so Im sure it wont take long for them to answer.
I need this info as I have to prepare a spec on costs of this procedure and Ive not tried an upgrade before.
Cheers
My app is currently Access Frontend/Access Backend, a mad project were requirements changed weekly, I have now ended up with about 42 tables, and its still growing!
It is already being used internationally but the requirement is now to allow more people, like delivery mgrs etc to use it.
I dont think Access can handle that, so I am looking to upgrade to SQL Server.
Ive already got someone in procurement to look at pricing.
My app uses DAO but the way I have written this is, I have created a wrapper class that contains all the DAO code, and so the code uses the wrapper functions. As part of the upgrade, I will rewrite it using ADO.
What I want to know is
1. How long would you expect to perform a migration like this, I am talking about moving the backend to the frontend. This is for someone who is familiar with DB design, but not SQL Server. I am more interested in the time taken to do a DB Migration, not code change. The way I have written the code, it requires very little tweaking, plus I know VBA/SQL quite well so there is no problem there.
2. There will be the same frontend but different instances of the database for different regions, can you have one SQL Server installation but different instances, different regions have their own data in effect.
3. What problems people have experienced, no matter how trivial
4. Cost issues, I understand to leave Access as the frontend, there is no client license to pay, anybody know about this (though Im trying my hardest to get the company to let me write it in dot net!)
5. Currently using linked tables are no longer required, but just confirm this, its time to move to ODBC, how else can Access talk to SQL Server
6. I take it SQL Server has user security, i.e only registered users can access DB
It may seem like a lot of questions, but this is primarily aimed at those who have already performed this upgrade so Im sure it wont take long for them to answer.
I need this info as I have to prepare a spec on costs of this procedure and Ive not tried an upgrade before.
Cheers
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Cheers lads
Thanks Rey for the link
Sid, just what I was looking for, thanks for that
Many thanks goes to Alan too,
AhmedBahgat, luckily I dont like the use of spaces in any of my fields/tablesnames, but its good to know that
As they are all useful contributions, I will split the points, pity I could give more
I think this post could be useful to other people as well, good to hear from people who have experienced it as you dont always find these problems in documentation.
Thanks again
Thanks Rey for the link
Sid, just what I was looking for, thanks for that
Many thanks goes to Alan too,
AhmedBahgat, luckily I dont like the use of spaces in any of my fields/tablesnames, but its good to know that
As they are all useful contributions, I will split the points, pity I could give more
I think this post could be useful to other people as well, good to hear from people who have experienced it as you dont always find these problems in documentation.
Thanks again
ASKER
Man, dishing out the points was hard, tried to give as much to Sid followed by Alan without being stingy but also giving to Rey and AhmedBahgat
Sorry if your disappointed, trust me, wasnt easy
Special thanks to Sid and Alan
Sorry if your disappointed, trust me, wasnt easy
Special thanks to Sid and Alan
ASKER
oops
when I said
pity I could give more
I meant
pity I couldnt give more
sorry
when I said
pity I could give more
I meant
pity I couldnt give more
sorry
you can remove the spaces from the tablenames,link the tables and then rename them to the old names in access...it won't rename them in mssql but queries and forms will still work...
which also brings me to another quirk of using the odbc link to mssql...if you make any changes to your table structure, you need to delete the linked table in the odbc'ed mdb and relink for the changes to show up...this onlyapplies to mdb's....adp's aren;t a problem...