How to Rename Linked Tables?

ayfour

New member
Local time
Today, 15:41
Joined
Sep 24, 2009
Messages
9
Hi
Am using Access 2007 with linked tables in SQL Server 2008.

When I link the SQL Server tables, they automatically appear with the DBO prefix.

I have found the VBA code to remove the DBO which is all good.

However, what I would really like to do is use the database name as the prefix instead of DBO.

Anyone got any code that might help me do this?

Many thanks
 
Since you already have code that works why not use it. I would just modify the code to do what you want. It probably is a a simple matter of modifying a single line of code. Can not tell you for sure without seeing the code.
 
Below is the code I have to remove the DBO. But I do not know how to modify it to pick up the database name.

---
Public Sub RemoveDBO()
Dim tbl As TableDef
For Each tbl In CurrentDb.TableDefs
If Len(tbl.Connect) > 0 Then
tbl.Name = Replace(tbl.Name, "dbo_", "")
End If
Next
Set tbl = Nothing
End Sub
 
To help you learn VBA, I will point you to the spot.

A parameter for the Replace() function is what needs to be modified.

To learn about the Replace function, highlight the word Replace in the VBA code and press the F1 key.


You will find that the replace function can be very handy. It is worth learning how to use.
 
Thanks - have looked at the replace function, and I understand what it is about.

What I do not know is how to reference the database name.

Apologies for not being being explicit in my first post - I have a number of tables from different databases, so I will not be substituting the same database name for each table.

I would like to pick the database name up from the connection string, if that is an appropriate place? (So that the database name is specific to each table.)
 
Thanks - have looked at the replace function, and I understand what it is about.

What I do not know is how to reference the database name.

Apologies for not being being explicit in my first post - I have a number of tables from different databases, so I will not be substituting the same database name for each table.

I would like to pick the database name up from the connection string, if that is an appropriate place? (So that the database name is specific to each table.)

You would have to look at the .connection property to see if it has the information you want. If it does, then you could parse it out.


it will probably be after the DATABASE=
 

Users who are viewing this thread

Back
Top Bottom