Renaming Tables in Acces

Hi there , can anyone tell me how I can rename an access
table trough oledb.
I always get an error.
I've tried
Dim strSQL As String
Dim m_conn As New ADODB.Connection
Set m_conn = New ADODB.Connection
m_conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Players.mdb;Persist Security Info=False"

m_conn.CursorLocation = adUseClient
m_conn.Open 'CardPlay
strSQL = "RENAME OLDPLAYER TO NewPlayer"
m_conn.Execute strSQL
Set m_conn = Nothing

error raised:Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

i've tried some other statements but all raising error
of error in sql statement;
Can anyone help me with a right statement?

I don't think you can get there from here. SQL, as far as I know, does not have a rename function.

You can open it in DAO and change the name by means of the QueryDef object, but I don't think you're going to get there from ADO.


Good Luck
DerFarm

[Serge]

Sure thing. Also add a reference to Microsoft ADO Ext. 2.x for DDL and Security (substitute "x" with the highest version you have installed). Then do something like this:

Private Sub Command1_Click()
    Dim i As Integer
    Dim cn As New ADODB.Connection
    Dim cat As New ADOX.Catalog
    
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\MyDatabase.mdb"
    
    With cat
        Set .ActiveConnection = cn
        .Tables("MyTable").Name = "MyNewTableName"
    End With
    
    Set cat = Nothing
    cn.Close
    Set cn = Nothing
End Sub

Thanks Guy's that's the code I was looking for.
Hope 2 help U some time