Before I get into my actual problem, I should probably give you a little background. My office uses several tools to do its work (Access FE/SQL BE). A vendor maintains these databases and I am the only person in my office that has rights to the SQL box itself, but not to the security portion of it - they maintain that. I do all of the local reporting via an Access DB I created that has the SQL tables linked to it.
For the last 2 years, I have had a generic ID and password that I used to link the tables and everything has been fine. All of the users have a locally distributed FE of my reporting DB that drops and relinks the tables each time they open it. I just got word from the vendor that they are discontinuing these generic IDs, but I still have the same access I always have had.
I figured all I would have to do is change the connection in the tables to my ID and PW, but I can't get it to work on anyone else's machine with them logged in. When I try to open the tables, I get an access denied error that I don't get when I am logged in. I have even copied over the DSNs from my machine to other users' and I still can't get them to connect.
Here is the code I am using to connect. The only thing I have changed is instead of the generic ID and PW, I am using mine.
Option Compare Database
Public hConn As ADODB.Connection
Public HCmd As ADODB.Command
Public Function OpenSQLConnection()
Dim ServerName As String
Dim DatabaseName As String
Dim UserID As String
Dim PW As String
ServerName = "MyServer"
DatabaseName = "MyDatabase"
UserID = "MyID"
PW = "MyPW"
Set hConn = New ADODB.Connection
Set HCmd = New ADODB.Command
hConn.Provider = "sqloledb"
hConn.Properties("Data Source").Value = ServerName
hConn.Properties("Initial Catalog").Value = DatabaseName
hConn.Properties("User ID").Value = UserID
hConn.Properties("Password").Value = PW
DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;DRIVER=SQL Server;UID=MyID;DATABASE=MyDatabase;WSID=MyMachine Name;APP=Microsoft Open Database Connectivity;SERVER=MyServer;PWD=MyPW", acTable, "MyTbl", "MyTbl"
I have a feeling it is something simple that I am missing, but I have been playing with this constantly for the last couple of weeks and I can't get it to work.
It's possible they switched from SQL Server Authentication to Mixed Authentication. If so, then your Windows login is what determines what access you have to the server. Can you try to make another DSN that has nothing for UserID and PWD? Or, in your function, remove the UID and PWD variables and see if YOU can still access the server. Either way it will confirm that SQL Server is using the username and password you use for your network to authenticate you and not a login that is stored in SQL Server.
OK, I first tried stripping my ID and password out of the code above. Sure enough, it attached the table with no problem and I was able to access the data. I then tried creating a couple new DSNs to see if that would make a difference. I tried first using Windows authentication (doesn't ask for ID or PW) and that connected the table with no problem. I then tried to do it using SQL Server authentication and you lose the 'next' option when you erase user ID. I then tried with my user IDs and passwords as well as the generic ID and PW that I have been using (and still works for a little while) and all of those came back with the same 'Access denied' error message.
Any other ideas, 'cause I am stumped! I really appreciate your help thus far.
I have some linked tables to SQL Server in my database.
My method refreshes the tables in the access db and uses DAO as appose to ADO.
This code relates to the connection properties in your DSN, and runs through each table in the Access db, if it finds the tablename prefix for SQL Server, then it will refresh the link to the table.
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim strName As String
Set db = CurrentDb()
For Each tdf In db.TableDefs
If Not tdf.Name Like "MSys*" And Not tdf.Name Like "USys*" Then
strName = tdf.Name
If Left(strName, 4) = "dbo_" Then
tdf.Connect = "ODBC;" & _
"DRIVER=SQL Server;" & _
"UID=MyID;" & _
"PWD=MyPassword;" & _
"Database=MyDatabase;" & _
"SERVER=MyServer;" & _
Else: GoTo Skip
Set tdf = Nothing
Set db = Nothing
Just change the relevent connection properties to the same as your DSN connection properties.
The line Else: GoTo Skip is there because I also have tables locally in the Access db, which if it wasn't there would bomb out of refreshing the rest of the tables as it runs through.
The Left(strName, 4) = "dbo_" is the prefix for the SQL Server tables in Access.
I have this running in Form_Load on a startup form 'splash screen', when the db opens.
It sounds like an intermittent problem, correct? Sometimes it works and sometimes not. When does it start working again? When you shutdown and restart?
I have SQL Server 2005 and Access 2002. It had an annoying problem happening where I had to put in the password for the SQL Server service account after I shut the server down. The domain account used to start the SQL Server services would lose its privileges until I reentered the password. Then I found an article on Microsoft.com (which I can't find now) that is related to http://msdn2.microsoft.com/en-gb/library/ms676909.aspx (Granting Access Rights to the Service Logon Account). For a service account you need to add Logon as a service... to the account. This may or may not be appropriate in your case.
Thanks again for your replies! Kev - I tried the code you use and I still run into the same problem. It adds the link to the table just fine and I can get into them with no problems - as long as I am the one logged in. Unfortunately, when I try it on other users' machines using their logons (Windows, not SQL), It fails to connect the tables. "Connection failed. SQL Server Error 18456".
DC - Sorry for not being clearer. I am running A2K3 and SQL 2K. Actually, the problem is not intermittent. I have never been able to get the tables to connect on anyone's logon but mine. I have even copied my DSNs onto their machines and it still doesn't work. The only way I have ever been able to get it to work is when I use the generic ID and PW that was provided to me. I don't know enough about SQL Server to know what grants permissions and such. However, since I can get into the tables with my ID, I thought that using my ID and PW in the connection string would suffice. Apparently not!
Thanks again and I am always up for trying something else if you have any other ideas.
Hmm, I wonder if it could be to do with how SQL Server users are set up.
Are you able to access Enterprise Manager?
You might want to check out if the users are set up in Enterprise Manager, as I think you need to add all the users that access the database over the network in Enterprise Manager, this is under Security -> Users.
And then allow your users access to the database you are using to link to in your Access db. Either when you set the user up assign them a database, or you can do it when you go to the database in Enterprise Manager.
I used a generic SQL Server logon because I didn't want to have to set up lot's of users on the server.
DC - Yes, I am on a network and as long as I use my ID to login to any machine on our network, I can get the tables to connect without a problem.
Kev - I did notice a difference in Enterprise Manager. My ID is not in there, but my ID is part of a "Super User" Active Directory group. That group, in turn, show up in the Security > Logins as the type Windows Group. The generic ID and PW that I have always used shows up as Standard. I have noticed this before, but never really paid any attention to it. Not knowing that much about SQL Server, it never really set off any alarms before. Do you know what the difference means?
Sounds like the logins aren't set up in Enterprise Manager for users on the network that are accessing the database.
I think what is happening is the DSN connections you made connects to SQL Server, but unless a login is found in Enterprise Manager it denies access to the database you are linking to.
So since you are set up in a Windows Group which is specified in the Enterprise Manager logins it is allowing you access to the database, same goes for the generic login.
The Windows Group will be your network login, and the generic login which you said was 'Standard' is created on SQL Server and is not part of the network.
I think what you will need to do is set up a login for your users in SQL Server, to do this find out if the users of your Access db are all in a Windows Group/Role on the network.
If so, go into Enterprise Manager -> Security -> Logins, and create a new login based on that Windows Group/Role.
Under Windows Authentication select your Network Domain from the drop down list, and then click on the button next to the Name to select the Windows Group/Role.
Then it's just a matter of setting the rest of the settings you require such as; Default Database, Database Access, Server Roles.