It's been a while since I've played with any VB code (let alone VBA)  but
I'm having some basic problems getting started.  I've got the following code
snippet

Public Function test()
Dim objCmd As DoCmd

Set objCmd = New DoCmd

objCmd.runsql ("select * from Students")

End Function

Problem is I always get an error on the 'New DoCmd'  in that is says -
Runtime error 429: ActiveX component can not create object'

Is there something I need to add in to get this to work?

Also will the RunSQL method handle running entire scripts rather than
individual SQL queries?

thanks
jb

RunSQL is only used for action queries, such as deletions and appends:

Sub sDeleteRecords
    Dim db As Database
    Dim strSQL As String
    Set db=CurrentDB
    strSQL="DELETE * FROM [tblName]"
    db.Execute strSQL
    Set db=Nothing
End Sub

This will delete everything from tblName. If you want to Select data, then
you will need to use a recordset:

Sub sTestRecords
    Dim db As Database
    Dim rs As Recordset
    Dim strSQL As String
    Set db=CurrentDB
    strSQL="SELECT * FROM [tblName] WHERE [TextString]='Test'"
    Set rs=db.Openrecordset(strSQL)
'    Perform actions on records returned
    rs.Close
    Set rs=Nothing
    Set db=Nothing
End Sub

This will select all records with a value of 'Test' in the field TextString,
and then you can do whatever you want with them.

--
Jon

Here is an example of how I have used SQL in a project, this builds a SQL
statement based on values found in an input form and then sets the record source
property in the output form to the SQL statement. It may be a bit complicated, I
would prefer learning from something a little simpler, but good luck with it!

Private Sub CmdViewReport_Click()
    Dim StrSql As String
    Const Q = """"

    StrSql = "SELECT * FROM WesternData"
    DoCmd.OpenForm "FWesternData"

    'Build SQL statement for Part Number Search
    If Me.LabelTitle.Caption = "Part Number" Then
        If Len(Me.SearchStartSingle.Value) > 0 Then

            If Left(Me.SearchStartSingle.Value, 1) = "*" Or
Right(Me.SearchStartSingle.Value, 1) = "*" Then
                StrSql = "SELECT * from WesternData WHERE Part LIKE " & Q &
Me.SearchStartSingle.Value & Q
            Else
                StrSql = "SELECT * from WesternData WHERE Part = '" &
Me.SearchStartSingle.Value & "'"
            End If

            If Me.FrameSortOrder = 1 Then StrSql = StrSql + " order by Part"
            If Me.FrameSortOrder = 2 Then StrSql = StrSql + " order by Part
desc"

        End If
    End If

    'Build SQL statement for Customer Search
    If Me.LabelTitle.Caption = "By Customer" Then
        If Len(Me.SearchStartSingle.Value) > 0 Then

            If Left(Me.SearchStartSingle.Value, 1) = "*" Or
Right(Me.SearchStartSingle.Value, 1) = "*" Then
                StrSql = "SELECT * from WesternData WHERE To LIKE " & Q &
Me.SearchStartSingle.Value & Q
            Else
                StrSql = "SELECT * from WesternData WHERE To = '" &
Me.SearchStartSingle.Value & "'"
            End If

            If Me.FrameSortOrder = 1 Then StrSql = StrSql + " order by To"
            If Me.FrameSortOrder = 2 Then StrSql = StrSql + " order by To desc"

        End If
    End If

    'Build SQL statement for Customer and Date Search
    If Me.LabelTitle.Caption = "By Customer and Date Range" Then
        If Len(Me.SearchStartSingle.Value) > 0 Then

            If Left(Me.SearchStartSingle.Value, 1) = "*" Or
Right(Me.SearchStartSingle.Value, 1) = "*" Then
                StrSql = "SELECT * from WesternData WHERE To LIKE " & Q &
Me.SearchStartSingle.Value & Q
                StrSql = StrSql + "AND Date >= #" & Me.ActiveXStartSingle & "#
AND Date <= #" & Me.ActiveXStop & "#"
            Else
                StrSql = "SELECT * from WesternData WHERE To = '" &
Me.SearchStartSingle.Value & "'"
                StrSql = StrSql + "AND Date >= #" & Me.ActiveXStartSingle & "#
AND Date <= #" & Me.ActiveXStop & "#"
            End If

            If Me.FrameSortOrder = 1 Then StrSql = StrSql + " order by To"
            If Me.FrameSortOrder = 2 Then StrSql = StrSql + " order by To desc"
        End If
    End If

    Forms![FWesternData].RecordSource = StrSql
End Sub

--