VBA and SQL

[Torsan]

I've been working with Visual Fox Pro, but I now have to convert to Access. I hope somebody can help me to get introduced of how to write a simple SQL in VBA. My book sucks I I can't make the connection work.

For testing purposes I need to know how to create a table based upon another table, including all the connection coding.

strSQL= "SELECT * INTO Table2 FROM Table1"

Thanks.

Example: When I run this script I get an error message: "Could not Use ";. File already in use"

Sub SQL()

Dim CurConn As New ADODB.Connection
Dim rst As New ADODB.Recordset

Set CurDB = CurrentDb
Set CurConn = New ADODB.Connection

With CurConn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " & CurDB.Name
.Open
End With

Set rst = New ADODB.Recordset
rst.CursorType = adOpenDynamic
rst.LockType = adLockOptimistic
Set rst = dbs.OpenRecordset("SELECT * FROM vm200501", dbOpenDynaset)
rst.Update
rst.Close

End Sub

 

[modest]

Try to take away the "Set rst = New ADODB.Recordset" and open the rst using rst.open strSQL, CurConn

Sub SQL()

    Dim CurConn As New ADODB.Connection
    Dim rst     As New ADODB.Recordset

    Set CurConn = New ADODB.Connection

    With CurConn
        .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " & CurrentDb.Name
        .Open
    End With

    rst.Open "SELECT * FROM vm200501", CurConn, adOpenDynamic, adLockOptimistic

    [COLOR="Green"]'retrieve your information here then when you're ready to close out 
    '  of the sub, close the recordset up (Access should do this automatically, but this forces it)[/COLOR]

    rst.Close
    Set rst = Nothing

End Sub

See if that works?