I've got a pass thorugh query which gets a list of users from SQL (various servers) that's stopped working now we've upgraded from SQL2008 to SQL2012.
My original code was:-
Private Sub Server_Name_Change()
server = Me.Server_Name.Text
'----- Update connection string for query to get database list -----
CurrentDb.QueryDefs("DataBaseList").Connect = "ODBC;DRIVER=SQL Server;SERVER=" & server & ";DATABASE=Master"
Me.Database_Name = ""
'----- Update connection string for query to get latest users -----
CurrentDb.QueryDefs("UserList").Connect = "ODBC;DRIVER=SQL Server;SERVER=" & server & ";DATABASE=Master"
Me.ListOfUsers = ""
That has now stopped working.
I've tried various options found online but they don't work with all servers.
I've currently got:-
CurrentDb.QueryDefs("DataBaseList").Connect = "ODBC;driver=SQLNCLI11;SERVER=" & server & ";Initial Catalog=Master;Integrated Security=SSPI;"
but that doesn't seem to work at all.
I need something that will work with the following:-
SQL 2012 Enterprise
SQL 2012 Developer
SQL 2008 Standard
SQL 2012 Business Intelligence
I know the issue is with the connection string but I can't seem to get to the bottom of the issue.
Any help on this would be greatly appreciated.
This connection string:-
"ODBC;driver=SQL Server Native Client 11.0;SERVER=" & server & _
works on the SQL 2008 Standard servers and one of the servers running Business Intelligence editions (but not the others running the same edition).
Now I'm very confused.