Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Location
    Huntington, IN
    Posts
    54

    Unanswered: ALTER remote table causing 3112 error

    Our db has a hotlink to one file of our accounting system. Upon opening the db, it copies key info from this large table to a smaller worktable for the db to use.

    After copying the data, we would have to run
    Code:
    'Application.CurrentDb.Execute "ALTER TABLE tblLineItem ADD CONSTRAINT 
    PrimaryKey PRIMARY KEY (ItemID)", dbFailOnError
    in order to set the primary key (critical for other functions/ relationships). So far so good.

    Now we've moved the tables to a backend database ("BE_DB") for multiple front-end users. The code was changed so that it copies the linked file data to the BE_DB.

    The following code was added to Alter the backend table for the primary key at the backend table:

    Code:
    Dim dbBackend As Database
    Dim strBackend As String
    Dim strDDL As String
     
    strBackend = "\\NetworkPublicFolder\InventoryManagement_be.accdb"
     
    Set dbBackend = OpenDatabase(strBackend)        
    strDDL = "ALTER TABLE tblLineItem ADD CONSTRAINT PrimaryKey PRIMARY KEY (ItemID)"
    dbBackend.Execute strDDL, dbFailOnError
    dbBackend.Close
    Set dbBackend = Nothing
    Okay, so this works perfectly EXCEPT that we immediately get error 3112 "no read permission" on other related tables. If the data is copied and only the remote Alter code is skipped, then no 3112 error.

    I know that 3112 is often a corruption issue, but if I skip this remote ALTER code, then everything works perfectly (except we lose the critical relationships). So a corruption issue doesn't seem to fit.

    It sounds like we're not opening/ closing the BE_DB quite right, but I can't see the error.

    Can any good soul see any flaw in this code?

    Thank you much!
    Matt Wilcox
    Casters & Wheels at www.apollocaster.com
    www.apollocaster.com/store

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I would try:
    Code:
        Dim dbBackend  As DAO.Database
        Set dbBackend = Application.OpenCurrentDatabase(strBackend, True)
        dbBackend.Execute strDDL, dbFailOnError
    Have a nice day!

  3. #3
    Join Date
    Mar 2004
    Location
    Huntington, IN
    Posts
    54
    Thanks Sinndho. I got a "Expected Function or Variable" error upon compile.

    Another solution to the original issue here would be to be able to run the MakeTable query without losing the Primary Key property on the id field of the new table. Is there a way of doing that? If that were possible, then I can avoid this horsing around trying to Alter Table of the new remote table.

    Thanks for any help.
    Matt Wilcox
    Casters & Wheels at www.apollocaster.com
    www.apollocaster.com/store

Posting Permissions

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