Announcement

Collapse
No announcement yet.

Using INSERT INTO in Excel VBA

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Using INSERT INTO in Excel VBA

    Greetings,

    I am trying to establish a user form within Excel that can then use the INSERT INTO ... SQL statement to update a SQL 2000 database.

    I was able to use XL-Dennis' code from http://www.ozgrid.com/forum/showpost...33&postcount=7 to retrieve data from the database, but not insert.

    Is it possible to do the insert?


    Thanks in advance,

    Matthew Q
    Cheers,

    Matthew

  • #2
    Re: Using INSERT INTO in Excel VBA

    You can providing you have the correct database admin permissions.

    An example of the code would be


    Sub InsertARecord()
    Dim cnt As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim stCon As String, stSQL As String
    Set cnt = New ADODB.Connection
    Set rst = New ADODB.Recordset

    stCon = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=JOEY"
    cnt.ConnectionString = stCon
    stSQL = "INSERT INTO MyTable (Price)"
    stSQL = stSQL & "VALUES (500)"

    cnt.Open
    rst.Open stSQL, cnt, adOpenStatic, adLockReadOnly, adCmdText

    If CBool(rst.State And adStateOpen) = True Then rst.Close
    Set rst = Nothing
    If CBool(cnt.State And adStateOpen) = True Then cnt.Close
    Set cnt = Nothing

    End Sub
    Kind Regards, Will Riley

    LinkedIn: Will Riley

    Comment


    • #3
      Re: Using INSERT INTO in Excel VBA

      Will,

      Thanks very much for the example code. It did the job!

      The SQL statements are pretty straight forward, now all I have to do is learn the underlying VB to develop my application.


      Thanks once again.


      Matthew Q
      Cheers,

      Matthew

      Comment

      Working...
      X