Using INSERT INTO in Excel VBA

[Mattheq]

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

[Will Riley]

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 

[Mattheq]

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