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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy