Unanswered: Security - Access front end and SQL Server back end
I need a piece of advice from those who have experience moving the back end to SQL server.
My scenario: The front and back end originally have been written as a standalone application in Access 2003. All vba code uses DAO model. The developer has put up an .mdw file with all groups and rights defined.
Now that project has been passed on to me, and I have to move the back end to SQL server, which I did just fine using Access wizard. Prior to moving the back end, I disabled security by joining the System.mdw. So, now neither back nor front ends are secured.
My question is: how do I set up security for the split application? Can I still use the old .mdw file to force users to login into the db or I need to create groups on the LAN and use Windows authentication (I'm just guessing here - it secures the back end only, correct?) I have no clue in what direction should I go next... Please help!!!!!!!!
Also, do I need to re-write VBA code in ADO? If yes, is it only true for the code where I use recordsets?
Do your users have access to SQL Server? If not, and I would think not, then only the Access front end needs to be secured. You should be able to use the existing .mdw file to do that.
I would very highly recommend changing all of the DAO code to ADO in your front end database. Because of a database that I have just inherited and some sever problems that occured, I have recently talked to some high level Microsoft Tech Support people. They made every effort to talk me into making the change from DAO to ADO. As soon as I can, I will be doing that, but in the mean time, I have a lot of "fires" to put out.
I was concerned about that .mdw file because I thought it only works for Jet, not ODBC.
As to transferring from DAO to ADO, I'm thinking how much code should be re-rewritten... Will this approach work: uncheck DAO reference and check ADO, then compile and see what is not working, and just fix those lines of code that are referenced by the debugger???
That approach will work, but what it will give you is the AREA where you need to change your code, not just the lines the compiler complains about. depending on how you currently have it coded and how consistent the code is, you might also be able to do a search on a part of the code to find the AREAs where you need to make your changes. By AREA, I mean there will be a replacement of more than one line, and you will end up with a little more code than you have started with. ADO takes more lines of code than DAO. It is not a one for one change.
I've modified the code to switch to ADO. Wherever I needed to open a recordset in code, I used
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM tbl", CurrentProject.Connection, adOpenStatic
Will that “currentproject.connection” parameter take care of the users’ info (id’s and passwords) according the .mdw’s settings or I need to create a connection object and pass id’s and passwords into it such as:
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.Open ConnectionString, userid, password
For now I don’t use .mdw with the SQL Server BE. When I’m done with all the FE tuning, I’m going to run the Upsizing Wizard again to move the appropriate objects to the SS, and when database is split, I'll join it to that .mdw again and then distribute the application to the users.
Do I need to make any server side security settings besides the ones defined in the .mdw? When creating data source, do I use Win authentication option (login using the network login ID) to let access to the server?