linking tables to different databases

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-

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]

Wow - this worked perfect.
I guess I have a ton of reading to do, really need to study up on
TableDefs.
Thank you for the starting place.

john-