"dbo_" Table Prefix...

[Mike Labosh]

I don't heavily use Access anymore, but it works well for a prototyping
environment for me, and it's soooo painless to print out table definitions
for SQL Server tables (SQL Server has no such feature)

One REALLY irritating thing though, is that whenever I link a SQL Server
table to Access, it prefixes the Access table name with the SQL "owner
name", dbo.

Anyone know of a way to make Access stop doing that?

I'm using Access 2003, in case it matters.

--
Peace & happy computing,

Mike Labosh, MCSD

"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane

 

[Guest]

Hi Mike,

I don't know how to prevent it, but I created a form with code behind a
command button to take care of the situation. Here it is. Note that this code
requires a reference to DAO:

Private Sub cmdRenameLinkedODBCTables_Click()
On Error GoTo ProcError

'Purpose: Rename linked ODBC tables, by removing the "dbo_" prefix.

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim strMsg As String
Dim strTitle As String
Dim i As Integer

Set db = CurrentDb

' Enumerate TableDefs collection.
For Each tdf In db.TableDefs
If Left$(tdf.Name, 4) = "dbo_" And Left$(tdf.Connect, 4) = "ODBC" Then
tdf.Name = Mid(tdf.Name, 5)
i = i + 1
'Debug.Print " " & tdf.Name
End If
Next tdf

strTitle = "Success..."

Select Case i
Case 0
strMsg = "There are no linked ODBC tables with the ""dbo_"" prefix
to rename."
strTitle = "No Tables To Rename..."
Case 1
strMsg = "One linked ODBC table was renamed."
Case Else
strMsg = i & " linked ODBC tables were renamed."
End Select

RefreshDatabaseWindow
MsgBox strMsg, vbInformation, strTitle

ExitProc:
On Error Resume Next
Set tdf = Nothing
db.Close
Set db = Nothing
Exit Sub

ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description & _
" in procedure RenameLinkedODBCTables.", vbCritical, _
"Error in cmdRenameLinkedODBCTables_Click Event Procedure..."
Resume ExitProc

End Sub




Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

I don't heavily use Access anymore, but it works well for a prototyping
environment for me, and it's soooo painless to print out table definitions
for SQL Server tables (SQL Server has no such feature)

One REALLY irritating thing though, is that whenever I link a SQL Server
table to Access, it prefixes the Access table name with the SQL "owner
name", dbo.

Anyone know of a way to make Access stop doing that?

I'm using Access 2003, in case it matters.

--
Peace & happy computing,

Mike Labosh, MCSD

"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane

 

[dbahooker]

just for the record, using an 'Access Data Project' you can print in
the same fashion using diagrams.. right?

and you can display them in a coulpe of different views.. i like it a
lot..

or i think that you can print the same way.. how were you printing the
table design view in MDB again??

thanks

 

[Mike Labosh]

just for the record, using an 'Access Data Project' you can print in

the same fashion using diagrams.. right?

and you can display them in a coulpe of different views.. i like it a
lot..

or i think that you can print the same way.. how were you printing the
table design view in MDB again??

Tools -> Analyze -> Documenter

Thanks, I'll try that out and poke around with it.
--
Peace & happy computing,

Mike Labosh, MCSD

"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane

 

[Mike Labosh]

AWWWWW!!!!! A .adp gets all the tables, views, SP's & UDF's right, but it
doesn't have that nifty documenter!

--
Peace & happy computing,

Mike Labosh, MCSD

"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane