I have a couple of access databases that I am considering upsizing to sequel server db. Are their any major issues that I should be concerned with that any of you may have encountered? I know for one that the DAO code that I use will have to be re-written in ADO and that security for all objects will have to be reset. My plan is to use access as a front end to the db because of its RAD nature. Does sql db have any GUI features that allow you to build tables and relationships, forms and reports? jlitondo@gatecitysteel.com
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Any issues to consider when migrating from Access 2k to Sequel server 1
- Thread starter jlitondo
- Start date
SQL Enterprise Manager has a cool , albeit limited, GUI tool for designing tables and relations among tables.
You may experience some data conversion issues, especially when converting historical datetime data. SQL will not accept dates before 1753...
You may experience some data conversion issues, especially when converting historical datetime data. SQL will not accept dates before 1753...
The advantages of moving SQL Server far out way the disadvantages. You can manage the entire SQL Server with multiple databases all in the Enterprise Manager.
You can create tables, stored procedures, views, triggers all from Ent. Manager. You will be able to setup and schedule automated jobs to backup your databases and execute stored procs if you want to.
Security is better in SQL Server and multiple users acan access the same db at the same time unlike Access.
You will lose the reports and form features but you would gain Query Analyser, SQL Profiler and the Query Builder.
You can design db diagrams in sql server and create tables and fk constraints in the diagram which will in turn save those changes to the db.
John
You can create tables, stored procedures, views, triggers all from Ent. Manager. You will be able to setup and schedule automated jobs to backup your databases and execute stored procs if you want to.
Security is better in SQL Server and multiple users acan access the same db at the same time unlike Access.
You will lose the reports and form features but you would gain Query Analyser, SQL Profiler and the Query Builder.
You can design db diagrams in sql server and create tables and fk constraints in the diagram which will in turn save those changes to the db.
John
-
1
- #4
Advice from the Access conversion world:
Make a copy of your database before you do anything. Trust me on this!
In Access you can use the upsizing wizard to convert the tables to SQL server. Then you link the tables in Access and they will work just like Access tables if you wish. When you link the tables will come in with the owners name attached (usually dbo if you use the wizard)so Profiles becomes dbo_Profiles. Just go in and rename them and all your forms, queries, etc. will work. All your tables will need a unique field of some kind or you won't be able to update them through Access. Also, the datatypes will be different and you many need to check that the upsizing wizard really picked the type you need. Read about data types in Books on line.
Now comes the hard part. You will not gain the speed advantage unless you convert your queries to stored procedures. Since many Access programmers don't directly write SQL (Allowing the query by design to do it for them), this may be a whole new world for you. Get a good book on Stored procedure programming if it is. Also, even if you have written Access SQL statements, you need to know that Access SQL and T-SQL are different flavors of SQL and have some differences. So just copying the Access SQL code into a stored procedure won't do it in many cases. A few of the places where converting is a pain are cross tab queries and queries where you directly refernce an object on the form. These are much more complicated in SQL and you wll have to learn new methodologies to do them. On the other hand, T-SQL is much more powerful and you can actually perform multiple actions in one procedure. My advice on converting queries is to go through your site once you have linked the tables and see what is running slowly. Convert these queries first. (Once you have some concrete examples of what you want to change, we can help you find the syntax, but really read up about stored procedures first or the advice won't make any sense.)
Now you also have some problems, you may not have had with Access like database admin, backup, security (many Access databases do not use any security), triggers, data transforamtion services. Lots of new things to learn. Make sure you set up backups because your transaction logs (another new thing you didn't have in Access) will grow tremendously large and eat all your hard drive if you don't. Another fun issue is that any changes you make to the database structure in SQL server, require you to relink the tables in Access.
Oh yeah, one other thing, when you set up your connection string to SQL Server, do not use the sa password to connect to the database. Either let everyone use their windows username and password or create a special user for the connection (or an application role). ANd do not under anycircumstances leave the sa login with no password (unless you want to get hacked into!) sa is the system admin userid and has the rights to do anything to the database - do not let anyone but the dba have this password!
Hope this helps. You will need more info as you go along, but this should get you started on what to look at.
Judy
Make a copy of your database before you do anything. Trust me on this!
In Access you can use the upsizing wizard to convert the tables to SQL server. Then you link the tables in Access and they will work just like Access tables if you wish. When you link the tables will come in with the owners name attached (usually dbo if you use the wizard)so Profiles becomes dbo_Profiles. Just go in and rename them and all your forms, queries, etc. will work. All your tables will need a unique field of some kind or you won't be able to update them through Access. Also, the datatypes will be different and you many need to check that the upsizing wizard really picked the type you need. Read about data types in Books on line.
Now comes the hard part. You will not gain the speed advantage unless you convert your queries to stored procedures. Since many Access programmers don't directly write SQL (Allowing the query by design to do it for them), this may be a whole new world for you. Get a good book on Stored procedure programming if it is. Also, even if you have written Access SQL statements, you need to know that Access SQL and T-SQL are different flavors of SQL and have some differences. So just copying the Access SQL code into a stored procedure won't do it in many cases. A few of the places where converting is a pain are cross tab queries and queries where you directly refernce an object on the form. These are much more complicated in SQL and you wll have to learn new methodologies to do them. On the other hand, T-SQL is much more powerful and you can actually perform multiple actions in one procedure. My advice on converting queries is to go through your site once you have linked the tables and see what is running slowly. Convert these queries first. (Once you have some concrete examples of what you want to change, we can help you find the syntax, but really read up about stored procedures first or the advice won't make any sense.)
Now you also have some problems, you may not have had with Access like database admin, backup, security (many Access databases do not use any security), triggers, data transforamtion services. Lots of new things to learn. Make sure you set up backups because your transaction logs (another new thing you didn't have in Access) will grow tremendously large and eat all your hard drive if you don't. Another fun issue is that any changes you make to the database structure in SQL server, require you to relink the tables in Access.
Oh yeah, one other thing, when you set up your connection string to SQL Server, do not use the sa password to connect to the database. Either let everyone use their windows username and password or create a special user for the connection (or an application role). ANd do not under anycircumstances leave the sa login with no password (unless you want to get hacked into!) sa is the system admin userid and has the rights to do anything to the database - do not let anyone but the dba have this password!
Hope this helps. You will need more info as you go along, but this should get you started on what to look at.
Judy
- Thread starter
- #5
Great pointers, thanks guys. You're right sqlsister, I'm going to have to hit the books. The other thing I saw was that the upsizing wizard converts the .mdb to an .adp. I'm assuming the adp file can be converted back to an mdb if I need to? Are there any advantages of using one file over the other or is an adp format required for access to interact with sql server in this manner? Is a full blown vb front-end better to use than access?
timscronin
MIS
you might skip the upsizing wizard and use DTS inside of SQL Server. You can import the tables from there. The only gotcha on either way is to check for the datatypes after conversion. I have seen in some cases text fields changed to nvarchar which causes some problems with some front-ends. Also be aware of the date/time datatype in SQL Server is different from Access. And a recent gotcha I got with linked tables, there is a known SQL bugs that causes problems with updates, add a timestamp field (with the timestamp data type) to all tables to get around this bug
- Status
Similar threads
- Locked
- Question
