Excel VBA: Insert record into MS SQL database

Question

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.

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  
  

Answer 1

Hi @VAer ,

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.