Database Update in VBA

Question

Sub uoload_data()
    Dim s(40) As Integer
    Dim Row As Integer
    Dim i As Integer
    i = 0
    For Row = 7 To 39
        s(i) = Sheets("Data").Cells(Row, 5).Value
        i = i + 1
    Next
    Dim cn As Object
    Dim rs As Object
    Dim strSql As String
    Dim strConnection As String
    Dim AppPath As String
    Set cn = CreateObject("ADODB.Connection")
    AppPath = Application.ActiveWorkbook.Path
    strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=C:\Users\devi\Desktop\Ability.accdb;"
    cn.Open strConnection
    strSql = "INSERT INTO MyTable Values ('" & s(0) & " ',    
    '" & s(1) & " ','" & s(2) & " ','" & s(3) & " ' )"
    Set rs = cn.Execute(strSql)
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
End Sub

I have a excel sheet of 40 field. I would like to update all field to access database. while insert record into database i am using insert into statement. In the mean time i need to write all fields of array into insert into statement. So please help me out to compact statement.

Tim Williams · Accepted Answer · 2015-06-25 16:34:48Z

1

You can use Join() here

strSql = "INSERT INTO MyTable Values ('" & Join(s, "','") & "')"

The values in s() are integers, but you're wrapping the values in single-quotes, so are your DB columns text-type?

If they are numeric columns then you should drop the single-quotes.

answered Jun 25, 2015 at 16:34

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Developer_World Over a year ago

Yes my column is text type if a declare a variable st as string varibale & i would like to use join function with varibale st. in the mean time its showing error "Invalid Procedure call or argument" st = Join(s, "&")

Tim Williams Over a year ago

My bad - you will need to declare Dim s(40) As String

Shotgun Ninja Over a year ago

Doesn't VBA ADODB support stored procedures? Why not use those instead?

Collectives™ on Stack Overflow

Database Update in VBA

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related