Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    rename table in remote db (Access 2000)

    can i rename table in remote database? I can do it in the current db, but i fail
    to do it in the remote db as follows

    Dim BEpath As String
    BEpath = "C:bestorebe.mdb"
    Set wsp = DAO.DBEngine.Workspaces(0)
    Set db = wsp.OpenDatabase("C:BEstoreBE.mdb", False, False, ";PWD=" & strPassWord)
    DoCmd.rename "temp", acTable, "orders"

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: rename table in remote db (Access 2000)

    The DAO method OpenDatabase only opens a database in code, not in the Access interface. The database running the code remains the active database. DoCmd.Rename is a command that works in the Access interface, hence in the active database. So you cannot use DoCmd.Rename to change the name of an object in a remote database opened by DAO.
    Instead, use the TableDefs collection of the remote database:

    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim BEpath As String
    BEpath = "C:bestorebe.mdb"
    Set dbs = OpenDatabase(BEPath, ...)
    Set tdf = dbs.TableDefs("Orders")
    tdf.Name = "Temp"
    Set tdf = Nothing
    dbs.Close
    Set dbs = Nothing

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: rename table in remote db (Access 2000)

    Thank you very much for your reply. I had big problems with that and i really appreciate the help, i couldn have done it alone

  4. #4
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: rename table in remote db (Access 2000)

    I presume that i also will not be able to perform in the remote database
    the maketable query? Any way to do it ?

    Public Function MakeNewTables()
    Dim sql As String
    sql = "SELECT * INTO orders FROM Temp "
    Db.Execute sql
    Db.Execute "CREATE INDEX PrimaryKey ON orders (orderID) WITH PRIMARY"
    End Function

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: rename table in remote db (Access 2000)

    Execute is a method of the DAO Database object, so you can run it in a remote database.

    If you use OpenDatabase to open a remote database as a DAO object, you can use all properties and methods of this object to manipulate the remote database. You can NOT use methods that work in the Access interface, such as DoCmd methods and RunCommand instructions, because those will always act on the active database, not on the remote database.

  6. #6
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: rename table in remote db (Access 2000)

    I get the message Object variable or with variable not set.Have i missed something?

    Public db As DAO.Database
    Public wsp As DAO.Workspace
    Public tdf As DAO.TableDef
    Public fld As DAO.Field
    Public prp As DAO.Property
    Public Const strPassWord = "secret"


    Dim BEpath As String
    BEpath = "C:bestorebe.mdb"
    Set db = wsp.OpenDatabase("C:BEstoreBE.mdb", False, False, ";PWD=" & strPassWord)
    Dim sql As String
    sql = "SELECT * INTO orders FROM Temp "
    db.Execute sql
    db.Execute "CREATE INDEX PrimaryKey ON orders (orderID) WITH PRIMARY"

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: rename table in remote db (Access 2000)

    You must either set wsp:

    Set wsp = DBEngine.Workspaces(0)
    Set db = wsp.OpenDatabase(...)

    or omit wsp entirely (when you run the code from within Access, it will use the default workspace if you don't specify it):

    Set db = OpenDatabase(...)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •