August 14, 2013 at 9:52 pm
Hello,
I had a VBA code which has been running fine for the last one year and then it suddenly stopped working.
Basically in this code I am just executing a Stored Procedure on a SQL Server Database.
Also I checked and the readonly user does exist.
connStr = "Driver={SQL Server};Server=SQL20;Database=ABC;user id=readonly;password="
Dim Cmd1 As ADODB.Command
'Setup the connection to the database
Dim connection As ADODB.connection
Set connection = New ADODB.connection
connection.ConnectionString = connStr
'connection.ConnectionTimeout = 500
connection.Open
'Open recordset
Set Cmd1 = New ADODB.Command
Cmd1.ActiveConnection = connection
Cmd1.CommandText = "SPCreate"
Cmd1.CommandType = adCmdStoredProc
Cmd1.Parameters("@pileNum").Value = pileNum
Cmd1.Parameters("@startDate").Value = CDate(startDt)
Cmd1.Parameters("@endDate").Value = CDate(endDate)
Dim myRecordset As ADODB.Recordset
Cmd1.CommandTimeout = 500
Set myRecordset = Cmd1.Execute() - THIS IS WHERE IT GIVES THIS ERROR (Access to the remote server is denied because no login-mapping exists)
August 14, 2013 at 11:44 pm
Please make it clear if it involves a MS Access database (because you posted this in the MS Access forum) or a SQL Server database (like you mention in the post).
If it involves SQL:
- Check for any errors in the SQL Error log
- Check if the login you use still exists on the instance
- Check if the login has the correct permissions on the database
- Check if the login is connected to the user in the database: use {db_name};exec sp_change_users_login 'Report'
If it involves MS Access:
- Can you manually start MS Access, open the database and execute the command (using the user credentials you use in the code)
August 15, 2013 at 2:36 am
- Check for any errors in the SQL Error log - NO ERROR
- Check if the login you use still exists on the instance - DOES EXIST
- Check if the login has the correct permissions on the database - YES
- Check if the login is connected to the user in the database: use {db_name};exec sp_change_users_login 'Report' - Executed this command but it gave me only 2 usernames with UserID's.
August 15, 2013 at 2:43 am
khurmi.navpreet (8/15/2013)- Check if the login is connected to the user in the database: use {db_name};exec sp_change_users_login 'Report' - Executed this command but it gave me only 2 usernames with UserID's.
The 2 usernames with the UserID's indicate that a connection between the user and the login is lost. The connection is lost due to a mismatch between the UserID's of the Login and the corresponding User. Most probably a database restore is executed or the login is deleted and re-created.
You can use the following command for each listed username to re-connect them again:
use {db_name};exec sp_change_users_login 'Auto_fix', '{username}'
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply