Solved

Can't Lock File, linking

Posted on 2000-05-15
10
943 Views
Last Modified: 2012-08-13
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
Comment
Question by:BrianWren
10 Comments
 
LVL 54

Expert Comment

by:nico5038
ID: 2812461
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
 
LVL 14

Expert Comment

by:mgrattan
ID: 2812562
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
 
LVL 9

Author Comment

by:BrianWren
ID: 2812675
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
 
LVL 14

Expert Comment

by:mgrattan
ID: 2812888
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
 
LVL 9

Author Comment

by:BrianWren
ID: 2813954
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 14

Expert Comment

by:mgrattan
ID: 2814180
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
 
LVL 9

Accepted Solution

by:
BrianWren earned 100 total points
ID: 2814252
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
 
LVL 14

Expert Comment

by:mgrattan
ID: 2814466
So, apparently you can set the dbAttachExclusive attribute in DAO but not the dbAttachedTable...interesting.

Good info.
0
 
LVL 9

Author Comment

by:BrianWren
ID: 2815155
Yeah, like I said above, these properties are Read/Write  _UNTIL_  the object is appended.

Apparently dbAttachedTable is meaningful only for ODBC attachments.
0
 
LVL 3

Expert Comment

by:darinw
ID: 2817627
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

705 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now