Porting Access to SQL
-
We have a system which has been running on Access with built in access forms etc. We now want to (for obvious reasons) move the system over to SQL server.
I understand that porting the data should not be too diffecult a task, however, it would be nice if we could keep the front-end as well, instead of having to redo the whole thing! Is this possible, or is a complete redesign in order?
-
Depending on how you wrote your application will determine that unfortunately....
I would port the data to SQL and after manually fixing the keys, indexes etc. (unfortunately these are not upsizable) do the following:
Create a copy of the Access database and link in the tables from SQL. These will come in with name like dbo_tablename. I would delete the Access tables and rename the SQL ones to NOT have the dbo_ and TEST, TEST, TEST..
Probably will have a few gotchas from no indexes but should not be too bad
Good Hunting!AJ Ahrens
webmaster@kritter.net -
We have done a lot of upsizing of Access/JET to both Access/SQL and VB or VB.NET/SQL.
If the application is big, then it is not usually cost effective to re-write the front end in a different environment. However Access does not give you the fine-grained control that VB/.NET does so you can't usually wring out the last few drops of increase.
The main gotcha's are:
* Redesign any continuous Access forms to retrieve filtered records. We usually start a form with SELECT...... WHERE 1=0 (to retain Intellisense) then change the Recordsource on the fly (just changing the WHERE clause) when the user types into a search box or selects from a list.
* Sub-forms with parent/childs fields either Autonumber/Identity PK's that are used for data entry are a problem. SQL Server assigns Identity fields AFTER the record has been saved. Access assigns Autonums as soon as a record is opened for editing. In the subform scenario you may need to call a stored procedure to create the parent record before adding anything to the subforms (and return the PK back to access using scope_identity()).
* Boolean fields cannot contain null - you must assign a default of 1 or 0 in SQL Server.
* After migrating you may find that performance is not as good as you hoped - redesigning these areas using stored procedures usually solves the problem.
* Learn and use the SQL Profiler. It it a valuable tool to show you what communication is going on between Access and SQL Server.
* SQL pass-through queries can be useful (they bypass JET), but be aware they are read only.
* Use ADO in code not DAO.
HTH
David Saville
Aldex Software
-
Make sure you read the Microsoft Access Version 2002 Upsizing Tools whitepaper
-
Where do i find that?
Thanks to you guys for all your help by the way
-
I usually start looking for Access info at the Access web site http://office.microsoft.com
check out the Assistance Section and the Downloads section, there is alot of good stuff
However, the whitepaper is available from Knowledge Base as article 294407
-
One thing to watch out for: Our developers used access identity fields. This is normally fine, but they used randomly generating identity fields instead of sequential ones. SQL Server uses sequential identity values.
If you did use random identity values in access, and use that as a foreign key / query join anywhere, you might run into a problem. When you upsize to SQL Server, the identity column will start at the next available number. Since things were generated randomly, the next available number might be near the upper limit of what an integer column can handle. If you're not careful, you could find yourself unable to insert any more records. And, last I checked, Access 2000 doesn't know what a bigint column is. I'm not sure about Access XP or 2003 though.
Good luck.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply