?
Solved

Can't Lock File, linking

Posted on 2000-05-15
10
Medium Priority
?
1,017 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
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.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

741 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