Shell object rename help

[Reyno]

Hi~
First off, sorry about the title; I couldn't really come up with anything that made sense.

Here's what I'm trying to do, I am trying to rename a table in one database from within another (there is a really good reason why I want to do it from somewhere other than the db that contains the object). Here is what I have so far in my code:


Private Sub cmdTest_Click()

Dim strSource As String 'the db that contains the table I want to rename

strSource = Application.CurrentProject.Path & "\test1.mdb"

'Shell the file
Shell "C:\Program Files\Microsoft Office\OFFICE11\msaccess.exe """ & strSource & "", vbNormalFocus

'Acknowledge security and open
SendKeys "NO", True

End Sub

of course I'm missing the part that would rename the object. I guess what I'm trying to do at this point is be able to run the docmd.rename command and have it execute in test1.mdb (the shelled db) as opposed to test2.mdb (the db that contains the code). Is this possible? How?

Thanks

 

[boblarson]

No, you're going about this in the wrong direction. You don't need to use Shell, you just need to use the Access Object Model.

 

[Reyno]

Thanks Bob...now how do I do that?

 

[Reyno]

Bob~
I did some research and this is what I have so far...my problem is that I don't know how to set rmCurrentTable to "tbl1" in rmSource (make sense?)

Private Sub cmdTest_Click()

Dim rmCatalog As ADOX.Catalog
Dim rmSource As String
Dim rmCurrentTable As ADOX.Table
Dim rmConnection As ADODB.Connection


rmSource = Application.CurrentProject.Path & "\test1.mdb"
Set rmConnection = New ADODB.Connection
rmConnection.Open "Provider=microsoft.jet.oledb.4.0;" & "data source=" & rmSource
Set rmCatalog = New ADOX.Catalog
Set rmCatalog.ActiveConnection = rmConnection


'Set rmCurrentTable to tbl1 in rmSource

'*************************************

' HOW?????

'*************************************



With rmCurrentTable
.Name = "tableone"
End With

Set rmCurrentTable = Nothing
Set rmCatalog = Nothing
rmConnection.Close


End Sub

 

[Reyno]

nevermind, I figured it out...

With rmCatalog.Tables
.Item("tbl1").Name = "tableone"
End With