Were looking to migrate all our Access 97 databases to SQL 2000, so this is the scenerio. We want to use Access 97 as the front end and SQL 2000 as the backend (Which will hold the tables, stored procedures and security). We have approximataley 400 users using Windows 2000 Pro and all using Novell 5.1 as our file servers. So we are not authenticating to an NT environment but rather to SQL Server, we only hold 2 CPU license and don't really want to use ODBC.
What is the best method for Access 97 to connect to SQL 2000?
Can someone provide me an example of the method of doing the connection to the SQL 2000 in Access 97?
I understand it complex to have all your databases seperately for relationships and its easier to have then all in one database, what's your opinion?
Any opinions and ideas would greatly be appreciated, I really don't want to use Access '97 but its not my call. New project and they want me to make it work, I'm good with Access and have never really used SQL.
You have to use the linked table to SQL Server via ODBC connection if you want to use ACCESS as fronted end. You also can execute pass-through query to access SQL Server tables too.
If you know how to programming VB to access database, there is no much difference between accessing ACCESS database or SQL Server database. You can use ADO, DAO or OLEDB to connect and access SQL Server from Modules in Access.
In order to handle about 400 users, you can consider to use SQL Server database roles and application role.