Impossible to open database Results

Hello guys.

I've got an idea for something I'm trying to implement that I suspect is actually impossible to do in Access, but thought I'd see if any of you guys knew of a way before I dismiss it.

I'm developing an Access database that handles my company's orders, purchases, quotation and deliveries etc. I've incorporated barcodes in order to quickly enter IDs and reference numbers but I'm also now starting to play about with creating custom commands and functions that can also be triggered by scanning a barcode.

I've got this working just great if you have the correct form open and the correct field has focus. You scan the code and it breaks it down into three chunks, the section of the DB, the command you want to apply and the record against which you want to run the command. Switch/Case is then used to apply the commands.

What I want to do however, is implement a system whereby a certain barcode can be scanned and it doesn't matter which form/field you have open/selected. I'm guessing I need to create some sort of 'listener' that runs silently in the background and detects when a specific string is entered.

Now the only way I can see to get this to work is to create a function and apply it to every single element on every single form, but this is inelegant and far from practical/feasible.

So does anyone know of a way to incorporate the above?

This was going to be a plea for help but is now a retelling of my experience in case someone else runs into a similar problem.

After being upgraded to Office 2010 I received the dreaded MSCAL.OCX error message but quickly found the solution of removing the missing reference from the VBA list. (This thread was especially helpful.) However, I found that after clicking through multiple error boxes, the VBA window would open but the References menu was greyed out, meaning I couldn't apply the solution. So I tried deleting the module that VBA jumped to after clicking through the error boxes; when I did that I was able to open the database like normal, go into VBA, disable the missing reference, and then paste the the module back in. Well after that not only did the database crash whenever I tried opening a form that formerly had a calendar control on it, but it also crashed whenever I attempted to open the VBA window, basically making it impossible to try any further fixes whatsoever.

So then I went to a backed-up version of the database and attempted to check every form for the actual calendar control object and remove it, as well as removing any references in the code. Unfortuntely I found none whatsoever, so it was frustrating and perplexing.

So then I went back to the "broken" version and decided to try opening it in 2003 and see if I could find and delete the objects that way. When strangely the problem vanished. While I'm not 100% sure, I believe what happened is that when I copied the database to work with it, the old .ldb file was deleted and regenerated, and for some reason disabling the missing reference didn't fix the reference problem until the .ldb regenerated.

So the moral of my story is: If you try to fix the MSCAL.OCX error problem in 2010 by unchecking the missing reference in the VBA window but your problems persist, try deleting your .ldb file.

I just thought I'd share.

Even though we upgraded to Office 2007, we left our Access database in 2003 (mdb) format, so that we can open our database in Access 2003 design mode and make changes to our custom menu.
When we open the mdb in Access 2007, we can see the menu under the Add-Ins tab.

By following the instructions here,
we were able to turn off the ribbon completely and just use our custom menu bar - which was great, until someone accidentally closed the custom menu bar, and now there seems to be no way for them to get the custom menu bar back.
What's weird is that the custom menu bar is only missing from their computer, and only for the database they were using.

To get around the problem, I got the Add-Ins tab back, and the custom menu is in the Add-Ins tab, but only in run mode, not in design mode.
(All other databases on all other computers have the Add-Ins tab in both run and design mode).

To make matters worse, when at first I could not resolve the problem on my customer's computer, I did the same thing on my development computer, because I thought for sure I would be able to resolve the problem, and now I have the same problem on my computer.
And since in design mode I cannot see the Add-Ins tab, I cannot see the custom menu, and therefore it is almost impossible to make any changes (since I cannot traverse the custom menu and must therefore look through all the individual forms to try and figure out what form the custom menu selection would have opened)

Another problem is that even if I follow the instructions (in the above link) again, I can no longer get it to hide the ribbon and just use the custom menu bar.

So somehow we have hidden the custom menu bar in both run and design mode, and the add-ins tab in design mode, and only at the computer where the custom menu bar was accidentaly closed, and there appears to be no way to fix the problem.

So there must be something on the affected computers that is telling that particular Access database that the custom menu bar should be hidden (or not visible), and there is no way that I can see to fix the problem (except perhaps by re-installing office or formatting the computer).

I hope that I am overlooking the obvious, and someone is going to tell me an easy way to get the custom menu bar to display again.
What an insane waste of time for nothing.
Why on earth did they have the audacity to assume everyone would reconstruct their custom menus using some complex ribbon thing??
How difficult would it have been to leave the old, simple, menu tool in there, and import the custom menus?
Considering the mountain of money and years they spent building the ribbon, what an oversite, intentional oversite I guess.

I would like to make a backup copy of my database when the DB is opened. How would I go about this? Would I have to do a shell command to copy the current db and make the copy to a new location. Is this easy or impossible?

Hello there,
I have a question for you experts. I am working with an intranet and would like to be able to have a link go directly to a database.

When I have the link set to go to the file jackpot.mdb, clicking on it gives me an option to "save this file to disk"...if i click on this, it saves the file to the location of my choice and allows me to open it.

If I connect to the .mde version of this file, jackpot.mde, I am given the option to either "Save this file to disk" or "Open this file from its current location." I can then either save it to my system to open it or just open it from its current location.

This leads us to my problem, I need to be able to have it go directly into the database. I want it to automatically just open it from its current location without asking how the user would like it opened. Is this at all possible? Please advise whether this is something I can continue to pursue or if it is impossible.



Working in Access 2000 to an MS-SQL 2000 server, and having created the ADE file and put it on the fileserver, it opens as read only, so it is impossible to add or modify data (somewhat defeating the object of a database, you might say)

I have tried copying the ADE down to local machines and testing it, but the result is the same. Why would an ADE file be read only? Does anyone know?

Many thanks


I have run an Office 97 / Access 97 database for many years under WIn98 with no problems. The database holds information which on a daily basis is extracted and pushed across to Word 97 where it is edited and finally printed.

I recently upgraded the computer to WinXP Pro sp2 and loaded up Office 97 and the database again. When I ran the database system there were a few oddities in transferring the data across to Word but generally speaking no problems EXCEPT one fairly big one.

When ever I have Access open, either on its own or , usually, with Word open, it is impossible to save a file with more than one letter the filename. Thus if I try to save a file in Word which contains the data that has been passed over from Access, and I use the filename "abcd.doc", the system will save "a.doc". If I type "xyz.doc", the system will save "x.doc".

Similarly, if I try to save a table from Access using saveas/export then, again, I can only save the file with a single letter name ..... filename "mnop.csv" saved as "m.csv".

To get the correct filename, I have to use Explorer to rename the file and that works ok.

What is going on here? I bet it is something very easy and simple but it has beaten me. Any help gratefully received



I'm currently in the process of testing security before shipping my database off. I have read the access security faq, but I'm not sure which option to go for. The main factor that makes me doubt, is that the db will be sent to locations that I will not be able to physically get to myself. I am hoping that some of you have some experience on dealing with remote installations of your databases.

Option 1:Use one workgroup file make a user_admin user which is NOT member of the admins group rename the workgroup file to an mdb, open it and give the user_admin permissions to edit the user and group table The problem with this approach is that the user_admin can assign any user to the admins group, which basically defeats the whole purpose of this option really.

Option 2: Use two workgroup files (development/securing and distribution) make the user_admin a member of the Admins group in the distribution file As the Admins group is different in the distribution workgroup file, it is impossible to change any database object. The only problem is if something were to go wrong (which I think is highly unlikely ) it might be good to be able to manually walk the user through fixing the problem, and to do that it is likely that some database object needs changing, therefore needing an admin login...

comments or suggestions welcome

Thanks again

I have a unique problem with replication. I sync the replica to the master and all works well with no conflicts. Problem is, the next time I open the master database I get a message saying "This member of the replica set has conflicts from synchronizing changes with other members. Do you want to resolve these conflicts now?" I click yes, even though when I originally sync'd the databases there we no conflicts. Then the Conflict Viewer dialog box displays and magically all the data I merged earlier in the week disappears. I'm caught in an endless loop! HELP! I've been working with master/replica databases for about 4 years now and have never has this problem before.

There's an article in the Microsoft database (article ID 883870) that explains the problem and a workaround. But in my case the workaround is unfeasible. The database is about 112 meg and there's so many records and fields in the conflict tables, it's almost impossible to make the corrections by hand.

Has anyoue out there every experienced this problem? If so, do you know of a fix for it?

Thanks so much for your help!

Cindy Z

For the benefit of clarity, I would suggest anyone who reads this thread to download the entity relationship model I have produced of the database in order to understand what the hell I'm talking about.

I have points to make before I present my questions:

* The database is intended to be used to log calls and emails. These are known as cases, hence the entity Case. Each case MAY have Clients (sometimes no details of the person calling are acquired, hence the Client entity being optional).

* Case_Reference is a reference table to link unique Cases with unique Clients. It is absolutely necessary in order to keep Clients as seperate entities from their Cases.

* You'll note that the Client entity is built up using many other optional entities. These entities could otherwise be fields of the Client entity itself, but I have made a design decision.

My difficulty comes when designing the forms for this database. I have full referential integrity, the tables are fully normalised as reasonably as possible. I have ruthlessly prevented any null entries of any sort.

However, after much reading up (internet and this book I have called Access 2003 Bible), I'm still unsure as to the power and capabilities of Access of achieving EXACTLY what I think is best for the client when it comes to user interface issues. Here is my difficulty:

Noting that Client has many optional related tables, and that there are many levels of optionality in my design: when designing forms, I'm finding it tricky.

Obviously, I would like my clients to be able to enter their data, check it over, take their time, then save the data, but only once they have added everything they want to a "case". The problem is referential integrity, and the user interface.

I'm aware of subforms, but the nature of forms is quite a thorn. I want to construct a form so that the data can be entered onto it and only when a "SAVE ALL" button is clicked does the data on screen transfer onto the tables IN THE ORDER I WANT. Not one table at a time.

* I also want to determine the Primary Key value that is used for each and every entry. My database does not use autonumber fields: they are unreliable and prone to....horrible things. Thus, all my tables have a unique NUMBER type primary key. How would I go about programming some sort of "counter" per table: I'm assuming I have to make the application check the largest number (i.e. latest entry) and save the current case as the next number. Question is how?

* Additionally, the actual user interface I have designed seems impossible using Access forms. A typical scenario is this:

Someone calls. This is a Case. He gives his name. He is a Client. He gives someone elses name. This is also a Client. Thus this Case has 2 Clients. The data entry person opens a new Case. He adds these 2 Clients, enters the Case details and saves the Case.

My question is, can I implement a form whereby a list of the Clients currently attached to the current Case being entered (thus it doesn't actually exist yet!! Hence referential integrity issues) is shown in a list box (I reiterate, they don't exist yet, thus I can't query them from the database, because they don't exist).

What I mean is the data entry person makes a case. He chooses to add a client, enters the details for that client, saves that client, and repeats for the next. After all this, he clicks 'save case' and only then is all this data logged. What is the simplest way to do this.

* One more thing: all Cases have Topics. The Topics table I have constructed has a list of hundreds of topics, and each has a Type and a Name plus their IDs.

From a user interface POV, I intended to have to lists boxes: one of all existing Topics, and one of all assigned Topics on the current case being entered. The user would drag topics from the existing list to the Case list. The topics need to be removed from the existing list (to prevent the same topic being added twice) and appear in the Case list. Can this be implemented in a form: noting that nothing is saved until the data entry person clicks a "save the whole case and all other things that it needs" button.

Summary: How do I instantiate every Case until all data is entered related to it and in it before recording it onto the Access database using forms...if it is possible?

Clarification: By Application as a whole, I am refering to the external level of ANSI/SPARC architecture and various similar models. Additionally, don't be afraid to tell me I'll need to program this nonsense into Access if I have to!

May be it is too simple, may be impossible, I do not know, but I met a problem. I want to prevent user to copy of access database files.

I made a test. I copied the access file (xxxx.mdb) and the work group file(yyyy.mdw) into a CD and used it at home. No problem !
It should not be so easy (?).

How can prevent the files are copied OR prevent users to use (misuse?) my database ?

I have access 2003. The files are on the server (MS Server 2003) and clients has rigths to open/execute these files. With the same permissions clients/users can copy both db-files and the work group file (yyyy.mdw) and they may misuse it sowhere else. Perhaps I can prevent copying some other way??

Hi everyone

I want to change password of database of access and i use this code :

	Private Sub ChangeMotPass_Click()

On Error GoTo err

Dim odb As DAO.Database
Set odb = CurrentDb
odb.NewPassword "ancien", "nouveau"
MsgBox "Mot de passe changé"
Set odb = Nothing
Exit Sub
Select Case err.Number
    Case 3031
        MsgBox "Mot de passe non valide", vbCritical, "Sécurité"
    Case Else
            MsgBox err.Description, _
            vbCritical, "Erreur No." & err.Number
End Select
Resume fin

End Sub

but i have error message :

Impossible to change the password of a database open in sharing!

Can you help me please??

I have a Switchboard which, when a particular button is clicked, Deletes all records out of a table, Appends several thousand records to that table, updates all of those records, calls a Select query to show filtered results from that table in a Report, and then Deletes all of the items out of that table again. The problem is that even after all of the records have been deleted, the db is still bloated to over twice its normal size. Is there a way to call Compact and Repair in the On-Click event of this command button? (I don't want to set it to Compact and Repair every time it closes as our network is slow and people pop in and out of the database all the time. This command button, however, will only be used about once a week or so).

*I now see in the Visual Basic Help Files that there is a CompactDatabase method, but for some reason, it won't let me open this file. If anyone has a snippet of code on how to use this method, it'd be much appreciated.

*Well, now, wait. Looking through posts, I see that it may be impossible to Compact and Repair while the db is open. So if anyone can give me a definitive answer, either yes or no, if this can be done, then please do so.

So I'm finally down to setting up some reports in my database, but I'm having trouble with setting up to print the way that i would like. When my report runs, I have it set to only show the "Print Preview", and it is also set as a PopUp and Modal. i've done this because I don't want anyone leaving open windows, they should open the report/review/and then print if necessary. The problem with this is that I have no menubar/ribbon giving options to print the window. (Unless of course you right click) I've been trying to research ways to add printing options and it seems that the most presentable option(and the one I like the most) is to add a ribbon to the report window. Here the user can do multiple items such as "print" or "zoom", etc. But I don't understand how.

I did find IDBE Ribbon Creator 2010, which I thought would be easy enough. I have almost no issues creating the ribbon(I didn't see some controls that I'd like, but no big deal). Once the bar is created, I go to the finish tab and select the database, and it creates some modules. This is where i have some problems, here's a couple stats to my database.

Access 2010
I have a split database. (Ribbon Creator stored the modules in the Back-End, but I did import them to the Front-End as well)

I still don't have any available options to add the Ribbon in the report properties, under Ribbon. Is it there? How do I activate it for the report window? Or is this impossible, and I need to rethink things?

Thanks in advance, I'm just lost right now.


I was building this database for school and everything was working perfect. I had a few lines of code that interacted with forms and it was working good.
But now all of a sudden when i try to run the code i get this "run time error 7 out of memory" on the form commands.

I searched a lot on the internet and i tried many fixes, nothing worked so far.
This error also happens on other computers to with different versions of access.

This is a link to the complete database:
Here is one of the functions that fails.
Code: Function s1()Dim x As String 'this one works x = Forms_TitleScreen.searchbox.Value If IsNumeric(x) Then If Len(x) = 4 Then Dim numsql As String numsql = "SELECT Movies.MovieID, Movies.Title, Movies.Year FROM Movies WHERE Movies.Year = " & CStr(x) & ";" DoCmd.OpenForm ("Info") Form_Info.Visible = False Form_Info.searchbox.Value = x Form_Info.Result.RowSource = numsql Form_Info.Visible = True Call i1 End If End If If IsNumeric(x) = False Then If (InStr(1, x, "a") > 0 Or InStr(1, x, "b") > 0 Or InStr(1, x, "c") > 0 Or InStr(1, x, "d") > 0 Or InStr(1, x, "e") > 0 _ Or InStr(1, x, "f") > 0 Or InStr(1, x, "g") > 0 Or InStr(1, x, "h") > 0 Or InStr(1, x, "i") > 0 Or InStr(1, x, "j") > 0 _ Or InStr(1, x, "k") > 0 Or InStr(1, x, "l") > 0 Or InStr(1, x, "m") > 0 Or InStr(1, x, "n") > 0 Or InStr(1, x, "o") > 0 _ Or InStr(1, x, "p") > 0 Or InStr(1, x, "q") > 0 Or InStr(1, x, "r") > 0 Or InStr(1, x, "s") > 0 Or InStr(1, x, "t") > 0 _ Or InStr(1, x, "u") > 0 Or InStr(1, x, "v") > 0 Or InStr(1, x, "w") > 0 Or InStr(1, x, "x") > 0 Or InStr(1, x, "y") > 0 _ Or InStr(1, x, "z") > 0 Or InStr(1, x, "A") > 0 Or InStr(1, x, "B") > 0 Or InStr(1, x, "C") > 0 Or InStr(1, x, "D") > 0 _ Or InStr(1, x, "E") > 0 Or InStr(1, x, "F") > 0 Or InStr(1, x, "G") > 0 Or InStr(1, x, "H") > 0 Or InStr(1, x, "I") > 0 _ Or InStr(1, x, "J") > 0 Or InStr(1, x, "K") > 0 Or InStr(1, x, "L") > 0 Or InStr(1, x, "M") > 0 Or InStr(1, x, "N") > 0 _ Or InStr(1, x, "O") > 0 Or InStr(1, x, "P") > 0 Or InStr(1, x, "Q") > 0 Or InStr(1, x, "R") > 0 Or InStr(1, x, "S") > 0 _ Or InStr(1, x, "T") > 0 Or InStr(1, x, "U") > 0 Or InStr(1, x, "V") > 0 Or InStr(1, x, "W") > 0 Or InStr(1, x, "X") > 0 _ Or InStr(1, x, "Y") > 0 Or InStr(1, x, "Z") > 0 Or Len(x) 4) Then Dim txtsql As String Dim tmp As String tmp = x x = "*" & x & "*" txtsql = "SELECT Movies.MovieID, Movies.Title, Movies.Year FROM Movies WHERE Movies.Title like '" & x & "';" DoCmd.OpenForm ("Info") Form_Info.Visible = False Form_Info.searchbox.Value = tmp Form_Info.Result.RowSource = txtsql Form_Info.Visible = True Call i1 End If End If End Function
Many thanks for any help, i'm really stuck here.
sorry for any bad English, i'm Dutch.

Kind regards,
Guy Dols

Hi all, Access rookie seeking help here. We've got both Access 2007 and 2010 running as front ends for our SQL db on several computers at work. I've got a small Autoexec VBA program to automatically update the Access database that is loaded on everyone's desktop:

Code: '>>Here's the declaration: Option Compare Database Option Explicit Declare Function apiCopyFile Lib "kernel32" Alias "CopyFileA" _ (ByVal lpExistingFileName As String, _ ByVal lpNewFileName As String, _ ByVal bFailIfExists As Long) As Long Sub CopyFile(SourceFile As String, DestFile As String) Dim Result As Long If Dir(SourceFile) = "" Then MsgBox Chr(34) & SourceFile & Chr(34) & _ " is not valid file name." Else Result = apiCopyFile(SourceFile, DestFile, False) End If End Sub '>>Here's the autoexec fxn: Function DatabaseLoad() On Error Resume Next If DLookup("VersionID", "tblVersion") DLookup("VersionID", "tblVersionMasterELISA Log") And Err = 0 Then MsgBox "Your version of " & CurrentProject.Name & " is out of date." & Chr(13) & Chr(10) & "Please reopen the database after it closes.", vbInformation, "Updating database" CopyFile "X:Database.mdb", CurrentProject.FullName Application.Quit acQuitSaveNone End If End Function

Specific error is that the switchboard form on the db does not load using Access 2010 after the update. Everything else seems to work. Also impossible to manually open or design form after the update, since Access says it now cannot recognize data in that form ?! 2007 Access works perfectly, no problems.
I've already turned off everything obvious in the Trust Center, as well as designated my source location as a trusted location. Plz help!

I am trying to use one form to view two different categories of the same information. In database speak I mean that all of the information is coming from the same table but I use a field in the table to classify the record as either active or inactive (yes or no control).

Here are the categories:
Lessons that are active
Lessons that are inactive

These two categories can be further filtered by all lesson that are inactive or active or a subset of lessons within a specific division that are active or inactive.

When the user opens the form he/she is presented with a dialog form with a combobox and an option group control. The user select the appropriate division from the combobox and then selects either Active or Inactive from the option group control. I have added an row in the combobox so that the user has the option to view all the records that are active or inactive. Now when the user clicks the go button on the dialog form, I want the main form to filter the records that it displays based on the combobox and option group control from the previous form. In addition, I have a listbox on the main form that displays the Lesson Number and Lesson Title of all the records in the filtered division. The user can quickly select a lesson from the listbox to view detailed information about the lesson.

I can get this to work perfectly if all I have on the dialog form is a combobox to select a division. However, when I add the option group control all the filters fail.

Details of the fail's:
1. Listbox is empty but details of the first record in the table display in the fields on the form.
2. Listbox is limited to the filters from the dialog form but the first record in the table, which is not part of the filter, displays in the rest of the form fields.
3. All records display when inactive is selected in the option group control.

I am trying to find a way to do this without having to create another form and query with different filter values.

Here is my current code by object that relates to this issue. Again this code works but I want to expand on it to accomplish what I stated above.

1. frmDivSelect: Dialog form that opens when the user opens the mainform

Code: Private Sub gotoLessonCard_Click() Dim strForm As String Dim strWhere As String strForm = "frmLessonCard" strWhere = "[qryLessonCardDataFilter]![LDivision] = " & Me!cboDivSelect & "" If Me.cboDivSelect.Value = 0 Then Me.cboDivSelect.Value = Null DoCmd.OpenForm strForm, acNormal Me.Visible = False Else DoCmd.OpenForm, strForm, acNormal, , strWhere Me.Visible = False End If 2. frmLessonCard: Mainform receiving the filter criteria from frmDivSelect; code that is behind the listbox.

Code: Private Sub lblLessonList_AfterUpdate() DoCmd.SearchForRecord , "", acFirst, "[LNumber] = " & "'" & Screen.ActiveControl & "'" 3. qryLessonCardDataFilter: The underlying record source for frmLessonCard.

Code: Field: Inactive Table: tblLCData Criteria: 0 or: 0 Field: LDivision Table: tblLCData Criteria: [Forms]![frmDivSelect]![cboDivSelect] Field: [Forms]![frmDivSelect]![cboDivSelect] Show: unchecked or: Is Null Yes, this is along post, but if you have made it to this sentence I hope that you will be kind enough to add some water to my knowledge bucket and not throw another log on my fire.

Thanks, Sean

"We, the willing, led by the unknowing, are doing the impossible for the ungrateful. We have now done so much for so long with so little, we are now capable of doing anything with nothing." - Unknown

Hello, I am working on my first access database for a non-profit after school center. I'm trying to build a user friendly database from which all employees can access our community partner organizations, the specific contacts with whom we've worked within those organizations, and the history of relationships with both organizations and individual contacts. The histories are sort of long, but when I open the form they display in one inch wide and one row high tiny text boxes that are almost impossible to use. It would be really nice if my data all just resized based on the amount of information. Is this possible? I tried turning autoresize on in the properties menu accessible in design view, but nothing changed.

Thanks in advance!

We have a large number of 2003 access databases. When opened in Access 2007 it is impossible to right clidk on say a report and open in design view. Nothing happens if you right click on the report. No create tab is available. If a new database is created then you can edit reports and the create tab is available. Both are in the same directory. Both have the same rights.

I've made myself a small database to catalog my photos. There were about 300 photos in a number of subdirectories to ..MyPictures. The file names, complete with explicit path, were in a field in tblPhotos, loaded by a small routine.

I then started flopping around with code to update the FileData spec of the child recordset for an Attachment type field. Don't know about you, by my code is rarely right the first time. Found that after fixing one bug the code started hanging up when I tried to update the child field. I was told I was creating a duplicate value. Curious. As far as the table was concerned, the attachment field had no values. After a couple of iterations I decided to compact the database and got a message to the effect that another user had the database open. Double curious - this was totally impossible.

What resolved the situation was shutting down Access, reopening the database then compacting it. I was then able to run my code to update the attachment field with the file name.

So here's a solution in case anyone else runs into a similar situation.


Not finding an answer? Try a Google search.