SQL i VBA

Et eksempel

Adodc1.CommandType = adCmdText
Adodc1.RecordSource = \"SELECT navn, Tlf, aar FROM Tabel1 WHERE aar > 0 AND navn like \'Jens%\' order by navn asc\"
Adodc1.Refresh

Husk dette komponent

Microsoft ADO Data Control 6.0 (OLEDB)

Adodc1 er af typen ADODB.Recordset ???

Set adoConn=Createobject(\"adodb.connection\")

adoConn.Open connectionstring

Set adoRST=adoConn.Execute(\"Select * From table where test=\'123\')

While not (adoRST.BOF or adoRST.EOF)
  For i=0 To adoRst.Fields.Count-1 \'Fields collection er 0 indekseret
    Debug.Print adoRst.Fields(i).Value & vbTab;
  Next i
  Debug.Print \" \"
  adoRST.Movenext \'gå til næste række i recordsettet.
Wend

/Jennemaan

Jeg har dette fra microsofts help-page og det skulle kunne hjælpe dig.

Sub OpenRecordsetX()

    Dim wrkJet As Workspace
    Dim wrkODBC As Workspace
    Dim dbsNorthwind As Database
    Dim conPubs As Connection
    Dim rstTemp As Recordset
    Dim rstTemp2 As Recordset

    \' Open Microsoft Jet and ODBCDirect workspaces, Microsoft
    \' Jet database, and ODBCDirect connection.
    Set wrkJet = CreateWorkspace(\"\", \"admin\", \"\", dbUseJet)
    Set wrkODBC = CreateWorkspace(\"\", \"admin\", \"\", dbUseODBC)
    Set dbsNorthwind = wrkJet.OpenDatabase(\"Northwind.mdb\")
    Set conPubs = wrkODBC.OpenConnection(\"\", , , _
        \"ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers\")

    \' Open five different Recordset objects and display the
    \' contents of each.

    Debug.Print \"Opening forward-only-type recordset \" & _
        \"where the source is a QueryDef object...\"
    Set rstTemp = dbsNorthwind.OpenRecordset( _
        \"Ten Most Expensive Products\", dbOpenForwardOnly)
    OpenRecordsetOutput rstTemp

    Debug.Print \"Opening read-only dynaset-type \" & _
        \"recordset where the source is an SQL statement...\"
    Set rstTemp = dbsNorthwind.OpenRecordset( _
        \"SELECT * FROM Employees\", dbOpenDynaset, dbReadOnly)
    OpenRecordsetOutput rstTemp

    \' Use the Filter property to retrieve only certain
    \' records with the next OpenRecordset call.
    Debug.Print \"Opening recordset from existing \" & _
        \"Recordset object to filter records...\"
    rstTemp.Filter = \"LastName >= \'M\'\"
    Set rstTemp2 = rstTemp.OpenRecordset()
    OpenRecordsetOutput rstTemp2

    Debug.Print \"Opening dynamic-type recordset from \" & _
        \"an ODBC connection...\"
    Set rstTemp = conPubs.OpenRecordset( _
        \"SELECT * FROM stores\", dbOpenDynamic)
    OpenRecordsetOutput rstTemp

    \' Use the StillExecuting property to determine when the
    \' Recordset is ready for manipulation.
    Debug.Print \"Opening snapshot-type recordset based \" & _
        \"on asynchronous query to ODBC connection...\"
    Set rstTemp = conPubs.OpenRecordset(\"publishers\", _
        dbOpenSnapshot, dbRunAsync)
    Do While rstTemp.StillExecuting
        Debug.Print \"  [still executing...]\"
    Loop
    OpenRecordsetOutput rstTemp

    rstTemp.Close
    dbsNorthwind.Close
    conPubs.Close
    wrkJet.Close
    wrkODBC.Close

End Sub

Sub OpenRecordsetOutput(rstOutput As Recordset)

    \' Enumerate the specified Recordset object.
    With rstOutput
        Do While Not .EOF
            Debug.Print , .Fields(0), .Fields(1)
            .MoveNext
        Loop
    End With

End Sub




Jeg tror iøvrigt at man skal passe på med at kalde
noget table da det vist nok er et beskyttet ord.

Der findes jo bla. en SQL statement der hedder
ALTER TABLE ... så prøv at kalde den noget andet.
Carstenp