• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to Insert Data Into SQL Table Using Excel VBA.

Status
Not open for further replies.
Nitesh Khot

Nitesh Khot

Member
Hi..,

I am looking to insert data into SQL table using excel vba.

I don't have any Idea but how to do this????

Thanks,
Nitesh
 
If you have a lot of data, it's probably simpler to use DTS in SQL Server to load the data from a worksheet. Or is it just a few records?
 
Yes I have few records only to insert into sql on daily basis....

pls share me sample file if you have.

Thanks in advance..
 
Here's a very simple example:
Code: 
Sub WriteDataToSQLServer()

    Dim cn                    As Object
    Dim rs                    As Object
    Dim n                     As Long
    Dim strQuery              As String
   
    Set cn = CreateObject("ADODB.Connection")
   
    With cn
        .Provider = "sqloledb"
        .ConnectionString = "Data Source=server_name;Initial Catalog=database_name;Integrated Security=SSPI;"
        .Open
    End With
   
    strQuery = "Table name here"
   
    Set rs = CreateObject("ADODB.Recordset")
   
    With rs
        .Open strQuery, cn, 1
        For n = 1 To 10
            .AddNew
            .Fields("Field1").Value = Cells(n, "A").Value
            .Fields("Field2").Value = Cells(n, "B").Value

            .Update
        Next n
        .Close
    End With

    cn.Close
    Set rs = Nothing
    Set cn = Nothing
End Sub
 
Debaser said:
Sorry - forgot the default lock type is read only. Try:
Code: 
.Open strQuery, cn, 1, 3
Click to expand...
Awesome.....Work want i am looking....

If i want to update or delete any records from excel then what code to used to perform these action.

Thanks,
Nnitesh
 
Debaser said:
How would you determine which records to delete or update?
Click to expand...

Thanks for your Reply...

Using Two excel sheet update & delete macro in two diff module....If i want to update data then i cal use Update macro & if i want to delete data then I can use delete macro....

if you have code pls update me..

Thank you so much..

Nikh
 
That's not really what I meant - I meant how would the code determine which records to delete or update?

Essentially, you just execute a SQL command using the connection object with a WHERE clause:

Code: 
cn.Execute "UPDATE table_name Set [Field_name] = 'some new value' WHERE [some other field] = 'criteria'"
or:
Code: 
cn.Execute "DELETE FROM table_name WHERE [some other field] = 'criteria'"
 
Hello everybody!
I need readapt this code to work with an Azure SQL.
I've tried, without success.
I've a Excel Workbook with any sheets that I need update in my SQL db.
Thanks everybody and
i'll wait for responses.
 
andreoliveira
You should open a new thread for Your own challenge.
This thread is already few Years old
and
as You're read from Forum Rules
  • Start a new post every time you ask a question, even if the theme is similar.

    This thread is closed now.
 
Status
Not open for further replies.
Back
Top