Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / 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
    74

    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,676

    Re: Using INSERT INTO in Excel VBA

    You can providing you have the correct database admin permissions.

    An example of the code would be

    VB:
    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

    Web Presence:
    Personal: The Trouble With Data
    LinkedIn: Will Riley

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

    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, 10:38
  2. DB in Excel: How to insert data?
    By Balangan in forum EXCEL HELP
    Replies: 10
    Last Post: December 20th, 2003, 09:57
  3. Auto insert time in Pocket Excel
    By tech2000 in forum EXCEL HELP
    Replies: 2
    Last Post: December 1st, 2003, 12:46
  4. excel / insert / define
    By richard oldcorn in forum EXCEL HELP
    Replies: 1
    Last Post: May 14th, 2003, 12:31
  5. How to insert picture in Excel header
    By labrooks in forum EXCEL HELP
    Replies: 3
    Last Post: March 29th, 2003, 21: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