remove dbo in ms-access

osmjahir
osmjahir used Ask the Experts™
on
Hi,

I have two problem when I work the sql data in ms-access.

1. My linked ms-access table with sql server, begins with dbo_, how to link the sql server with actual table name (for e.g. sql server table name is customer, the same when I link with ms-access, it shows dbo_customer in ms-access). How to show the actual table name in ms-access as appear in the sql server?

2. I'm wondering, the linked sql server table, I cannot change in the ms-access, it seems to be read only. I cannot make new entry, and cannot change the data also. Pls help me!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
you can rename the linked tables in access and remove the dbo_ with no problem

if you can't add or change data you probably need add a primary key to your tables
Most Valuable Expert 2014
Commented:
This will rename the tables for you.
Public Function Rename_SQL_Tables()

Dim SQL As String
Dim DB As database
Dim RS As Recordset

SQL = "SELECT Name " & _
      "FROM MSysObjects " & _
      "WHERE (((Left([Name],4))='dbo_'));"
     
Set DB = CurrentDb()
Set RS = DB.OpenRecordset(SQL)

If RS.EOF = False Then
    RS.MoveFirst
   
    Do Until RS.EOF
        DoCmd.Rename Mid(RS!name, 5, 100), acTable, RS!name
        RS.MoveNext
    Loop
    RS.Close
End If
End Function

Now as far as being able to edit data. The tables on the SQL Server need at least a unique index and preferably a primary key on every table.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial