Solved

Can't Lock File, linking

Posted on 2000-05-15
10
990 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MS Access question 3 41
Trying to disallow duplicate entries in a text fiel on a form 3 18
Save Selections in MS Access 3 25
Copy all value in one form to another 4 39
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

679 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