Share via

Access 2019 how to use without exclusive or file is open by other user errors

Anonymous
2021-02-22T14:22:49+00:00

Since I moved on from Microsoft Office 2003, I have been tormented by 2010, 2016 and 2019 (microsoft access)

I am an retired old man, and do development in Access for my own use.

Would someone explain to me in detail:

 I want to create a database from scratch (please instruct how to do it, i have done some extensive development when I still worked in the business world.)

eg.

create table:Temp

Fields Data1 Data2 Data3

Content: 1,done, 1done  

Result in Data3: append Data1 to Data2.

Tools/Reference:

Visual Basic For Applications

Microsoft Access 16.0 Object Library

Microsoft DAO 3.6 Object Library

OLE Automation

vba code:(module)

Option Compare Database
Option Explicit

Function basCallProc00_1000_PREP01()
CallProc00_1000_PREP01
End Function

Private Sub CallProc00_1000_PREP01()

Dim dbs As Database
Dim rst As Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Temp", dbOpenDynaset)
Do

   rst.Edit

        rst!Data3 = rst!Data1 & rst!Data2
    rst.Update
   If Not rst.EOF Then
       rst.MoveNext
    End If
Loop Until rst.EOF

DoCmd.OpenTable "Temp"

MsgBox "done"

End Sub

My problem:

I have installed windows 10 as an administrator.

I have installed office 2019.

I have given myself full control access of the folders.

Please give me detailed instructions how do create a database from scratch so that the vba code runs, no saving problems.

Sometime I can save the database module code,

and other times the module code save button gets greyed out.

Then I have to close the database, with questions to save this and save that.

Then reopen access from scratch again.

I am the only users, administrator.

Remember, with office 2003, I had not problem at all. All my applications worked.

Thank you.

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

6 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2021-03-22T20:52:58+00:00

    Thank you for all your help.

    The problem still persists.

    What I find between about 21h00 and 22h00 South African time, I have not problems.

    I can work freely, with absolutely no problem. 

    However, outside this window time frame, the problem persists.

    Something seems to be running in the background, but I cannot detect it.

    I have looked at Task Manager, however, 

    not thoroughly.

    Thank you.

    I am using windows 10, office 2019.

    By the way, I choose to use Access 2002 to 2003 format mdb extension.

    If you would like to send me a small access program, then I can test it.

    Thank you so much.

    Deon

    Was this answer helpful?

    0 comments
  2. Anonymous
    2021-02-23T19:11:27+00:00

    Greetings,

    Thank you ever so much, it appears to be working, let me carry on, as per your instructions and I will let you know if the problems appears again. I found in the past, it worked at certain times of the night without problems.

    I also have AVG security programme, running in the background.

    However, I will give you a feedback in a weeks time.

    Once again. Thank you. Deon

    Was this answer helpful?

    0 comments
  3. Anonymous
    2021-02-22T18:08:25+00:00

    How this works in say 2003, or 2019 has not changed.

    I would however launch the task manager.

    Exit Access and then check the task manager and be sure that Access is still not running.

    Eg this:

    So some of your narrative seems to suggest that a stray copy of Access is still running.

    And of course in most cases, if you code is running, then you can't modify code. And ALSO be VERY but BEYOND very careful if you have a form launch with a timer control + event. Timer events and a form loaded and running will play havoc with the VBA code editor. (you want to close that form and ensure that the timer event + code is not running when you decide to edit code or make changes to such forms.

    So watch out for if you launched two copies of Access.

    Watch out for if/when/you are using a form with a timer.

    And ALSO very much watch out if you are using any SharePoint tables. Now, it is very possible that none of the above applies to you - but one of these issues might. (and all of the above are common reasons for say you not being able to modify or change something).

    So exit Access - then check the task manager - some of the issues you describe suggests that a stray copy of Access is still running (or perhaps you are using automation and creating a new running copy of Access - but that's certainly not been part of this narrative - but again a "possible" thing or issue to check.

    And I assume that often and frequent while in the VBA editor, just before you try and run some code, you do a debug->compile to ensure that the VBA code does not have syntax errors.

    So, there is something going on here - it just not clear what that is. But some issue or something is occurring or has occurred that is making this process painful. And the above issues and problems to look at? They are the same ones that existed say in 2003 as in 2021.

    The other issue is perhaps file permissions. Today, often some folder does not have permissions or permissions have messed up the file or the folder one is using.

    But a timer form running - that I would look for. Doing a debug->compile in the VBA editor would also be something I do far more frequent until whatever the issue is and found goes away. And as noted, exit Access - check the task manager to ensure that Access (or a stray copy) is not running.

    Also, check your defaults for when you open a file - it should not be exclusive. Eg this:

    file->options->Client settings->down to advanced settings.

    the default should be shared. And same when you open a database here:

    So don't use exclusive or read only when you open. So, it not at all clear what "issue" is being missed, but clearly one trip up or issue is occurring - it just a question of what.

    Regards,

    Albert D. Kallal (Access MVP 2003-2017)

    Edmonton, Alberta Canada

    Was this answer helpful?

    0 comments
  4. Anonymous
    2021-02-22T16:46:39+00:00

    Hello, and thank you so much.

    >I added the "Hello, world", vba script.

    ran perfect.

    >Made changes to your script and saved ok and ran. good good.

    >THEN I ADDED THE CODE ABOVE, to open a table and update, as previously sent.

    IT RAN PERFECTLY.

    >I THEN tried to make changes to the vba script., and the following messages popped up.

    MESSAGE 1.

    You do not have exclusive access to the database at this time. If you proceed to make changes, you may not be able to save them later.

    click show help button.

    When attempting to open a database object in Design view, or when trying to save design changes to a database object, you may encounter this error message. Access requires an exclusive lock on the database to make design changes on some object types, since other users may attempt to use the objects. This error indicates that Access has not obtained an exclusive lock on the database. If you change the name of a table, or its field definitions, while someone has the table open, this will result in a serious error for the other user.  Therefore, you must have exclusive access to the database while you attempt to update the object. When you release your exclusive lock, other users will again be allowed to use the database.

    It is recommended you implement source code control for development efforts by using the Microsoft Visual Source Safe Add-in for Microsoft Access. As an alternative, you could distribute local working copies of the database to each developer.

    MESSAGE 1 END.

    >The then went out of vba editor to look at the result of code.

    MESSAGE 2.

    Another error message:

    Microsoft Access can't save design changes or save to a new database object because another user has the file open to save your design changes or to save to a new object, you must have exclusive access to the file.

    MESSAGE 2 END.

    >Now I am closing Access.

    MESSAGE 3.

    Another error message pops up.

    Do you want to save changes to the design of module 'basCallProc00_1000_PREP01'?

    MESSAGE 3 END.

    MESSAGE 4.

    Another error message:

    You do not have exclusive access to the database. Your design changes cannot be saved at this time. Do you want to close without saving your changes?

    MESSAGE 4 END.

    >>My observation:

    The challenge errupts when working between "module: vba script" and "access part" of the MSACCESS. Changes to vba script, and copying modules, is restricted, which is not the case when copying tables, queries and macros of the system. The two systems clash.

    Was this answer helpful?

    0 comments
  5. Tom van Stiphout 40,201 Reputation points MVP Volunteer Moderator
    2021-02-22T14:57:06+00:00

    Maybe if we take baby steps, we can figure out what is wrong.

    1. Open A2019. File > New > Blank Database > give it a name > Close Access. Problems?
    2. Open A2019 > File >Options > Trust Center > Trust Center Settings > Trusted Locations: add the folder where you want to save your databases.
    3. Open A2019. File > New > Blank Database > give it a name > Alt+F11 to open VBA > create new standard module > create test procedure:

    public sub test1()

    debug.print "hello, world"

    end sub

    Ctrl+G to go to the Immediate window > type:

    test1

    and hit enter. So far so good? If not, please quote any error messages verbatim.

    Was this answer helpful?

    0 comments