Convert Access BackEnd Databse to MS SQL Server BackEnd Database,

[noorani]

Hi,
Has anyone tried to use MS Access as Front end on SQL server BackEnd Database? I would like to know more about it.
Can any one redirect me to some usefull links or give me
some advice about whether this is a good idea ore not.

i have started seriously thinking to convert my Access Backend database to Sql server database. because Access database so often crushed and some time it's not repairable. twice i have lost my data but luckily i had a backup copy. i have read information in Access help about upsizing my access database to Sql server but i wonder will my front end will work if i do so. or do i need to change all the code of my front end database. and tables in Backend.

Please guide me.

thank you,

 

[mmaddox]

I'm no expert, but I converted my Access back end to SQL by using the SQL Enterprise Manager, All Tasks, Import data wizard. Required only minor tweaking of data types and setting primary key.

Then, In Access I used the File, Get external data, Link Tables. Set up the ODBC to the SQL database first.

Finally, In Access Rename the linked tables to be the exact same name as they were when they were Access tables (get rid of the access tables first). Forms, reports, queries will not know the difference and will work.

You may need to re-establish relationships, or if you want to get more advanced, do the relationships and queries on the SQL server.

That was a quick and simple method for getting tables off of Access and on to SQL that worked OK for me.

 

[Terpsfan]

You should do a lot of planning BEFORE upsizing your database to SQL Server because you could end up slowing down your application even more. I would seriously consider buying the book Microsoft Access Developer's Guide to SQL Server by SAMS ISBN:0-672-31944-6. It's by far the best book I've seen on upsizing applications from Access to SQL Server. Just upsizing your tables to SQL Server and linking them is not enough. Linked tables usually mean bad performance. You'll have to consider using unbound forms instead of bound forms. You'll definitely want to get rid of the Jet layer so you want to refrain from using Linked Tables and think about using Pass through queries and stored procedures. I have found the best way to get the data into SQL Server is to use the Access Upsizing Wizard. It upsizes tables, queries, forms, reports, etc to a new Access Data Project, where you can administer the SQL database from there. Then the Upsizing Wizard will publish a report of what upsized and what didn't, along with an explanation of why it didn't. I hope this at least gives you some things to consider.