moving access to database

[jlabre01]

Hi,

I'm in the process of moving my company's access 2003 database to sql server 2005, i plan on using the MS SSMA to do the transfer of tables and data. I just wanted to know if I have to install MS sql server on all the computers in order for people to have ability to use the database? (i plan on using the common way of doing this and using access as a frontend and sql as a backend)

jlabre01

 

[boblarson]

No, you don't install SQL Server on each computer. SQL Server is exactly that, a server. You can link your tables via ODBC connection, ADO code, etc. There is a steep learning curve so I think you might want to get some training first before just stepping off into the deep. The biggest thing that makes me think you need some training first is your question. Someone with SQL Server experience would not have asked that (not that it was bad to ask, mind you).

So, at the MINIMUM go through these tutorials here:
http://www.functionx.com/sqlserver/index.htm

 

[jlabre01]

hi,
So once the tables are linked from sql to access using the SSMA everyone can simply use the access database from the network and it will run fine?

 

[MSAccessRookie]

I am currently working on a project just like that, and both comments are true. You just create an ODBC Link to the server, and link the user front end to the SQL Server tables. I was under specific orders not to change the user MS Access Interface at all unless it could be avoided, and therefore from a user point of view, the new system looks and feels exactly the same as their existing one with three exceptions:

1. The back end is linked via ODBC to a Network SQL Server so different warning messages occur
2. Some of the queries were converted to SQL Server Views, which are up to 10 times faster.
3. File locking has nearly completely disappeared, and the "record locked" messages have disappeared

Boblarson is also right that there may be a learning curve involved. How steep it is depends on your background, but it will be there.