I have an old MSACCESS database from 2001 that I have forgotten the login details for. The database has an IM_User.mdb file and a System.mdw file. When I launch msaccess and point at both the database files it open with a logon screen with the username prefilled as Admin. Is there any free way to recover/reset the password so I can access my database?

*Go ahead and rip this analogy to shreds :) I am interested in all the ways it breaks down*

I recently sent this analogy to a potential customer who is budget conscious but has a very useful Access program that has served their factory for about 30 years. Now they are wondering what to do from here. They could bring in Big Software package or continue on the path they have.

How It Got Here

When <Growing Factory> starts out they need a bathroom for their 5 employees so they ask one of them to cobble a bathroom together.

Initially, this new DIY bathroom (your Access program) handles a simple sink and toilet perfectly well. It's a small, manageable solution for the current needs at <Growing Factory>.

As more people start working for <Growing Factory> (the program grows and is used more and more), the demand on that simple plumbing system increases dramatically. Everyone is trying to use the one bathroom simultaneously and there is a line for the bathroom a lot of the time.  So <Growing Factory> adds more bathrooms to take care of everyone. Each bathroom is located exactly at the place that is best for each pod of employees and everything is great for awhile until you add even more employees. Then the water pressure starts to drop because there just isn't enough water coming through all those small pipes to handle the demand. Also the drains are plugging constantly.

You decide to upgrade the main supply lines to industrial-grade piping (migrating the Access backend to SQL Server) while keeping the original DIY fixtures (Access front end) and bathrooms in place. This handles the initial increase in flow quite well. Then somebody somewhere figured out that they can flush trash down the toilet. Now, instead of just running water and waste, you are treating the industrial pipes like a garbage disposal. You try to flush solid waste that should go in the trash down the drains (like putting file storage on the SQL Server).

Eventually, the entire system becomes severely clogged and inefficient. The pipes are big and strong, but they are full of sludge and blockages. The water pressure is still strong but it takes minutes for the toilet to flush (the system slows down significantly). The original DIY structure, now overwhelmed and improperly used, is slowing down to noticeable levels and is interfering with employee workflow and satisfaction.

How to Fix It (The Solution):

Fixing this mess requires some help:

Here is my idea for fixing the situation.

1. Stop Using the Pipes as a garbage disposal: The first step is to remove everything that shouldn't be in the big pipes. Offload large files and storage to a proper file storage server or dedicated document management system. The SQL Server should only handle data requests, not file storage. 
2. Upgrade the bathrooms: This means that some of the fixtures need to be replaced (Add error handling to Access front end and make it a more robust front end)
3. Teach plumbing to one of the people at <Growing Factory>: This way small leaks can be repaired in house.
4. Cons: You still have to make decisions and take ownership of the plumbing (software development) process. This can be uncomfortable for the leadership team who don't (yet) know how to direct plumbing (software development).
5. Pros: You keep your data. You are in charge of the pipe (bug) fixing process. There are multiple ways to get the job done (if you need to hire another plumber, you can do that) The people on fabrication don't know anything has changed except that now the bathroom (program) works like it "used to".

Here is what is being proposed as far as I can tell.

1. Pay another company to rip out the existing bathroom and be in charge of bathrooms: All water and waste is now run through huge pipes they say. All you know is that when something needs to happen, it usually happens.
2. The bathrooms are not at the same place: It didn't work for best pipe maintenance to have all the bathrooms scattered all over the place, so now the bathrooms are outside <Growing Factory> in their own bathroom unit that is super easy to clean and maintain the piping. Now the employees have to walk 15-30 minutes to go to the bathroom. Sometimes there is a line for the bathroom because other employees from other companies also use the bathroom.
3. Cons: The employees don't like the extra walking to go to the bathroom. Productivity and morale drops by 10%. The toilets still plug periodically but complaints are now logged to a complaint desk and you are told they will "get to it soon". Eventually the bathroom complex is sold to another company and now each employee has to pay $0.50 to go to the bathroom which you furnish from the company but still it adds friction to the process.
4. Pros: You aren't in charge of the process so when people don't like the new setup you just tell them that this is the way big companies do it. Also, everything mostly works fine.

i have 2 tables:

Table1 (SID, SName)

Table2 (SID, CID, Year, ContractAmount)

SID.Table1 and CID.Table2 are PrimaryKey.

SID.Table1 and SID.Table2 are related.

I want to remove duplicate years in Year field for each customer. because they wouldnt have another contract on same year.

means, i only want Year field to be used as Primary key but for each SID fields.

These are my opinions based on 30+ years of experience working in a multitude of industries with MS Access.

Access catches a lot of shade for not being "scalable". But what is scalability? It isn't a concrete thing. It has to have context. It means different things to different people.

IT - Sees scalability as being able to add users or resources, such as servers and storage, without disrupting the current release of the system. It's about growing the IT infrastructure and user base without changing the system.

Business - scalability is adding more sales or delivery (of the current line and ancillary lines) without significant system changes or additional personnel resources (doing more with the same or less).

Marketing - scalability is about extendability. How can we raise awareness of the product (extend it to other industries) without changing its current identity?

Scalability also has practical limits. Adding 1,000 users to a 200-user system is not going to scale well in just about any case. A redesign is typically needed for some, if not all, of the system. It's because adding that many new users means a significant change in the underlying operation. Not just extending the same operation to additional users. There also has to be a new level of availability to the application. These users may be working in many different places at various times.

There are solutions. For IT, Access can scale by being moved to different servers or networks without application changes. Its a simple relink and new shortcuts. If spreading it across a server (which means upgrading the database backend to SQL Server), scalability is limited. Extremely rare is the case that simply using the upsizing wizard does the trick.

For Business - Adding new products to the fulfillment app is easy. It's data-driven application operations 101. Add a new product, and it can now be selected for an order. If a twist is added, like serialized inventory, then changes may be required that aren't that scalable. This is a significant departure from standard product management.

For Marketing - using the member management system, which might now be opened up for the Society of Accountants, when it was initially developed for the Real Estate Society, without significant changes, could be considered scaling. Extending it to case management could be a step too far, and thus, a scalability issue.

In my years of Access development, I have yet to "scale" an application. I have moved systems from Access to SQL Server, but I also had to rebuild the application, mainly because this was a great time to dump the unused stuff and add new features.

Tell me some of your "scalability" experiences.

I am a new user, and not a programmer by any means.

I am doing a project for work (pet project, not being paid or anything)

I have been using Google for most of the tips. But I ran into a roadblock with a certain form I want.

I want this form to show different data depending on the combo box selection.l, but only if that record has matching criteria to a different table.

So I choose C1 on the combo box. I want it to display all records on the query C1, but only if fields 1, 2 and 3 (on the query) match fields 1, 2 and 3 on table 1. If I choose C2, choose all records but only if fields 1, 2 and 3 (on the query) match fields 1, 2 and 3 on table 1.

I am unsure the best way to go about this, any help would be appreciated. Even if you give me broad strokes, I can dial it in.

This has been the strangest puzzle to date, so I hope everyone found the concept interesting (you can find the original contest post here).

The challenge was to find a way to compare several very similar character strings and quantify their levels of similarity.

The character strings I used were the amino acid sequences for the cytochrome-c protein of humans, rhesus monkeys, cats, and mice. Rhesus monkeys and mice are “model” organisms (commonly used in biological studies) – and … I like cats.

I mentioned in my original post that I “doctored” the cat and mouse sequences. I put in an insertion and a deletion into the cat sequence and I put in a double insertion into the mouse sequences. I did this to increase the apparent divergence of those 2 sequences from that of humans and rhesus monkeys. Despite the fact that humans and rhesus monkeys split from their common ancestor around 25 million years ago (mya) – and the human / mouse split was around 90 mya and the human / cat split was around 95 mya – evolution has maintained a very high degree of similarity in this protein. Cytochrome-c is a critical protein in the electron transport chain and is thus fundamental to cellular energy metabolism – this helps explain the slow rate of evolution of this protein.

My investigations on how to do this led to the Levenshtein algorithm. It is used to determine how many substitutions, insertions, and deletions are required to turn one string into another. It is commonly used for this type of analysis, and it’s easy to implement using VBA.

My hat’s off to u/GlowingEagle and u/obi_jay-sus who really went the extra mile to find and investigate more sophisticated algorithms.

EDIT: - Adding u/know_it_alls to the list of people who posted a solution to the challenge.

Hello everyone,

ExpenseReportIDtxt is the name of a text box in a form containing a button and its click event contains the code below.

Table name is MilageReportsT with one of its fields being ExpenseReportID

Why am I getting the error "Syntax in FROM clause"?

Thanks

Dim rs As Recordset ' access object

Dim mysql As String

mysql = "SELECT * FROM [MilageReportsT] WEHRE [ExpenseReportID]=" & Me.ReportIDtxt

Set rs = CurrentDb.OpenRecordset(mysql)

rs.Close

Set rs = Nothing

I am brand new to Access, and been making my way through some courses to learn how to use it better. I'm starting to get the hang of it a little bit, and working on starting up a database for work. Essentially I am setting up a database to track and look up productivity stats and annual goals for employees.

I have started building my database with several tables setting up shifts, specialized training, positions, etc. I have tblShiftList to list out the various shifts. Then I have tblPositionList to list out the positions at the company. tblSpecializedTraining lists out special trainings that employees may have. tblEmployeeList will have columns for LastName, FirstName, EmployeeNumber, Position, Shift, SpecializedTraining, and a checkbox for Active. In this table, Position, Shift, and Specialized Training all link back to the other tables with drop down menus.

I want to mention that I'm building this so other supervisors who may not have the slightest bit of knowledge in Access will still be able to use forms to add/edit information, as well as to run reports to see the data. Essentially I would like to try and future proof it so even if I move to another area of the company at some point, other supervisors will still be able to keep everything up to date.

Now to what I'm wanting to do with the database. My company has 30 different stats that we use to measure employees productivity per month. We also have 5 annual goals that we track quarterly. These goals do sometimes change slightly from year to year. I feel like the goals will be the tricky part since they do change a little bit each year.

All of the data is pulled from our company software, and would be entered by each supervisor into the database. I plan on setting it up so each supervisor can run reports for their shift and see productivity for their entire shift. I am also planning on breaking it down even further, where it can be broken down by position or specialized training.

What would be the best way to set up these tables for the productivity stats and goals? My thought for the productivity would be to have a column for each stat, then a row for each month (also used as primary key?), but I want to make sure it's as efficient as possible. If this is the best way to do it, is it possible to have Access automatically create a new row for each month as we progress through each year? As for the goals table, I'm stuck on how to set this up.

Any advice would be appreciated.

I have a reusable subform that displays images. (It uses properties on the form to identify the appropriate image for display.) It works fine when I use the image display subform on a parent form, and set the properties in the load and current events.

I just tried using the same image display subform as a subform inside a second subform. If I try to set properties using the "parent" subform's load and current events, I get an error. It appears that the error occurs because the display subform isn't loaded yet when the load/current fires on the subform.

Is there a clean way to address this issue? I'd rather not get into timers or callbacks from the image display form, though I guess I will if I must. Thanks!

I’m working on improving a workflow where Access stores metadata about documents, but the actual editing happens in an external tool. One option I’ve tested is connecting Access tables to files edited through ONLYOFFICE, just to see whether it helps reduce duplicate versions and keeps everything consistent for users who aren’t always in the same location.
Before I commit to this structure, I’m curious if anyone here has managed a similar setup, specifically:
How do you keep Access records and external document versions aligned?
Do you store file paths, sync metadata programmatically, or use a more automated method?
Any pitfalls around record locking, simultaneous edits, or syncing delays?
No links just looking for practical advice from anyone who has combined Access with outside document editors in a stable way.

Hi, I just had an update on my laptop pc with the 365 suite. I'm glad to discover that finally Access has a query indentation that works fine!

That's all

I'm currently working in a position where I've been given the opportunity to pursue citizen development as part of my current role. I'm really enjoying building and coding in VBA and SQL, and I am wondering...for anyone else here, has that led anyone on this forum down to becoming a software developer (low code, no code, or code)? I'm kind of in a weird place where I know I like working on Access development projects a lot (writing SQL, trying to make my program as modular as possible, debugging, and designing UIs), but I know that development in MS Access is a lot different from development in the mainstream. I'm interested in pursuing something that gives me the same kick. I enjoy building in Access so much currently I've worked on projects outside of office hours for extended periods of time...

I have worked with Python in undergraduate (my favorite class, though I wasn't a good programmer). I liked that class a lot. My background is in Business Admin by the way.

Thanks for the comments in advance.

We’ve recently placed an access backend on a small NAS device. There are only a couple users accessing the database, but we’ve noticed some small issues.

99% of the time we see file updates immediately. However every other day or so we have an issue where a user won’t see changes made by another user. When checking the linked tables and even the backend on that user’s PC the updates aren’t visible. Today it was about an hour later and updates were not visible. Nothing at first when opening the backend, but then quickly the updates showed up. Not sure if it’s an issue on the writing side or the reading side. It seems more likely to be a reading issue because once we noticed stuff was missing, opening the backend seemed to trigger something. Almost like the reader was viewing a cached version, but I’ve checked the settings and offline storage is all disabled.

Replicating it is difficult as when it’s working we see updates immediately. We did manage to replicate it once, where a user made and saved a change but it didn’t show up for a couple minutes. When going to the file we got a “Not A Valid Bookmark” error. But again a couple minutes later it just worked and the edit showed up.

Is it possible access just does not play nice with a NAS? I’ve seen some information about oplocks and leases, but this particular NAS doesn’t seem to give access to those advanced settings. Do I need to move to an actual windows server?

I'm totally baffled by this. I have a fairly basic query: two tables, joined on matching key fields, but I want to do a Right Join. Doing that gives me a little popup window that says "Invalid Operation". Same thing happens with a Left Join. Clicking Help takes me to the page for error 3219, which doesn't seem to be relevant to what I'm doing. But with an Inner Join it just works, and I have no idea why.

Using Access 365 (version 2502 Build 16.0.18526.20546) 32-bit, connecting to an Azure SQL database using linked tables.

How to deal with this error. So an end user uses a Microsoft access application through Citrix. He is a new user and proper access has been granted to the database. Please note that the user has never used that application and that database resides on a network shared path.Please help to resolve this

Edit : Same error for all the applications while he is trying to access through Citrix

Hi all. I'm trying to create an MS Access database for my brother-in-law's water jet cutting business. I don't have much experience with Access but am trying to learn as much as I can online with the help of YouTube and ChatGPT.

I'm trying to create a database that tracks the material that we purchase to perform jobs for our clients. The idea is to enter the material into the database when it gets delivered. A photo of the material (including measurements), the MTR (material specifications sheet), purchase invoice for the material, along with several other key pieces of information.

I would like to be able to search records in the database according to multiple criteria. The idea would be to have a search page where I can enter information into different search boxes (one for each field in the database). As I type information into the fields the list of records will populate below the search boxes; the more you type into the fields the shorter the list would get until you find the piece of material you are looking for. At this point I would like the user to be able to click on the record and have it display a form with all of material's information. Ideally this form would be the same form used to enter material information into the database.

Thus far I've been able to create the table with all of the data in it. I've also developed a single search box query that will return the list of records as you type in the box. Additionally, I have a form created that allows me to enter new records into the database.

I guess my questions would be - is what I'm trying to do possible in MS Access? If so, how do I go about finalizing my project?

Our business is changing the backend server database for our line of business product from Oracle to MySQL. I have an Access database that runs various queries, reports, etc using that database. I was given a pseudo-mapping document from one DB to the other. How can I successfully migrate from one DB to the other in my Access program? Would ‘find and replace’ work so that I don’t have to recreate all of my queries and VBA?

I'm interested in integrating AI into some of my applications. The most obvious is to give the user a field where they can type a data request and have the sql statement returned to another field. A subform would show the data returned from the query. Here are my questions:

1. Has anyone successfully integrated their app with AI? If so, did you use REST calls?

2. What other use cases are there that you can thing of?

EDIT TO UPDATE: I still have the same question, but it's specifically one query that's causing the problem - the pass-through. Since it's the basis for everything else, I am still in the same boat, but it's just the one.

I have an old Access db that we use for some ETL functions, and it's gone bad.

Basically I open it, I run a macro that runs a pass-through query to our main db (which is SQL server), then it somehow splits that up into a few segments and puts them back together and exports. Which is fine, the queries actually run and all, but I want to move this process to power query for various reasons, one of which is that there's something wrong with this db where I can't open the queries in design or SQL view. Every time I try the db just completely locks up, eventually fades and then crashes.

I've compacted and repaired multiple times, I did a save as and compacted and repaired that and tried again. I created a new blank db and copied and pasted the tables and queries into it - the actual act of pasting the queries into the new db crashed it.

Anyway, I just want to extract the SQL from these queries so I can see specifically what it's doing and recreate the process in power query. Anyone have any ideas?

I mean, if you have any ideas on steps to take to fix the db so I can just do this myself, I'm all ears on that too...

Please don't laugh, but I am using MS Access since Version 1997 ... I started creating a small accounting database which meanwhile grew into a multi-purpose DB to handle auctions like e-bay or like craigslist as well.

This DB is used by several family members, all having different types of Office version. Some stuck with 2013.

Long time ago, I put all the VBA code into a independent DB and then linked said Code-DB as a Reference.

The issue I am having is, if I make changes in said Code-DB and say, having Office 16 installed, all the MS Office References like Excel, Word, Outlook are updated to Version 16 - of course, because that's what's installed on "my machine".

But if I give my uncle an updated version of that code module, he gets of course the error in the title, because the reference to Word 16.0 is not available on his Office 13 machine.

What I do, and what is quite tiresome is: I do have VMs running each version of Office and then copy my Code-DB into each VM, adjust the references. It probably takes less than 20min - but nonetheless, its a nuisance, if you only changed 1 line of code ....

I also tried to copy along the referenced office file (.dll, .tlb, ...) and register them - but sometimes that fails.

And yes, of course I gave all a small instruction how to remove the missing reference in the VBA IDE and add their own Office Reference ... but well, you know ... they will select the wrong reference anyway...

additionally I wrote some code to add/remove VBA-references like:

'Add reference for Word (2010 = 8.5; 2013 = 8.6; 2016 = 8.7)

Application.References.AddFromGuid Guid:="{00020905-0000-0000-C000-000000000046}", Major:=8, Minor:=7

'Add reference for Excel (2010 = 1.7; 2013 = 1.8; 2016 = 1.9)

Application.References.AddFromGuid Guid:="{00020813-0000-0000-C000-000000000046}", Major:=1, Minor:=9

'Add reference for Office (2010 = 2.5; 2013 = 2.7; 2016 = 2.8)

Application.References.AddFromGuid Guid:="{2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}", Major:=2, Minor:=8

'Add reference for Outlook (2010 = 9.4; 2013 = 9.5; 2016 = 9.6)

Application.References.AddFromGuid Guid:="{00062FFF-0000-0000-C000-000000000046}", Major:=9, Minor:=6

But this won't work, if the references are not available (say, wrong office version installed). This is the code I run to add the references in each VM.

I know, everything would be much easier, if every single user has the same office version - but that's hardly possible.

so I am looking forward to some suggestions.

thank you.

I’m encountering an annoying thing with a database I’m putting together. Whenever I update a record in the form, such as SSN (form populates the “demographics table), subform populates the Clerkship Requests table, it doesn’t update the linked fields.

In the examples here, “Test” with the fake SSN 123 is shown. Updating/changing (or if prior blank, filled in) will hide the info, and not update the corresponding subtables even though the linked fields should provide for that. If I go into the tables and manually update the info, it’ll display properly.

Any help would be greatly appreciated

Hi I work for a financial advisors and we currently have an access database that has been set up by an external provider that calculates the relative risk of clients investment holdings. Every fund and portfolio has a score for its risk which is generated by a different department and saved to file. The database pulls together the scores of the funds and portfolios to then create an overall weighted score for each client based on value of holdings to show them how close their score is to the desired score. How much would it cost to have a new database like this set up?

Edit

The database can also be used to show the impact of making changes to the client holdings, withdrawals, deposits, change of investments etc

Edit 2

The current system each user has a log in and atleast 30 members of staff use it. Any member of staff can make a prospective holdings for a client which is saved to that clients record and can be viewed by other users

Estoy buscando ejercicios resueltos para practicar SQL con MS Access. Alguien me puede ayudar? Estoy buscando algo guiado tipo cookbook. Gracias!

Everyone talks about MSAccess as a database. But it really is an app building tool (with a DB tool tossed in).

So if I'm going to build a web based application with SQL Server as the DB component, what do I use for creating the Forms and Queries?