remove dbo in ms-access

Posted on 2005-04-14
Last Modified: 2009-09-29

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!
Question by:osmjahir
    LVL 36

    Expert Comment

    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
    LVL 38

    Accepted Solution

    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
        Do Until RS.EOF
            DoCmd.Rename Mid(RS!name, 5, 100), acTable, RS!name
    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    In this article—a derivative of my blog post (—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now