Excel to Microsoft SQL server using macros

If the UPDATE is being sent and there is no error then probaby the WHERE part of your UPDATE is not selecting the correct rows to update.

When I have saved the file on the machine where the server is installed, the same macro runs just fine and updates the table. It is only when I using the macro from another machine that the update doesnt work. The user is added the the Database with Full permissions, and I am using Windows Authentication.
How can the query be wrong if it works from the local/ server machine and not from another machine. (which is connected to internet and logged with right windows XP username/password.

Without seeing your Excel macro code I couldn't say, but there are many things that could be going wrong, especially if you are manually creating an UPDATE statement and running it.

For example, one thing that often goes wrong is that the regional settings don't match between computers. If this is not allowed for in the code, then the month and days get swapped in date functions and things stop working.


Can you definitely verify that the UPDATE statement is running from both machines? If you can capture both in Profiler, then take a close look at the statements and check that they match.

Not only update, but any of the INSERT, UPDATE, or DELETE do not work from another computer which is conected to the internet, and the Company's VPN.
My Excel Macros for simple data read (IMPORT BUTTON) and data delete (EXPORT BUTTON)
'----------------------------------------------------------------------------------------
Option Explicit
Dim cnPubs As ADODB.Connection
'----------------------------------------------------------------------------------------
Private Sub Import_Click()
' Create a connection object.
connect_adodb

' Create a recordset object.
Dim rsPubs As ADODB.Recordset
Set rsPubs = New ADODB.Recordset

With rsPubs
    ' Assign the Connection object.
    .ActiveConnection = cnPubs
    ' Extract the required records.
    .Open "SELECT * FROM table_name"
    ' Copy the records into cell A1 on Sheet1. Clear the cells before importing
    Sheet1.Range("D1:F15").Clear
    Sheet1.Range("D1").CopyFromRecordset rsPubs
    ' Tidy up
    .Close
End With

cnPubs.Close
Set rsPubs = Nothing
Set cnPubs = Nothing

End Sub
'----------------------------------------------------------------------------------------
Sub connect_adodb()
' Create a connection object.
Set cnPubs = New ADODB.Connection

' Provide the connection string.
Dim strConn As String

'Use the SQL Server OLE DB Provider.
strConn = "PROVIDER=SQLOLEDB;"

'Connect to the Pubs database on the local server.
strConn = strConn & "DATA SOURCE=aries-meeta;INITIAL CATALOG=current_db;"

'Use an integrated login.
strConn = strConn & " INTEGRATED SECURITY=sspi;"

'Now open the connection.
cnPubs.Open strConn
End Sub
'-------------------------------------------------------------------------------------------------
Private Sub Export_Click()

Dim cnn As New ADODB.Connection
Set cnn = New ADODB.Connection

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

cnn.ConnectionString = "Provider=SQLOLEDB.1;Initial Catalog=current_db;Data Source=aries-meeta;INTEGRATED SECURITY=sspi;"
cnn.Open

If cnn.State = adStateOpen Then
      'MsgBox "Connected!!!"
   Else
      MsgBox "Sorry. Not Connected."
   End If

cnn.Execute ("Delete FROM table_name where name='aries';")
Set rs = cnn.Execute("Select @@rowcount")
   
'Sheet1.Range("D1:F15").Clear
'Sheet1.Range("D1").CopyFromRecordset rs

MsgBox rs(0) & " rows deleted!!"

' Close the connection.
rs.Close

cnn.Close
Set cnn = Nothing

End Sub

##########################################################
When the Export button is clicked from another machine, the Profiler shows that the User Logged In with NT login name, and the delete command. How should I verify whether what happened after the command was fired, besides checking the database table. How do I see what is the error/ or why the rowcount that is deleted is always 0. The row is not deleted in the database.

I suggest actually copying the SQL out of Profiler, then pasting it into Query Analyzer and running it under both logins and see if they work.

It can't be a permissions thing because an error would be returned the Excel.

From memory there may be some information in the ADO connection object after you execute it.... an errors collection or something? It might be worth displaying that for the two test cases and see if anything is different.

I started Profiler on the machine which has the Microsoft SQL Server running.
I opened Excel on both the machines.
I updated the SQL Query in both Excel Export Macros for Update rather than delete  (UPDATE table_name set description = 'Aries' Where name = 'Meeta';
I clicked on the Export Button after saving the excel files from both the locations.
The Profiler showed the same query fired from both the excel sheets, But DOES NOT Update the one from the computer other than the server computer.

I still copied pasted the queries from both in the Query Analizer that I started on the server machine. It did update on both the SQLs that i copied pasted saying "1 row(s) affected".

The only difference in the two were the Audit Login and the NTUserName and LoginName while performing these queries.
                         Audit Login                                NTUserName                         LoginName
server:      -- network proticol: LPC                      admin_username                 domain1\admin_username
machine2: -- network protocol: TCP/IP                 user_name                         domain1\user_name

Please Suggest how should I see whats the problem. Is there anything in the SQL Profiler that can tell me whether why the Update was not completed. Maybe I am using wrong connectionstring? Is anything else needed for TCP/IP protocol? Have I posted this in wrong section? 2 days and no solution, seems waste of money to have registered for experts-exchange, or should I be more patient ? ;)

Please read previous posting too.
I forgot to comment about your third statement:
--From memory there may be some information in the ADO connection object after you execute it.... an errors
--collection or something? It might be worth displaying that for the two test cases and see if anything is different.

Can you suggest how should I see the information from memory in the ADO connection object after I execute it? where/ How can I see the error collection?

In Excel VBA editor, to see the error collection, you need to put a breakpoint in your code after you've run the update.

Then press View/Locals window

You'll get a window with a tree view, it lists all of your variables.

You can drill into your ADO connection object, and see if there are any errors in the error collection.


Alternatively you can write a bit of code that will spit out the errors collection to a msgbox. I prefer the locals window method just in case there is a bug in my display code.



Can you temporarily alter your update statement to NOT use a where clause and see what that does? (i.e. update every row)

-If it updates every row it tells us that the current 'where' clause is not matching any rows
-If it still update no rows, it tells us that there is still some kind of permissions issue.


Also regarding this:

>> still copied pasted the queries from both in the Query Analizer that I started on the server machine. It did update on both the SQLs that i copied pasted saying "1 row(s) affected".

Can you try it by logging in under each different user, i.e. domain1\admin_username  and  domain1\user_name and running the script.

-If it works it tells us it's an Excel/connectivity issue
-If it doesn't work it tells us it is a permissions issue


If it is a permissions issue its a strange one because an error should be raised to the Excel application saying 'UPDATE denied on xxx....'



Two more questions:

1. Are there any triggers on the table?
2. Are you updating a view?




Also, from your posts Im seeing two different queries:


UPDATE table_name set description = 'Aries' Where name = 'Meeta'


DELETE FROM table_name where name='aries';


In one case, "name" contains 'Meeta', in another, name contains 'aries'. There may be a good reason for this but I just wanted to point it out.

I added Breakpoint at
          Set rs = cnn.Execute("Select @@rowcount")
Here is something I saw in the View -> Locals window that struct me when I tried the update statement from a different computer with right Windows Authentication permissions.

- cnn
  -errors
   - item1
        : Description : "Inserts, updates & deletes are not permitted from Microsoft Office 200" : String
        : HelpContext : 0 : Long
        : HelpFile : "" : String
        : NativeError : 50000 : Long
        : Number : 0 : Long
        : Source : "Microsoft OLE DB Provider for SQL Server" : String
        : SQLState : "01000" : String


Regarding your questions:
1) There are no triggers on the table. 2) Not a view. This is a table.
Also, the update statements are all right.


When I ran the queries using another login (SQL Authentication) with full permissions from the Query Analizer from directly the server, I got this error:
Inserts, updates & deletes are not permitted from SQL Query Analyzer  

The Update Statement works with Windows Authentication on the server itself from query analyzer. Windows Authentication I assume is the server login itself.

Thus, the Inserts, Updates, Deletes Dont work from anywhere else, even MSAccess, from another computer :( Please help.

OK at least we have an error message now. Unfortunately I've never seen it before and find it very strange that it mentions the actual client that you are running from (i.e. query analyze and Office)


>> Windows Authentication I assume is the server login itself.

I just want to make sure we are speaking the same language.... windows authentication is whatever you are currently logged into windows as at the moment. When you log into the database using windows authentication, it uses your current windows user to decide what rights you have. I assume you log into windows on the server as the local administrator? By default that has full rights on SQL Server.


So correct me if I'm wrong, we have these three cases:

1. Updating from Excel on a remote machine using windows authentication results in "Inserts, updates & deletes are not permitted from Microsoft Office 200"  in the error collection.
2. Updating from Query Analyzer on the server using a SQL user results in: "Inserts, updates & deletes are not permitted from SQL Query Analyzer"
3. Updating from Query Analyzer on the server using Windows authentication (logged into the server as local admin) is successful.


So lets work backwards and firstly try to resolve case 2.



Can you open Enterprise Manager, go to Security/Logins then find the SQL account in there. Double click it.

On the 'Server Roles' tab is anything ticked?

On the database access tab, if you select your target database, is there anything ticked in the 'Permit in database role' section?



Lastly, I'm curious about the error message. Does it appear in the 'Messages' tab?

Does it look something like this:

Server: Msg 229, Level 14, State 5, Line 1
DELETE permission denied on object 'CallsTable', database 'Scratch', owner 'dbo'.

Can you paste the whole message in?


Unfortunately there is no simple answer to this (unless someone can recognise that error message), we'll just need to go through it until we resolve it.


It mght be worth googling that error message or some part of it.

>> I assume you log into windows on the server as the local administrator? By default that has full rights on SQL Server.
You are right
>> So correct me if I'm wrong, we have these three cases:
You are right in the three cases

>>Can you open Enterprise Manager, go to Security/Logins then find the SQL account in there. Double click it.
>>On the 'Server Roles' tab is anything ticked?
NO, Nothing is ticked

>> On the database access tab, if you select your target database, is there anything ticked in the 'Permit in database role' section?
in the 'Permit in database role' section, following are ticked
public, db_owner, db_accessaadmin, db_securityadmin, datasource_resource(which has Select, insert, update, delete permissions on the table I am trying to update)

Cannot found such error in Google or on experts exchange :(
it DOES NOT look something like:
>>Server: Msg 229, Level 14, State 5, Line 1
>>DELETE permission denied on object 'CallsTable', database 'Scratch', owner 'dbo'.

Should something be selected in Server Roles tab????
Please let me know what I need to do to debug this, sorry I was out of station for last few days and couldnt reply. I am back, please let me know any questions you have, I need to resolve this by tonite. Please help

This problem was never solved. I quit trying to Update Database using VB from another machine :(

OK. If you want you can post something in community support to retract the points allocation and delete the question.... no need to award me any points.