[RESOLVED] Write Query Loop on Excel worksheet in VBA module

[todd.kauffman]

Is it possible to set up and write SQL query on a worksheet? loop thru the record set and insert data onto another worksheet in the same file. If so, please show me a sample snippet on how to set it up in the VBA modules.

[westconn1]

http://support.microsoft.com/kb/257819
check out this article from microsoft

[todd.kauffman]

I am getting an error when it gets to the oCm.Execute line for some reason. Can you please look at my code and see if I am missing something. I am using ADO 2.8 library refrences in the excel VBA module.

Dim Cn As ADODB.Connection '* Connection String
    Dim oCm As ADODB.Command '* Command Object
    Dim iRecAffected As Integer
            
'   On Error GoTo ADO_ERROR
    
    Set Cn = New ADODB.Connection
    
    DBPath = "S:\Common\DialerExtracts\KAUFFMAN\dialer_result_last6attempts.mdb"
    Cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & DBPath
    
    Cn.ConnectionTimeout = 40
    
    Cn.Open
    
    Set oCm = New ADODB.Command
    
    oCm.ActiveConnection = Cn
 
     SQL$ = "INSERT INTO tbl_results (record_num) VALUES (" & record_num & ")"
            
     oCm.CommandText = SQL$
    
      oCm.Execute

[todd.kauffman]

I figured out what was wrong. I was getting a primary key error, once I added in the error checking. Marked as resolved.