• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1039
  • Last Modified:

Can't Lock File, linking

When I use the menu system to get a particular table for linking, I have no troubles.

When I try to duplicate that in VBA, I get error number 3050, "Couldn't lock file."

Both

Sub LinkTables()
       Dim d As Database: Set d = CurrentDb
       Dim t As TableDef
       Set t = d.CreateTableDef("DIS_DOC", _
                                               dbAttachExclusive, _
                                               "DIS_DOC", _
                                               ";DATABASE=E:\DIS\Access\dis.mdb")
     ...


and

Sub LinkTables()

       Dim d As Database: Set d = CurrentDb
       Dim t As TableDef
       Set t = d.CreateTableDef("DIS_DOC", _
                                                dbAttachExclusive, _
                                                "DIS_DOC", _
                                                ";DATABASE=\\spssvr019\nonmtrd$\DIS\Access\dis.mdb")

     ...

What is different in the code compared to what the menu system does?  
I have opened the resultant linked table and the Attributes are = to the value of dbAttachedTable (1073741824).

But if I use that argument in my code, I get error number 3001, Invalid argument.

If I use dbAttachExclusive, I get the error that the table can't be locked, but no invalid argument error.

Brian
0
BrianWren
Asked:
BrianWren
1 Solution
 
nico5038Commented:
Brian,

I've only linked tables with ODBC but I just have some questions:
1) Is the share option of the database OK?
2) I can't follow the code. I would expect to open a new workspace that's connected to the database followed by a CreateTabledef for the table ?

Might be stupid questions but who knows what they trigger !

Nico
0
 
mgrattanCommented:
TableDef attributes are read-only for linked tables.  You need to create the tabledef first, then use the Connect method to actually make the tabledef a linked table.  Here's a sub that uses Northwind for an example:

Sub CreateLinkedTable()
    Dim dbLocal As Database
    Dim tbfNewAttached As TableDef
   
    Set dbLocal = CurrentDb()
    Set tbfNewAttached = dbLocal.CreateTableDef("MyEmp")
   
    With tbfNewAttached
      .Connect = ";database=<your path to Northwind>"
      .SourceTableName = "Employees"
    End With
   
    dbLocal.TableDefs.Append tbfNewAttached
End Sub
0
 
BrianWrenAuthor Commented:
mgrattan,

"TableDef attributes are read-only for linked tables" only after they are appended.  Prior to appending them, the attributes can be changed.

When I tried the way of doing it that you have proposed, (after all, if the property gives you trouble, try not addressing it, right?), I got an error indicating that there were no fields, amd sure enough, when I examined the TableDef object in the debug window, the fields.Count = 0, (in the top half, explorer appearing part...).

Brian
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
mgrattanCommented:
Brian,

I was referring to the Attribute dbAttachedTable.  It is read-only in DAO code according to the online help and the Knowledge Base articles on support.microsoft.com.  If you tried it exactly the way I proposed you should not have received such an error, since the TableDef is not actually appended to the TableDefs Collection until after the Connect string has been defined.  The .Connect method is the key to successfully linking a table in DAO but it must be done prior to using the Append method.

Using Northwind for my source database, I used the sub mentioned above (the following explicitly shows the path to Northwind.mdb) to successfully link the Employees table to a test database:

Sub CreateLinkedTable()
    Dim dbLocal As Database
    Dim tbfNewAttached As TableDef
     
    Set dbLocal = CurrentDb()
    Set tbfNewAttached = dbLocal.CreateTableDef("MyEmp")
     
    With tbfNewAttached
      .Connect = ";database=c:\program files\microsoft office\office\samples\northwind.mdb;pwd=admin"
      .SourceTableName = "Employees"
    End With
     
    dbLocal.TableDefs.Append tbfNewAttached
End Sub

I needed to also specify my password in this case, since Northwind is password-protected on my test system.

Are you using Access 2000?  If so, then something may have changed that is preventing this code from working the way it should (I'm on Access 97).

Mike.
0
 
BrianWrenAuthor Commented:
Sorry to say, the problem apparently is that though I had read/write permission for the directory where the database is, I had no permission to create a file.  Therefore I could not create the LDB file, hence, the 'Cannot Lock File' error.

I need to try this out, but if it now works with my modified permission, I provided my own answer...

0
 
mgrattanCommented:
That does seem to explain the 'cannot lock file error'.  However, it does not explain the 'invalid argument' error.  The explanation for that error I have already provided.

Your code still would not have created a linked table since it is trying to set the attachment attribute via DAO....
0
 
BrianWrenAuthor Commented:
This worked after the change in permissions:

    Dim d As Database
    Set d = CurrentDb

    Dim t As TableDef
    Set t = d.CreateTableDef("DIS_DOC", dbAttachExclusive, _
                             "DIS_DOC", _
                             ";DATABASE=E:\DIS\Access\dis.mdb")

    d.TableDefs.Append t



This also worked:

    Dim d As Database
    Set d = CurrentDb
   
    Dim t As TableDef

    Set t = d.TableDefs("DIS_DOC")
   
    t.Connect = ";DATABASE=\\spssvr019\nonmtrd$\DIS\Access\dis.mdb"
    t.RefreshLink



Brian
0
 
mgrattanCommented:
So, apparently you can set the dbAttachExclusive attribute in DAO but not the dbAttachedTable...interesting.

Good info.
0
 
BrianWrenAuthor Commented:
Yeah, like I said above, these properties are Read/Write  _UNTIL_  the object is appended.

Apparently dbAttachedTable is meaningful only for ODBC attachments.
0
 
darinwCommented:
Hello everyone,

I am moving this question to the PAQ.

-- I am accepting BrianWren's comment as an answer --

darinw
Customer Service
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now