?
Solved

remove dbo in ms-access

Posted on 2005-04-14
2
Medium Priority
?
2,018 Views
Last Modified: 2009-09-29
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!
0
Comment
Question by:osmjahir
2 Comments
 
LVL 36

Expert Comment

by:SidFishes
ID: 13787848
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
0
 
LVL 38

Accepted Solution

by:
Jim P. earned 80 total points
ID: 13791243
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.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

807 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