Transform Access-application to SQL Server: difficult or easy? 1

[dirkg]

Hi all,

I have created an extended Access-application (a lot of table, code, forms and reports) and I would like to know what the effort it is to transform it to SQL server. I really have no idea how difficult or easy it is. I know that you can use SQL Server as backend and Access as frontend and then there is no problem since it are just the tables that are converted. However I have the following questions:

- How do I have to see SQL Server: is it more or less equal to Access but than more reliable and powerfull: can you also create forms, reports, modules in SQL Server? What are the main differences between Access and SQL Server apart from it's reliability?
- If you can also create Forms, reports and modules in SQL Server, how difficult is it to convert them from the access-version to SQL Server?
- What programming language is used in SQL Server? Is it also VBA and SQL?
- As an experienced Access-developper, will I need to learn a lot extra to obtain the same level of capabilities with SQL Server?

Thanks a lot in advance for your help!

Greetz,

Dirk

 

[SQLSister]

SQL Server has no user interface, since you already have extensive forms, code etc. in Access, I would convert the tables to SQL (which is much faster and can handle many more records than Access). Then link the tables in Access and your existing code should work. However, you will want to go through and see if you have any slowness. If you do, then change the queries to Stored procedures in SQL and then then call them programmincally in vba or by the use of passthough queries.

You can get away with no security and no backups in Access, but security is a requirement and backups are necessary to keep the size of the transaction log down as well as to provide the ability to recreate the database when necessary. You need to to read up on these subjects in Books on Line. You'll also need to learn about query optimization and perfomance tuning.

Lots of luck.

 

[dirkg]

Thx for the useful information!
Do you know if there would be any reliability advantage of converting my forms, reports and code to a Visual Basic executable program over continuing to use Access as a front-end? What front-end would you recommend?

 

[SQLSister]

I'd ceratinly use Access to start with; you'll have enough to do to learn SQL Server. You can use a VB front end, but if your Access one does everything you need to do, I wouldn't bother to change it unless you want to web-enable your application.