Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    18

    Unanswered: SQL in VBA/Excel (other than importing)

    I know how to import data from a database into Excel in VBA, using SQL commands. Are there any ways to use other SQL commands, such as INSERT, UPDATE, etc. in VBA for Excel (my database is access if that matters)?

  2. #2
    Join Date
    Dec 2003
    Location
    Östersund Sweden
    Posts
    60
    Hi Viggy,

    You can only use SELECT, INSERT TO and UPDATE but not DELETE.

    Kind regards,
    Dennis
    Kind regards,
    Dennis

  3. #3
    Join Date
    Jan 2004
    Posts
    18
    But how do I use the INSERT TO and UPDATE commands in VBA. I know how to use SELECT (db.openrecordset...) but I can't figure out how to use INSERT TO or UPDATE.

    Thanks,
    Viggy

  4. #4
    Join Date
    Dec 2003
    Location
    Östersund Sweden
    Posts
    60
    Viggy,

    Below You find two sample-procedures. The $ in sheetname indicate that the sheet exist. The 8.0 has nothing to do with which version of XL You are running. "HDR=Yes" tells Excel that the first row include fieldnames.

    Don't forget to set a reference to the Microsoft ActiveX Data Object (AD) Library 2.5 or above.

    Code:
    Option Explicit  
    
    Sub Lägg_Till_Post_Arbetsblad() 
    Dim cnt As ADODB.Connection 
    Dim cmd As ADODB.Command  
    Dim stCon As String, stSQL As String 
    
     
    Set cnt = New ADODB.Connection 
    Set cmd = New ADODB.Command 
    
    stCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & vbCrLf _ 
             & "Data Source=E:\Arbetsmaterial\Datakalla.xls;" & vbCrLf _ 
             & "Extended Properties=""Excel 8.0;HDR=YES"";" 
    
     stSQL = "INSERT INTO [Data$] (Avdelning,Resultat,Budget) VALUES ('QQ', '10', '8')" 
    
    cnt.ConnectionString = stCon 
    cnt.Open 
    cmd.ActiveConnection = cnt 
    cmd.CommandText = stSQL 
    cmd.Execute 
    
    Set cmd = Nothing 
    cnt.Close 
    Set cnt = Nothing 
    
    End Sub 
    
    Sub Uppdatera_Post_Arbetsblad() 
    Dim cnt As ADODB.Connection 
    Dim cmd As ADODB.Command 
    Dim stCon As String, stSQL As String 
    
    Set cnt = New ADODB.Connection 
    Set cmd = New ADODB.Command 
    
    stCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & vbCrLf _ 
             & "Data Source=E:\Arbetsmaterial\Datakalla.xls;" & vbCrLf _ 
             & "Extended Properties=""Excel 8.0;HDR=YES"";" 
    
    
    stSQL = "UPDATE [Data$] SET Resultat=20 WHERE Avdelning = 'BB'" 
    
    
    cnt.ConnectionString = stCon 
    cnt.Open 
    cmd.ActiveConnection = cnt 
    cmd.CommandText = stSQL 
    cmd.Execute 
    
    Set cmd = Nothing 
    cnt.Close 
    Set cnt = Nothing 
    
    End Sub
    Some of the names and so on are in swedish but I believe You're capable of understand it.

    Kind regards,
    Dennis
    Kind regards,
    Dennis

Posting Permissions

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