Executing access queries from VB

[SmagO]

Hi all,
please leme knoe how to execute an access query from vb
I need to run a make table query...

sqlq = "SELECT ned_rpt_case_stats.* INTO temp " & _
" FROM ned_rpt_case_stats"

thats my query
now i always just put in a data structure from the toolbox and call it datSQL. then i go datsql.recordsource=sqlq...
but now i need the query to actually execute from vb so that the table in access gets created

thanks ina advance

[Jeff Carlin]

I'm not sure if I'm missing something here, but...

Assuming your database name is "MyDB", you can execute any valid SQL statement against the database as follows:

MyDB.Execute MySQLStatement

[SmagO]

thanks but...
please show me how to set the database up
i have always used record sources and dont know how to dim the database and then show where it is attached to

[TheBao]

Hi SmagO,

Please try this, I think it is what you are looking for:

Dim con As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset

Private Sub Command1_Click()
    Set cmd = New ADODB.Command
    With cmd
        .ActiveConnection = con
        .CommandType = adCmdUnknown
        
        'execute the Invoice query
        .CommandText = "Invoices"
        Set rs = .Execute
    End With
    
    'testing it
    Dim counter As Integer
    For counter = 0 To rs.Fields.Count - 1
        Debug.Print rs.Fields(counter).Value
    Next
End Sub

Private Sub Form_Load()
    'connect to Northwind access file
    Set con = New ADODB.Connection
    con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Microsoft Visual Studio\VB98\Nwind.mdb;Persist Security Info=False"
    con.Open
End Sub

Regards,
TheBao