DAO and Table Locking

Greetings,

I've written some code to create a relationship between table A and table B. 
The code looks something like this:

Set rel = db.CreateRelation("A_B")
rel.Table = "A"
rel.ForeignTable = "B"
Set fld = rel.CreateField("AID")
fld.ForeignName = "BID"
rel.Fields.Append fld
db.Relations.Append rel

This code works just fine except that a few lines later, I use DLookup() to 
find a row in table B, but I then get an error that table B is exclusively 
locked and cannot be accessed.

Can anyone clarify why table B has been locked and how to unlock it?

Thanks.

Jon
 

0
Jonathan
3/5/2010 6:54:17 AM
access.formscoding 7493 articles. 0 followers. Follow

6 Replies
1416 Views

Similar Articles

[PageSpeed] 6

On Thu, 4 Mar 2010 23:54:17 -0700, "Jonathan Wood"
<jwood@softcircuits.com> wrote:

You may need to add:
db.Tabledefs.Refresh

You know you can create relationships at design time as well, right?

-Tom.
Microsoft Access MVP


>Greetings,
>
>I've written some code to create a relationship between table A and table B. 
>The code looks something like this:
>
>Set rel = db.CreateRelation("A_B")
>rel.Table = "A"
>rel.ForeignTable = "B"
>Set fld = rel.CreateField("AID")
>fld.ForeignName = "BID"
>rel.Fields.Append fld
>db.Relations.Append rel
>
>This code works just fine except that a few lines later, I use DLookup() to 
>find a row in table B, but I then get an error that table B is exclusively 
>locked and cannot be accessed.
>
>Can anyone clarify why table B has been locked and how to unlock it?
>
>Thanks.
>
>Jon
> 
0
Tom
3/5/2010 2:04:07 PM
Tom van Stiphout wrote:

> You may need to add:
> db.Tabledefs.Refresh

I tried that before the offending code but still get the error: Run-time 
error '3008': The table 'B' is already opened exclusively by another user, 
or it is already open through the user interface and cannot be manipulated 
programmatically.

The table is not opened in Access.

> You know you can create relationships at design time as well, right?

Yes, of course.

-- 
Jonathan Wood
SoftCircuits Programming
http://www.softcircuits.com

>>Greetings,
>>
>>I've written some code to create a relationship between table A and table 
>>B.
>>The code looks something like this:
>>
>>Set rel = db.CreateRelation("A_B")
>>rel.Table = "A"
>>rel.ForeignTable = "B"
>>Set fld = rel.CreateField("AID")
>>fld.ForeignName = "BID"
>>rel.Fields.Append fld
>>db.Relations.Append rel
>>
>>This code works just fine except that a few lines later, I use DLookup() 
>>to
>>find a row in table B, but I then get an error that table B is exclusively
>>locked and cannot be accessed.
>>
>>Can anyone clarify why table B has been locked and how to unlock it?
>>
>>Thanks.
>>
>>Jon
>> 
0
Jonathan
3/5/2010 2:49:32 PM
On Fri, 5 Mar 2010 07:49:32 -0700, "Jonathan Wood"
<jwood@softcircuits.com> wrote:

OK, this peeks my interest. Can you email me a sample program,
stripped down to the bare essentials?
My spam trap should be easily avoidable by humans.

-Tom.
Microsoft Access MVP


>Tom van Stiphout wrote:
>
>> You may need to add:
>> db.Tabledefs.Refresh
>
>I tried that before the offending code but still get the error: Run-time 
>error '3008': The table 'B' is already opened exclusively by another user, 
>or it is already open through the user interface and cannot be manipulated 
>programmatically.
>
>The table is not opened in Access.
>
>> You know you can create relationships at design time as well, right?
>
>Yes, of course.
0
Tom
3/6/2010 3:19:26 AM
"Jonathan Wood" <jwood@softcircuits.com> wrote in message 
news:e0uUwCDvKHA.6124@TK2MSFTNGP04.phx.gbl...
> I've written some code to create a relationship between table A and table 
> B. The code looks something like this:
>
> Set rel = db.CreateRelation("A_B")
> rel.Table = "A"
> rel.ForeignTable = "B"
> Set fld = rel.CreateField("AID")
> fld.ForeignName = "BID"
> rel.Fields.Append fld
> db.Relations.Append rel
>
> This code works just fine except that a few lines later, I use DLookup() 
> to find a row in table B, but I then get an error that table B is 
> exclusively locked and cannot be accessed.

Here is the function I use for creating new relationships. I've always been 
able to continue working in the db after running this code.

Public Function pjsCreateRelationship( _
 dbData As DAO.Database, _
 strRelationName As String, _
 strParentTable As String, _
 strChildTable As String, _
 astrFieldNamesPrimary() As String, _
 astrFieldNamesForeign() As String, _
 Optional fCascadeDelete As Boolean = False, _
 Optional fCascadeUpdate As Boolean = False _
) As Boolean
On Error GoTo ErrorHandler
    Dim fSuccess As Boolean, relTemp As DAO.Relation, _
     lngLoop As Long, lngAttributes As Long

    'Make sure we were passed compatible field name arrays
    If LBound(astrFieldNamesPrimary) = 0 And LBound(astrFieldNamesForeign) = 
0 _
     And UBound(astrFieldNamesPrimary) = UBound(astrFieldNamesForeign) Then
        fSuccess = True     'Assume we succeed
        'See if relationship name already exists
        Set relTemp = dbData.Relations(strRelationName)
    Else    'Bad calling parameters
        Err.Raise Number:=9999, Description:="Programmer Error: Invalid 
calling parameters"
    End If

    If relTemp Is Nothing Then  'Return here from error handler
        With dbData
            lngAttributes = IIf(fCascadeDelete, dbRelationDeleteCascade, 0) 
+ _
                    IIf(fCascadeUpdate, dbRelationUpdateCascade, 0)
            Set relTemp = .CreateRelation(strRelationName, strParentTable, 
strChildTable, lngAttributes)

            With relTemp
                For lngLoop = 0 To UBound(astrFieldNamesPrimary)
                    .Fields.Append 
..CreateField(astrFieldNamesPrimary(lngLoop))
                    .Fields(lngLoop).ForeignName = 
astrFieldNamesForeign(lngLoop)
                Next lngLoop
            End With
            .Relations.Append relTemp
            .Relations.Refresh
        End With
        CurrentDb.Relations.Refresh
    Else    'Could verify the existing relationship has the same attributes 
and fields
    End If

ExitHandler:
On Error Resume Next
    pjsCreateRelationship = fSuccess
    Set relTemp = Nothing
    Exit Function

ErrorHandler:
    Select Case Err.Number
    Case 3265   'Name doesn't exist in this collection
        Resume Next
    Case Else
        'Your error handling code
        fSuccess = False
    End Select
    Resume ExitHandler
    Resume
End Function
 

0
Paul
3/6/2010 2:09:39 PM
Tom van Stiphout wrote:

> OK, this peeks my interest. Can you email me a sample program,
> stripped down to the bare essentials?
> My spam trap should be easily avoidable by humans.

Thanks, I think I may have resolved this (and I can't really send it to you 
without retyping everything--including the data--because this work is being 
done over the Internet using VPN and Remote Desktop)

Basically, it's the code I posted followed by the DLookup() call that causes 
the error. However, there is one more element: I was creating a transaction 
(calling BeginTrans). If I don't call BeginTrans, I do not get the error.

So it seems that Access won't let me access a table with pending edits 
awaiting CommitTrans or a Rollback.

Does that seem right?

Thanks.

Jon
 

0
Jonathan
3/7/2010 1:49:33 AM
Thanks, but my relationship seems to be created just fine. However, as 
mentioned in my other post, I was also calling BeginTrans and I've found 
that the error goes away if I don't call BeginTrans. It appears Access does 
not allow me to modify a table that has changes pending a commit or 
rollback.

Thanks.

Jon

"Paul Shapiro" <paul@hideme.broadwayData.com> wrote in message 
news:Oi5TqaTvKHA.5936@TK2MSFTNGP04.phx.gbl...
> "Jonathan Wood" <jwood@softcircuits.com> wrote in message 
> news:e0uUwCDvKHA.6124@TK2MSFTNGP04.phx.gbl...
>> I've written some code to create a relationship between table A and table 
>> B. The code looks something like this:
>>
>> Set rel = db.CreateRelation("A_B")
>> rel.Table = "A"
>> rel.ForeignTable = "B"
>> Set fld = rel.CreateField("AID")
>> fld.ForeignName = "BID"
>> rel.Fields.Append fld
>> db.Relations.Append rel
>>
>> This code works just fine except that a few lines later, I use DLookup() 
>> to find a row in table B, but I then get an error that table B is 
>> exclusively locked and cannot be accessed.
>
> Here is the function I use for creating new relationships. I've always 
> been able to continue working in the db after running this code.
>
> Public Function pjsCreateRelationship( _
> dbData As DAO.Database, _
> strRelationName As String, _
> strParentTable As String, _
> strChildTable As String, _
> astrFieldNamesPrimary() As String, _
> astrFieldNamesForeign() As String, _
> Optional fCascadeDelete As Boolean = False, _
> Optional fCascadeUpdate As Boolean = False _
> ) As Boolean
> On Error GoTo ErrorHandler
>    Dim fSuccess As Boolean, relTemp As DAO.Relation, _
>     lngLoop As Long, lngAttributes As Long
>
>    'Make sure we were passed compatible field name arrays
>    If LBound(astrFieldNamesPrimary) = 0 And LBound(astrFieldNamesForeign) 
> = 0 _
>     And UBound(astrFieldNamesPrimary) = UBound(astrFieldNamesForeign) Then
>        fSuccess = True     'Assume we succeed
>        'See if relationship name already exists
>        Set relTemp = dbData.Relations(strRelationName)
>    Else    'Bad calling parameters
>        Err.Raise Number:=9999, Description:="Programmer Error: Invalid 
> calling parameters"
>    End If
>
>    If relTemp Is Nothing Then  'Return here from error handler
>        With dbData
>            lngAttributes = IIf(fCascadeDelete, dbRelationDeleteCascade, 0) 
> + _
>                    IIf(fCascadeUpdate, dbRelationUpdateCascade, 0)
>            Set relTemp = .CreateRelation(strRelationName, strParentTable, 
> strChildTable, lngAttributes)
>
>            With relTemp
>                For lngLoop = 0 To UBound(astrFieldNamesPrimary)
>                    .Fields.Append 
> .CreateField(astrFieldNamesPrimary(lngLoop))
>                    .Fields(lngLoop).ForeignName = 
> astrFieldNamesForeign(lngLoop)
>                Next lngLoop
>            End With
>            .Relations.Append relTemp
>            .Relations.Refresh
>        End With
>        CurrentDb.Relations.Refresh
>    Else    'Could verify the existing relationship has the same attributes 
> and fields
>    End If
>
> ExitHandler:
> On Error Resume Next
>    pjsCreateRelationship = fSuccess
>    Set relTemp = Nothing
>    Exit Function
>
> ErrorHandler:
>    Select Case Err.Number
>    Case 3265   'Name doesn't exist in this collection
>        Resume Next
>    Case Else
>        'Your error handling code
>        fSuccess = False
>    End Select
>    Resume ExitHandler
>    Resume
> End Function
>
> 
0
Jonathan
3/7/2010 1:51:55 AM
Reply:

Similar Artilces:

Pivot Table
Hi My data in table are very simply organised as below: --------------------------------------------- Res.no City Code Var1 Var2 Var3 --------------------------------------------- 1 City1 CD0001 num num num 2 City1 CD0002 num num num 3 City1 CD0003 num num num 4 City1 CD0004 num num num 5 City1 CD0005 num num num 6 City2 CD0006 num num num 7 City2 CD0007 num num num 8 City2 CD0008 num num num 9 City2 CD0009 num num num 10 City3 CD0010 num num num 11 City4 CD0011 num num num 12 City3 CD0012 num num num 13 City3 CD0013 num num num 14 City3 CD0014 num num num 15 City1 CD0015 num nu...

Adding to an imported table
I am a beginning Access user. When I order items, I am able to import the order information (info about each specific ordered item) into an Access database. I have figured out that I can add information from subsequent orders to the same big table (and could use a query to ID stuff from specific orders if necessary). I want to mark items received or not received. If I do this in the table, I mess up the column names and importing the order information doesn't work (at least, it doesn't work with my limited knowledge). I have tried to create a form where I add in a checkbox, but I ...

How to lock closed opportunities
Hi, I would like to prevent users from re-opening closed opportunities after a certain period from the Actual Closed Date. This is to prevent them from changing some of the information. The best would be to disable the "Reopen Opportunity" option in the Actions menu for the closed opportunity. I found that there is no access to the menu if I want to use javascript and workflow manager does not seem to cater for this scenario. I also looked at the security privileges but it does not have controls to menu level. Can anyone advise me how I can solve this? Thanks. Hi, Put the...

Sorting Cube dimension names in a Pivot Table
How do I get the dimension names in the "pivot table field list" sorted by the translated names? The pivot table field list contains: - one measure-node - one dimension node for each cube-dimension. The cube-dimension-nodes are represented by their translated names. It looks like, this names are not sorted by this translated names, but by the order of the data source. The related radio-buttons in the pivot-table-options are greyed ("A to Z" and "sort by data source"). I would need the "A" to "Z" order. Pleas notice! I...

Does anyone know of a software application for Pivot tables other. #2
We have some very complex pivot tables in Excel that unfortunately are not very stable or flexible when you make changes. The data list is used to generate multiple pivot tables in the same workbook but the data and the number of records can change frequently. One time we might have 18000 records and the next time we might have 2500 records. So far it seems that everytime we have to change the data we have to reset the data source range for each pivot table. I believe each pivot table was copied via Edit, Move or Copy, rather than being generated as anew pivot table or based on anot...

Access 2007 Pivot Table Chart
I've created a Pivot Chart and would like to add value labels to the columns. Is that possible? ...

Cursor Anchor Lock
I have had this happen to me in Excel over the years. I am sure it has to do with a memory issue but I am curious if it has happened to anyone else. The helpless desk where I work is clueless, I know 10X more about excel than anyone in the IT dept. Sometimes, for no apparent reason, my cursor will anchor onto a cell. I can still move the cursor but it stays in select mode, i.e. if I drag the mouse it is as if I was holding down the left click button, selecting cells as I move the mouse. No matter what I do I can't get the excel to deselect the cell and I have to control/alt/del to kil...

Purging old data from pivot table
Is there any way to purge old data from a pivot table? I modified th contents of a column of data, and "refresh" put the modified data int the pivot table but also retained the old data (which no longer exist in the original spreadsheet). It gets confusing when I need to selec different items for a new view, since the old non-existant data appear to be selectable as well as the current data. Saving, closing, reopening did not help either -- Message posted from http://www.ExcelForum.com Hi have a look at http://www.contextures.com/xlPivot04.html -- Regards Frank Kabel Frankfur...

Lock cell #2
Hello In excel 2003 I found it very handy to have the 'Lock Cell' icon customised on my toolbar. I can't find it in Excel 2007 which is very disappointing. In a large spreadsheet, I want to be able to see if a particular cell is locked or not, just by selecting it rather than having to open Cell Format and then the Protection tab each time. Thanks J Hi, go to the "quick access taskbar" besides the menue button and select "more commands" then switch to the Home Tab and select the lock button! Yeay! Thank you "Sascha" <noreply@microsoft....

Renaming forms tables and reports
Is choosing "save as" and making a copy and removing the original the only way to rename a report, table, or form? I started with the Issues template but I want to change the names of the objects. I can right click and rename the shortcuts in the navigator window, but the name doesn't change and the references are still under the old name. I do not want my shortcut to be a different name than the reference name because then there will be confusion for database maintenance. I've also tried renaming the objects in their properties window, but I couldn't find a na...

Report using Fields with the same name from different tables
Hello all - I created a db with no knowledge of naming convention. Now I am attempting to write some reports pulling data from tables with the same field names. Apparently Access doesn't like this....I attempted to change the names in the BE but when I relink the FE it is prompting for parameters. What is the best way to proceed? Thanks - Lee -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/201005/1 TheLee via AccessMonster.com wrote: >I created a db with no knowledge of naming convention. Now I am attempting to &...

Conditional Formatting in data table of a chart
I'm using Excel 2007, and trying to apply conditional formatting to the data table of a line chart. I've tried creating the chart without the formatting applied to cells with the original data (data source), and then applying the format to the source; as well as creating the chart after applying the format to the source. The data table in the chart will naturally update with changed data since it is linked, but will not incorporate any style formatting. Any thought? Thanks. Data tables in charts are not particularly flexible. They do not, for example, support conditional fo...

Changing Properties of Fields in Table
Below is part of a code that somebody had given me to change the property of a field (in this case to make the field a required field). fld1.properties("Required") = True What do I need to do if I want to modify this line of code to do the following: a) Change a field to have a DATATYPE of Number b) FIELD SIZE should be DOUBLE Any help will be appreciated. Toks You cannot change data types in that way. Your choices are: 1) Create a new field of the correct type, run an Update query to transfer the data from the old field to the new field, delete the old field then rename ...

Rotate data table by 180 deg.
Hi, How to rotate data table (matrix) by 180 deg.? Kind of what Transpose function do just I need to do two Transposes in the same direction, as to rotate picture by 180 deg. For example to rotate table: 1 2 3 4 11 12 13 14 21 22 23 24 And get: 24 23 22 21 14 13 12 11 4 3 2 1 Of course it is possible to do by linking one cell to other, but very long process for big matrix. Thank you. Gedi. With your range called "myrng", select A1 of a new sheet, enter this formula, and press <ctrl><shift><enter>: =OFFSET(myrng,ROWS(myrng)-ROW(),C...

GUID In Table
I have a GUID in a table that I fill programmatically. Often the table will be searched on the GUID. It is just a GUID generated from dotnet in a varchar field. The majority of the time the table will be searched on this GUID... only sometimes will be it be searched on an the primary key ID (just an identity). What type of index, if any, should I place on the GUID? Unique Constraint, Unique Index? The table may get to a couple million records. I suggest you store the GUID value in a uniqueidentifier column instead of varchar . This way, you'll store the native 16 byt...

PayPeriod table
I am working on a payroll system. One of the task is to create a payperiod table to capture the previous payroll currently store in the Excel file from year 2006 to present. The payperiod is bi-weekly and I want to populate this table with the pervios payperiod and up to year 2050. The columns in this table will have the following fields: ID Calendar Year - which is determined by the PayPeriod End Date PayPeriod Start Date PayPeriod End Date PayPeriod - PayPeriod Start Date - PayPeriod End Date How to write a sql query to populate this table? PayPeriod always start on Sund...

Macro to lock VBA project
Hello Is it possible to have a macro that will: 1. under VBA project properties lock project for viewing 2. open input box to input password to view project properties. 3. save file /or prompt to save as. So in other words after you open vba project and play woth macros, work on file etc, one could just click a button and a input box to input password to lock VBA project would be opened. Afeter you input it the input box closes and saves the file or prompts save as if it is not saved yet? I tried to google it and record it but no luck... Anyone? Thank you Excel's VBA doesn't supp...

lock code in excel EX(hyperlinks)
I HAVE MADE A SMALL PROGRAM AN NEED TO LOCK ALL CODED LINKS AND BE ABLE TO CLEAR PAGE THANKS FROM IAN ...

Removing a field From a Pivot Table
I'm working with a pvt table in excel. Right now there are several fields in the pvt table, including the month different entries are made and the account that made them. When i hit the show detail button in the pivot table i get the detail all of the entries grouped by month and by account on a separate sheet. I want to continue to do this, but i want to also be able to get a second view where i hit the show detail button and only see the detail grouped my account (month is either hidden or totally removed from this list of data in the pvt table and the rows that composed the different...

Tables update interrupted
SOP is configured to post through to the GL, I came this morning and saw this status message, 'tables update interrupted'. I know I can use the batch recovery for this batch, but before I do anything, why did this happen? And how can it be prevented? rcr, You know your environment better than anyone of us. Without a description of what processes, etc., run on your server while posting is happening is very hard to even venture to give an aswer. This is one of those cases where it's anyone's best guess if you do not provide any extra info. My first guess would be check ...

Linked Excel Table, Update Table
Using Access 2002. I import an Excel table into my database that is regularly updated from my company's main database via a BusinessObjects query. I need to create a table that constantly updates to mirror the linked Excel table because of record locking issues (can't have multiple people in the database if queries are based on the Excel table). I use a make-table query as a starting point... My question is how do I keep the table updated? Thinking I need to do an update query, a delete query and/or an append query but not sure how or in what order. Any help would be apprec...

Comments on a pivot table
Dear All, How would one add comments to a pivot table? I want to add two comments to items in a pivot table however when I select different items in the filter list, thus changing the range covered in the pivot tables, the comments remain static and just stay on cell D12 for example. Any items how I can apply a comment to a specific item in the pivot table? Please see pivot layout below: Invoice Date Range 10-20 21-30 1234 £10.40 5346 £12.50 I want to add a comment to, for example, the cell with £12.50 i...

Table name in Expression builder not allowed?
I tried to do a simple calculation (on a form) but fail to understand this: =[UnitPrice]*[Quantity] works, however if I use the Tables list from (below) and construct the formula it shows as: [OrderDetails].UnitPrice * [OrderDetails].Quantity and it does not work. I get #Name error When field names are written as [UnitPrice] is it not a standard practice to name them as such? Somewhere I also see [OrderDetails]![UnitPrice] notation. Why? Thanks "dindigul" <padhye.m@gmail.com> wrote in news:O9MdHWQEIHA.5752@TK2MSFTNGP02.phx.gbl: > I tried to do a simple calculation ...

Pivot Table Help
Hello, I have a challenging pivot table issue that I hope I can get some help on. Here is the spreadsheet I'm working with: https://www.yousendit.com/download/OHo0N3RSSU90d0UwTVE9PQ I'm having problems with showing percentages in my pivot table such as gross margin and operational profit. Here is the P&L pivot table as I have it now, as you can see I can't get the "Gross Margin %" to output a number. Both "Gross Margin %" and "Operational Profit %" are calculated fields I've added to the pivot table since the raw data only inc...

Problem making versions of a table by changing sourse data in another workbook
Hello How to make a version of a table by changing source data in another workbook and retain original version of the table easily? I lose the original version of the table if I change the source data in the other workbook even if I make a copy of the original table workbook. I have to recreate all links if I make copies of the original table workbook and the source workbook. Hi! Have you tried making a "snapshot" of the worksheet? (Converting the formulae into their values - fixed forever - using copy/paste special values) Just don't do it unless you have a secure copy ...