question

VAer-4038 avatar image
0 Votes"
VAer-4038 asked Cathyji-msft commented

Excel VBA: Insert record into MS SQL database

The code worked fine with Sybase, now database has been moved to MS SQL, and it runs into error. It seems that it has issue to run INSERT INTO or UPDATE code.

What does error mean? object in the screenshot is table_name.

Thanks.


143916-ms-sql.jpg


 Set CnDev = CreateObject("ADODB.Connection")
 CnDev.Open ConnectionString
    
 TestQuery = "INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...)”
    
    
 Set TestRs = CreateObject("ADODB.Recordset")
            
 TestRs.Open TestQuery, CnDev
            
 Set TestRs = Nothing


sql-server-generaloffice-vba-dev
ms-sql.jpg (21.6 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @VAer-4038,

According to your error message, your login did not have permissions on this table. Please try the solution from Shalvin. If the solution resolved your issue, do "Accept Answer". If it is not work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

0 Votes 0 ·

1 Answer

Shalvin avatar image
1 Vote"
Shalvin answered Shalvin edited

Hi @VAer-4038 ,

There could be multiple reasons for this failure. The most common reason would be that the user you are using in connection string might not have INSERT permission. Please use the steps in grant-transact-sql to GRANT INSERT permission to the user. Even if user has permission, would suggest removing and them and granting again.

Use the following query to grant permission to a specific table.

 GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.table_name TO user_name;

Use the following query to grant write permission to all tables.

 EXEC sp_addrolemember N'db_datawriter', N'user_name';

Another reason for the error is that there could be a trigger on the table preventing insert. In that case, you may need to check with DBAs and act accordingly.

Please let us know if the issue is fixed after above steps.





5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.