For the first problem, you can use the following code:
Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Set dbCurr = CurrentDb()
For Each tdfCurr In dbCurr.TableDefs
If StrComp(Left$(tdfCurr.Name, 4), "dbo_", 1) = 0 Then
tdfCurr.Name = Mid$(tdfCurr.Name, 5)
End If
Next tdfCurr
Set dbCurr = Nothing
I'm assuming that the tables are actually like dbo_tProvider, not
dbo.tProvider
For the second, you could write a loop to delete the tables if that makes it
any easier:
Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim intLoop As Integer
Set dbCurr = CurrentDb()
For intLoop = (dbCurr.TableDefs.Count - 1) To 0 Step -1
Set tdfCurr = dbCurr.TableDefs(intLoop)
If Len(tdfCurr.Connect) > 0 Then
dbCurr.TableDefs.Delete tdfCurr.Name
End If
Next intLoop
Set dbCurr = Nothing
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
<jomonto@zeus.kern.org> wrote in message
news:1128377925.363963.265280@g47g2000cwa.googlegr oups.com...[color=blue]
> Hello-
>
> Here is my situation. I have multiple copies of the same database on
> our MS SQL server (same program, but differnet funding streams). I have
> an Access 2003 front end that I link to the SQL server for ad-hoc
> reports and queries. I link all the tables I need from a database (for
> instance: dbo.tProvider, dbo.tParents, etc) Then I rename the tables
> links in Access to just tParents, tProvider... this way I can have one
> query, one report - I just update the links to the new database
> whenever I need to change sources. Is there a clever way of renaming
> the 'linked' table names in Access - to remove the dbo. (Im using a
> painful macro right now)?
>
> ...and I have a new problem. So far this has worked out great. Because
> all the databases are the same and the table ownership has been dbo.
> But we recently added another copy, but the tables are
> NewDBA.tProvider, NewDBA.tParent, etc (diff ownership for reason beyond
> this message). So now insted of just updating the links - i have to
> delete the links and create new ones - then rename to the simple form
> (tProvider).
>
> Any suggestions??
>
> thanks,
> john-
>[/color]
