Post reply

Cannot use database with (ado) tag

  • March 10, 2005 at 7:15 am

    #66584

    Hi,

    A database designed for multiple users is not useable by one user due to the fact that he had the little (dbo) at the end of each object of the database (tables and queries).

    Is it due to SQL or Access and : How can I remove it ?

    Thanks for your help, I searched for hours in vain ...

    Cheers,

     

    Olivier

  • March 10, 2005 at 7:18 am

    #546361

    sorry title is not good ;')

    It's the (dbo) tag !!!!

  • March 10, 2005 at 8:23 am

    #546385

    I am a little confused. I'm making a lot of assumptions.

    I'm guessing here, he linked in the tables and all the tables now are named something like "dbo_table1", "dbo_table2", etc. But you designed the database to use "table1", "table2", etc.

    First of all he shouldn't have had to relink the tables. If it's a security issue of everyone having their own user id then you need to make the ODBC call use Windows Authentication mode instead of SQL authentication.

    If you are having them relink the tables you need to either redesign because by default when ACcess links SQL tables it puts "dbo_" in front of the table name.

    The other method is to build a function that auto renames.

    Public Function Auto_Rename_Tables()

Dim RS As Recordset
Dim SQL As String

SQL = "SELECT NAME " & _
        "FROM MSysObjects " & _
        "WHERE LEFT(NAME,4) = 'dbo_'"

Set RS = CurrentDb().OpenRecordset(SQL)

If RS.EOF = False Then
    RS.MoveFirst
    Do While Not RS.EOF
        DoCmd.Rename Mid(RS!Name, 5, 75), acTable, RS!Name
        RS.MoveNext
    Loop
End If
RS.Close

End Function

    Hope this helps.



    ----------------
    Jim P.

    A little bit of this and a little byte of that can cause bloatware.

  • March 10, 2005 at 9:17 am

    #546408

    Thanks for this Jim, can be helpful for another day but now the problem is that tables and queries are all with (dbo) written after the name.

    I assume that is a Access/SQL system or rights thing and i wished that somebody had the same problem solved.

    Olivier

  • March 10, 2005 at 9:31 am

    #546410

    Try this....it will take everything but the last 3 characters of the name.

    Public Function Auto_Rename_Tables()

Dim RS As Recordset
Dim SQL As String

SQL = "SELECT NAME " & _
        "FROM MSysObjects " & _
        "WHERE Right(NAME,3) = 'dbo_'"

Set RS = CurrentDb().OpenRecordset(SQL)

If RS.EOF = False Then
    RS.MoveFirst
    Do While Not RS.EOF
        DoCmd.Rename Left(RS!Name, Len(RS!Name)-3), acTable, RS!Name
        RS.MoveNext
    Loop
End If
RS.Close

End Function

    But I have never seen the dbo end up after the name. That is strange.



    ----------------
    Jim P.

    A little bit of this and a little byte of that can cause bloatware.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply