Delete Table In Other Dabase... 1

[vanillapod]

Hi, can anyone tell me how to do this?

Say i have two database files me1 and me2. Whilst in database me1 I want to be able to click a button on a form that will then delete an object in database me2.

I'm also interested in renaming objects in other .mdb files in this way.

Cheers in advance for any help!

 

[vanillapod]

Anybody?

 

[SBendBuckeye]

From Access97 Help.

Sub OpenDatabaseX()
Dim wrkJet As Workspace
Dim dbsNorthwind As Database
Dim dbsPubs As Database
Dim dbsPubs2 As Database
Dim dbsLoop As Database
Dim prpLoop As Property

' Create Microsoft Jet Workspace object.
Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)

' Open Database object from saved Microsoft Jet database
' for exclusive use.
MsgBox "Opening Northwind..."
Set dbsNorthwind = wrkJet.OpenDatabase("Northwind.mdb", _

True)

' Open read-only Database object based on information in
' the connect string.
MsgBox "Opening pubs..."
Set dbsPubs = wrkJet.OpenDatabase("Publishers", _
dbDriverNoPrompt, True, _
"ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers&quot

' Open read-only Database object by entering only the
' missing information in the ODBC Driver Manager dialog
' box.
MsgBox "Opening second copy of pubs..."
Set dbsPubs2 = wrkJet.OpenDatabase("Publishers", _

dbDriverCompleteRequired, True, _
"ODBC;DATABASE=pubs;DSN=Publishers;&quot

' Enumerate the Databases collection.
For Each dbsLoop In wrkJet.Databases
Debug.Print "Database properties for " & _
dbsLoop.Name & ":"

On Error Resume Next
' Enumerate the Properties collection of each Database
' object.
For Each prpLoop In dbsLoop.Properties
If prpLoop.Name = "Connection" Then
' Property actually returns a Connection object.

Debug.Print " Connection[.Name] = " & _
dbsLoop.Connection.Name
Else
Debug.Print " " & prpLoop.Name & " = " & _
prpLoop
End If
Next prpLoop
On Error GoTo 0

Next dbsLoop

dbsNorthwind.Close
dbsPubs.Close
dbsPubs2.Close
wrkJet.Close

End Sub

Hope this helps to at least get you started!

 

[vanillapod]

Wo! Thats more than I bargained for! I've had a go but get 'User Defined Type Not Defined' for the first part of code:

Dim wrkJet As Workspace

I'm using Access 2002, is that the problem?

 

[vanillapod]

Wo! Thats more than I bargained for! I've had a go but get 'User Defined Type Not Defined' for the first part of code:

Dim wrkJet As Workspace

I'm using Access 2002, is that the problem?

Cheers for the help by the way!

 

[vanillapod]

Ooops didn't have my references set properly...

any ideas on how I'd go about deleting an object in that database now?

 

[SBendBuckeye]

Here's an example that adds a new field to a table and then deletes it. Hopefully this will get you started.

Public Sub DeleteTableField()
Dim dbs2 As Database
Dim tdf As TableDef
Dim fld As Field
Set dbs2 = however you are currently doing things
Set tdf = dbs2.TableDefs("YourTableName&quot
Set fld = tdf.CreateField("MiddleInitial", dbText, 2)
tdf.Fields.Append fld
tdf.Fields.Refresh '<== See that it added it
tdf.Fields.Delete fld.Name
tdf.Fields.Refresh '<== See that it deleted it
dbs2.Close
Set dbs2 = Nothing
Set tdf = Nothing
End Sub

To delete an existing table field, do the following instead of the CreateField line above. Of course, you would get rid of the append line above as well.

Set fld = tdf.Fields(&quot;YourFieldName&quot
tdf.Fields.Delete fld.Name
tdf.Fields.Refresh

Also, if you are in single step mode and watching these changes in the locals window, make sure you close the tabledef object between lines of code or you could get an error.

Good Luck!

 

[vanillapod]

Thanks SBendBuckeye, I've just found the delete tables queries in the table / query etc defs code, but didn't realise the individual field part, thats great, made my day much better!

Cheers loads