Insert Values to SQLTable from VBA

Dinesh Santhalingam

8y913SQL

I need to insert a Values to the table in SQL Server from VBA .I tried Something I mentioned below .But I couldn't able to achieve my results .please guide me to solve this .I am using Exact Conn String in my Option even though its not functioning.

Sub To_SQL()
Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
Dim strSQL As String
'Create a new Connection object
Set conn = New ADODB.Connection
'Set the connection string
conn .ConnectionString = "Data Source=55.50.53.9;Initial Catalog=Management;Persist Security Info=True;User ID=licenceusr;Password=******"
'Open the Connection to the database
conn .Open **
conn .Execute "insert into MyTableName (firstname, lastname) values('""Dhini""','""Dhini""')"
'Create a new Command object
Set cmd = New ADODB.Command
End Sub()

Thanks in advance.

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke8y ago
Accepted answer
I should have spotted it before. You are using .NET connection string in VBA code. Obviously not going to work isn't it?

Go to connectionstrings.com and find the suitable one for your requirement / set-up.

https://www.connectionstrings.com/microsoft-sql-server-odbc-driver/
Dinesh Santhalingam8y ago
Err :

I dont want that double quotes.Sorry to included like that. I connected with Visual Studio for SQL Operations.
Nitin Sontakke8y ago
Wouldn't it he helpful for us if you share the error you are getting? Is this SQL Server? Are you already able to connect to the SQL Server from SQL Server Management Studio or any other way?

Also, do you really want those double quotes in database? I don't think so!

conn.Execute "insert into MyTableName (firstname, lastname) values('Dhini','Dhini')"