Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 3 of 3

Thread: Using INSERT INTO in Excel VBA

  1. #1
    Join Date
    22nd November 2004
    Location
    Perth - Australia
    Posts
    75

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    28th January 2003
    Location
    Solihull - England
    Posts
    8,680

    Re: Using INSERT INTO in Excel VBA

    You can providing you have the correct database admin permissions.

    An example of the code would be

    [vba]
    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
    [/vba]
    Kind Regards, Will Riley

    LinkedIn: Will Riley

  3. #3
    Join Date
    22nd November 2004
    Location
    Perth - Australia
    Posts
    75

    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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. SELECT or INSERT INTO Oracle 2 Excel
    By stanl in forum EXCEL HELP
    Replies: 4
    Last Post: August 25th, 2004, 11:38
  2. DB in Excel: How to insert data?
    By Balangan in forum EXCEL HELP
    Replies: 10
    Last Post: December 20th, 2003, 10:57
  3. Auto insert time in Pocket Excel
    By tech2000 in forum EXCEL HELP
    Replies: 2
    Last Post: December 1st, 2003, 13:46
  4. excel / insert / define
    By richard oldcorn in forum EXCEL HELP
    Replies: 1
    Last Post: May 14th, 2003, 13:31
  5. How to insert picture in Excel header
    By labrooks in forum EXCEL HELP
    Replies: 3
    Last Post: March 29th, 2003, 22:11

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno