Other subreddits of interest:

What are some of the things you have implemented in Ms Access that you are most proud of and think is really cool? It doesn't have to be massively code fancy, a cascading combo box for example? Share your success!

Looking to revamp the utility of the current database. Currently is set to pull information of internal unit and is used as a reporting or research tool for my business.

I have heard of queries being set to apply changes towards the internal unit/ERP from the database itself.

Have there been any big leaps in the technology that would greatly increase the utility? I know that the use of Co-pilot is a big deal and I’m not sure if this could be an enhancement.

Hi, I still consider myself a beginner in Access but have used lots of resources (huge Reddit fan) to get my database to this point.

The issue I need help with is creating a data entry form that is straight-forward and updates the many tables that link academic manuscript information for departmental faculty. Here are the tables and relationships that have been created.

https://preview.redd.it/ay6ribrxnxge1.png?width=1242&format=png&auto=webp&s=4212e1a96cdd79f2796534c639fc56ae9f51712f

This is the form I have created so far to input information for the different fields

https://preview.redd.it/i62v1rpwmxge1.png?width=1021&format=png&auto=webp&s=6a0dc6419f96882e12c55c81cca5821102d9353e

I need two more lookup forms for the Journal name and Journal Key. I am starting to wonder if I am making this too complicated and if there is a more efficient way of doing this. Btw, getting here has taken me 6 mos. of working on my own and using help from an amazing person here on Reddit to get this far. I use Acess 2016 Bible and Stack Overflow as well, so if you know of any links that would help me figure this out, I appreciate that too.

I hope this is enough information/background to allow more knowledgeable persons to provide guidance. Thank you in advance!

Hey, could someone help me with an Access query at work? I'm struggling to figure out what's wrong and fix it. Any Access gurus out there willing to lend a hand? I'd be super grateful for any assistance. Thanks in advance!

I have a subform built off a table tmp_SalesTrans where I want to warn the user of duplicate entries before I commit a new record to the table through the form.

Sadly my code isn't catching my duplicates.

My lines debug properly, so I think my syntax is right. Is my logic bad here?

The following code is in the BeforeUpdate Event of the subform

(Sorry in advance for not properly formatting the code... reddit's markup buttons don't seem to be working right for me at the moment)

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim ID As Integer

Dim Stuff As String

Stuff = "Cust_Platform_ID = " & Me.Cust_Platform_ID & " AND " & ProdDist_ID = " & Me.Cmb_ProdDist_ID "

ID = Nz(DLookup("Cust_Platform_ID", "tmp_SalesTrans", Stuff), 0)

If ID = 0 Then Exit Sub

If MsgBox("This appears to be a duplicate entry. Do you still want to post it?", sbYesNoCancel + vbQuestion, "Possible Duplicate Entry") = vbYes Then

Exit Sub

Else

Undo

End If

Hi All, I need to create a delta(monthly variance) sales report, and trying to find the best way to do this and allow for users to access variations of it in the front end.

So I can easily do current month - previous month on products and services, but what about where there are only previous month values? Need to bounce some ideas off as I think I'm overcomplicating it.

The only other thing I am not doing is password protecting the front end database, but user controls are in place for individual forms to be launched based on user environment ID.

What would the risk be if users can hold down shift to launch the front end if the only thing that occurs at launch is a welcome directory form?

Table A has a 1-to-many relationship with Table B.

Main form based on Table A with a continuous subform of Table B. TableBsubform has a master child link: Table A PK to Table B FK.

TableBsubform is a single combobox with value list selection. During form operation, you can enter data in the subform and it collects the proper information, stores it in the table. But an error message pops up with each addition/edit record in the subform that says "An error occurred while referencing the object". The error message goes on to say "You tried to run a visual basic procedure that improperly references a property or method of an object." You can hit OK and go about your business.

There is no VBA. This type of set up has worked every other time, I feel like Ive looked into everything I can, tweaked everything I can and I am losing my mind. I can't figure out what makes this different/is the source of the error. Ive researched online and found little discussion of this error. Any clues??

I am looking for a template that can be used for a museum archive, for tracking and organizing a collection of a few thousand historic paper documents and ephemera. I don't need something as heavy duty as ArchiveSpace or Archivematica, as this is not for a huge archive, and we're looking to run off a single Windows machine. My archivist is decent with computers, but gets lost in the woods of spinning up virtual machines and servers; Access would have fewer interlocking parts that could break.

So far, all my searching yields is instructions of how to archive old data, which, while a correct use of the word, is not what I'm looking for.

Any ideas or suggestions? I'd rather not write the whole thing from scratch if a template already exists.

Hello! I'm an absolute beginner with MS Access so please bear with me.

I am attempting to create a table for an assignment using Design View, and I seem to have no option to add another Field or "column". Field Name, Data Type, and Description (optional) are the only columns I can see in Design View. I have tried scrolling down and right to see if I'm missing something on the table itself.

The steps I took to create the table was File > Blank Database > then added my Field Names in Design View. I am simply trying to add one extra column to include Field Size. Everything I have searched online seems to have different instructions that are not available for me. I feel like I am missing something extremely obvious.

Version: Microsoft® Access® for Microsoft 365 MSO (Version 2412 Build 16.0.18324.20092) 64-bit

The first picture I attached is what my table looks like, second picture is instructions from my assignment. I would be eternally grateful for any guidance!

https://preview.redd.it/qgovrp9a75ge1.png?width=1920&format=png&auto=webp&s=be3e7849e88e476a756fd89d44bd1b3bd4166811

https://preview.redd.it/i7a6s38b75ge1.png?width=723&format=png&auto=webp&s=3acdc59cfef14090a5176df85f2ab54312d4c908

Does anyone know if there is a way that network admins can throttle back performance of MS Access? I have a 300kb database and the table in there is around 1 million records. In the past Acess handled data this size with no issues. Now I can't even run a simple update query on this table. I let it run overnight and it still hadn't finished...

Hey there. Someone to assist me on Microsoft acess pls I am about learning.. I want to know properly how to creat report and others things.

Anyone here pls.

My course is actively killing me, looking for YouTubers good at explaining basic concepts

Hello folks, first time here but old Access user.

I'm desperately looking for some help on conecting an MS Access DB with an Oracle DB via ODBC.

I'm used to connecting SQL srv with Access, but I've never done this with an Access with Oracle setup. A client has a complex(ish) Access DB he needs to ODBC-link to an unix Oracle server, using severall new Windows 11 PCs with no Oracle installed, and no real plans for that. I thought I just had to pop a new ODBC connection on the Access side and that's it, but nope, doesn't work. I messed up a PC so badly, with so many atempts at various softwares and drivers, I ended up doing a format C: to start afresh...

So here I am. Assume a clean PC, with just Office 365 installed. I have the server's IP & name, the instance & service name. What do I need to do in the PC, so that I can create an ODBC link in the Access DB?

Any help is greatly appreciated!

Hey everyone, I am looking for advice on where to start with this problem.

To be clear, I haven't used access in years and am prepared to re-educate myself for this project. My organization struggles with timely information analysis. We have ~300+ people of over 80 jobs types consistently going place to place. Often, we receive requests for a group of these employees to go support in parallel to the continuous operations.

The catch is: we have key tasks that have assigned minimum personnel requirements. 2 people from this job for this task or 20 different jobs for that task. It is important to our operations chief to be aware if we're able to support those key tasks despite loaning people out for the continuous jobs.

My question, is MS access a good place to start to compile data tables for all the different teams? Several teams make a section and the sections feed the operations chief. My idea is if each section updates their linked table weekly, I can create a database where it is easier to analyze total strength or capability across the whole.

Are there good starting points for this? I'm imagining a whiteboard for tracking the structure of it all

Thank you for your time reading this post!

I have an Acces front end FE and Access back end BE. I'm migrating the BE to Azure SQL. Before migrating I installed SQL Express locally to try out the migration and adapt the FE where needed.

In the FE I have the tables linked via ODBC connection to the SQL Express db.

In general the application works except there is one point where I consistently get stuck because the insert creates a record lock on the table and I don't understand why or what I can do about it.

in the vba code I do an insert in the linked table tblInvoiceHeader.

Dim dbs As DAO.Database

Set dbs = CurrentDb

dbs.Execute "INSERT ...", dbFailOnError + dbSeeChanges

As of this moment, there is a record lock. I can query for it using SQL server management studio and it returns me a record showing that there is a lock granted.

SELECT * FROM sys.dm_tran_locks WHERE resource_database_id = DB_ID() AND resource_associated_entity_id = OBJECT_ID(N'guest.tblInvoiceHeader');

I tried to use workspaces so I can create transactions and commit them but that doesn't do anything

Set wrkCurrent = DBEngine.Workspaces(0)

wrkCurrent.BeginTrans

Set dbs = CurrentDb

dbs.Execute "INSERT ...", dbFailOnError + dbSeeChanges

wrkCurrent.CommitTrans

Is there a way to set the SQL Express database to automatically commit or is there a parameter that I can pass to avoid this lock or a next statement that can trigger a commit?

Hi,

An odd one. I am running a query and says record deleted, but it is just a query of a linked table, and there is data.

Any ideas?

Same query that has been running for ages. Split database.

Hello guys,

QUESTIONS
What is the right (efficient?) way to query the users' information?
Can anyone with a similar structure share their opinions? I'm not really sure which approach is best practices. Any suggestion is appreciated!

SETUP
I currently manage an internal database with about 50,000 users. We have about 15 end users that run microsoft access application at the same time.
The users table only contains the ID. This was done to keep track of all the changes done and to reference information from the user.

When the user form loads, we grab the next tables which reference users table and get the latest record from each for their respective user:

Names: first, middle, last
Address: address, apartment, city, state, zip
Status: civil, dob, gender, language
Emails: email, type_email
Phones: phone, type_phone

BETTER APPROACH?
I am currently working on creating a user site, so that our users can register, update their information. I am thinking that querying 5 individual tables, each containing hundreds of thousands of records and pulling out the latest record that matches their user_id seems to me that is not the most efficient way of doing this.

I am thinking on having the users' table being horizontal and include all the fields from the tables mentioned above. When a user updates their user table, it will generate a record for the specific table. If the user only updated their last name then the user table will be updated, and the server will generate one additional record for the Names table.

Hello

Playing roleplaying games i am thinking of a way to use access to type down the characters we run in too so it is easy to see in a tablet (ipad). I knew about the forms part in access and decided to give it a try.

i want to be able to type in a box for example the name "vencarlo" (without quoutation marks) and all the information will be shown on that name.

i also want a button to allow me to add new non player characters that i meetthat will be added to the table.

I have just started access created a table and a simple form it is just 4 things in my table (beside the mandatory id. they are (translated) Name, Place, descripton and specific information. how do i ad a box that makes me search for a name in that box and everything containing this will be filled out?

i hope you can help me as the video tutorials does not show this nor do i know what to search for in microsoft helpwebsite

Hello all! :) can I do like this on my screen or not? I not understand how to do that

Hello everyone,

I am currently working in a project where we need to maintain a MS Access application which works as a client update tool and reads attachments from a linked Sharepoint table and stores it on the users machine.

Over New Years, an error started to occur where the application fails to write the attachment to the drive, while the code has been running like this for at least a year. The attachments are of different file types, we are reading both an .xml file as well as an .accdb file. The .xml file works well, but when it attempts to write the .accdb file, the application stops and reports the following error message:

Run-time error'-2146697202 (800c000e)':
<Unknown Error-message > HRESULT: &H800C000E

I do not think that there is anything wrong with the code, but for the sake of completeness, this is my function to download attachments from Sharepoint:

Function SaveAttachments( _
    ByVal tableName As String _
    , ByVal attachmentColumnName As String _
    , ByVal SavePath As String _
    , Optional ByVal OverwriteFile As Boolean = True _
    )

    Dim db As DAO.Database
    Dim rsParent As DAO.Recordset2
    Dim rsChild As DAO.Recordset2

    Set db = CurrentDb
    Set rsParent = db.OpenRecordset(tableName) ' <<< This is the dynamic table that attachments will be saved too

    If rsParent.EOF And rsParent.BOF Then
        Debug.Print "nothing"
        GoTo Housekeeping:
    End If

    rsParent.MoveLast

    On Error GoTo errSaveNew

    'Move to first record in the SharePoint List Recordset
    rsParent.MoveFirst

        'Loop through the list until "End of File" is reached
        Do Until rsParent.EOF

            Set rsChild = rsParent.Fields(attachmentColumnName).Value

            With rsChild.Fields("FileName")
                FileName = Mid(.Value, InStrRev(.Value, "/") + 1)
            End With

            'saving
            On Error Resume Next

            'clear file
            If OverwriteFile Then
                Kill SavePath & "\" & rsParent.Fields("Title").Value
            End If

            Debug.Print rsParent.Fields("Title").Value
            Call rsChild.Fields("FileData").SaveToFile(SavePath & "\" & rsParent.Fields("Title").Value)

            On Error GoTo SaveNext:

SaveNext:
            Set rsChild = Nothing
            rsParent.MoveNext
        Loop

Housekeeping:
        On Error Resume Next
        Call rsParent.Close
        On Error GoTo errSaveNew

        Set rsChild = Nothing
        Set rsParent = Nothing

        Set db = Nothing
    Exit Function

errSaveNew:

'   if the error number = 3022 (record is already in the index)
'   then GOTO next record
    If err.Number = 3022 Then
        Resume SaveNext

    '   for any other error type, throw a error message box
        Else
            MsgBox "Error: (" & err.Number & ") " & err.Description, vbCritical
            Resume Housekeeping

    End If

End Function

I also tried to download .accdb files to Sharepoint through the linked table within MS Access, but I am getting this error message:

https://preview.redd.it/cyhbqq6vwhfe1.png?width=395&format=png&auto=webp&s=9845b2e5553e2786fa3e76a57f98a1e7f35d53c8

It looks to me as if MS Access somehow things that .accdb files are now dangerous, but I can't find any indication to where that rule was created.

I have already tried to add a registry key to unblock .accdb attachments as suggested here, but to no effect.

Please, if you have any more clarification questions, let me know! :)

Thank you!

I'm hoping someone can help,

Up until a week ago, I could use the mouse wheel to change records in forms and then it suddenly stopped working.

I'm know that Microsoft disabled mouse scrolling as default years ago, but I've been using one of the Visual Basic codes from online to keep it working. I'm not overly technical with my knowledge of Access, but I was able to follow instructions to use Visual Basic to implement the codes.

I've tried all of the codes suggested in the online forums into the form class objects and modules but none of them are working any more. When I try to scroll, i get the message "Formatting page, press ctrl-break to stop"

I also suspect that something has changed overall with my Access. Old Access files that used to work with mouse scrolling have also stopped working, even though I haven't changed the Visual Basic code in years.

I'm hoping that someone could help with advice to fix the issue. I can upload an dummy example if it would help

Thanks very much

For reference, here is the codes I've tried to copy into my form objects in Visual Basic:

Private Sub Form_MouseWheel(ByVal Page As Boolean, ByVal Count As Long)

If Not Me.Dirty Then

If (Count < 0) And (Me.CurrentRecord > 1) Then

DoCmd.GoToRecord , , acPrevious

ElseIf (Count > 0) And (Me.CurrentRecord <= Me.Recordset.RecordCount) Then

DoCmd.GoToRecord , , acNext

End If

Else

MsgBox "The record has changed. Save the current record before moving to another record."

End If

End Sub

Public Function DoMouseWheel(frm As Form, lngCount As Long) As Integer
On Error GoTo Err_Handler
    'Purpose:   Make the MouseWheel scroll in Form View in Access 2007 and later.
    '           This code lets Access 2007 behave like older versions.
    'Return:    1 if moved forward a record, -1 if moved back a record, 0 if not moved.
    'Author:    Allen Browne, February 2007.
    'Usage:     In the MouseWheel event procedure of the form:
    '               Call DoMouseWheel(Me, Count)
    Dim strMsg As String
    'Run this only in Access 2007 and later, and only in Form view.
    If (Val(SysCmd(acSysCmdAccessVer)) >= 12#) And (frm.CurrentView = 1) And (lngCount <> 0&) Then
        'Save any edits before moving record.
        RunCommand acCmdSaveRecord
        'Move back a record if Count is negative, otherwise forward.
        RunCommand IIf(lngCount < 0&, acCmdRecordsGoToPrevious, acCmdRecordsGoToNext)
        DoMouseWheel = Sgn(lngCount)
    End If

Exit_Handler:
    Exit Function

Err_Handler:
    Select Case Err.Number
    Case 2046&                 'Can't move before first, after last, etc.
        Beep
    Case 3314&, 2101&, 2115&   'Can't save the current record.
        strMsg = "Cannot scroll to another record, as this one can't be saved."
        MsgBox strMsg, vbInformation, "Cannot scroll"
    Case Else
        strMsg = "Error " & Err.Number & ": " & Err.Description
        MsgBox strMsg, vbInformation, "Cannot scroll"
    End Select
    Resume Exit_Handler
End Function

Option Compare Database

Private Sub Detail_Click()

End Sub

Private Sub Detail_DblClick(Cancel As Integer)

End Sub

Private Sub Detail_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)

Call DoMouseWheel(Me, Count)

End Sub

Private Sub Detail_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)

End Sub

Private Sub Detail_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)

Call DoMouseWheel(Me, Count)

End Sub

Private Sub Form_MouseWheel(ByVal Page As Boolean, ByVal Count As Long)

Call DoMouseWheel(Me, Count)

End Sub

What's best practice for database design and function? I've got a heavily modified version of Northwind, and I've got an idea to add some additional client details. Should i just add more fields to the customer table, or should i create a separate CustomerDetails table?

Thanks in advance!

Hello, sorry if this seems like a basic question—I’m new to Access, and this is for a school project. I’m trying to create relationships between tables, but I can only make one “one-to-many” relationship. I understand that I need a primary key in each table, and that works, but my professor said that only one table can have the primary key. Also when I try to click on “Enforce Referential Integrity,” I get an error saying “No unique index found for the referenced field of the primary table.” In class, when we did this, all of this worked with the primary key only in one of the tables (see the attached image).

I’m not sure what I’m doing wrong, so any help would be greatly appreciated. Thank you!

Tengo el problema que genero unas planillas por pdf, pero necesito que la numeración de pie de pagina se vuelva reiniciar a partir de una condicional. No he logrado topar con la solución conociendo que es sencillo de resolver.

I have the following recordsource for a form:

SELECT tmp_PromoConcat.Plat_ID, tmp_PromoConcat.Promo_ID, [ReleaseText] & " " & [Genre] & " " & [TransitionA] & " " & [PlatformName] & " " & [TransitionB] & " " & [ProdDist_Title] & ".  In it, " & [Prod_ReccoText] & " " & [CalltoAction] & " " & [ProdLink] & " " & [Closing] AS SalesTxt 

FROM tmp_PromoConcat 

GROUP BY tmp_PromoConcat.Plat_ID, tmp_PromoConcat.Promo_ID, [ReleaseText] & " " & [Genre] & " " & [TransitionA] & " " & [PlatformName] & " " & [TransitionB] & " " & [ProdDist_Title] & ".  In it, " & [Prod_ReccoText] & " " & [CalltoAction] & " " & [ProdLink] & " " & [Closing]; 

With this code, "SalesTxt" is behaving like a 255 char limit text field. I need it to have an extended character limit of either a memo -- or if I can declare a specific character limit 500 char limit would work.

Due to how this is being built, I can't use a temp table or a query to build this recordsource.

I've tried googling for an answer but I don't think I'm using the right terms to get a solution as I can't find example statements setting the fieldsize.

I did something I don't know and now I can't access the navigation bar or other open forms. This affects only one form. What can I do?

https://preview.redd.it/7iqegqvlqwee1.png?width=240&format=png&auto=webp&s=6fb5141035a177c2f93b20ec66d22aea69bf1143

I need some advice. I'm pretty new to Access and SQL, so forgive my descriptions below. I've been using a couple books, and chatgpt to help do some things to make my job easier.

I'm a maintenance scheduler at a large company. The schedule here has about 64,000 line items, with about 30-40 relivant fields dispersed across multiple Oracle data tables.

I use access to pull the data I want to see from those Oracle data tables, for the most part it's pretty simple.

However, One of the tables is very poorly organized, and anytime I link to it, it slows down my queries to the point it breaks access. It contains some critical codes I need. For some reason, each code is an individual record, which means the table probably has 5-10 million records when consider the multiple plants we have.

I created a passthrough query to compile the data I would need from that table. I've broken it down to manageable pulls of about 14k records. The pass through itself is slow, 2 minutes ish, but it works, and doesn't break access.

When I link other queries to the passthrough, it slows down everything again. I think this is because it's trying to refresh?

I think the solution is to create a local table with the data from the passthrough filter, and linking to that instead. I'm not sure what the most efficient way to do this would be. So far I have been unsuccessful via Access with the suggestions from ChatGPT. It may be that I don't know enough to ask the question the right way. The first suggestion was to use ADO to facilitate making the table, but that didn't work no matter what version of the Library I chose.

I thought about maybe querying the results via an excel query first, and then using access to make a table with that excel file, but that seemed a little cumbersome. I think I could write a macro to do it all though, which may be the right answer.

Anyone have any suggestions in the direction I should be looking?

This is kind of a shot in the dark, but anyone ever connect to Shopify (or similar service) using their API?

I have some code that connects to the Shopify server using their API and downloads new orders, which are stored in an Access table. The db has a timer that queries the Shopify server once per minute and checks for new orders, by querying for all orders with an Order ID higher than the last downloaded order ID.

Everything is working fine, and not getting any errors. However, we're finding that after a while it stops seeing new orders, even though it's apparently still connecting to the server.

But then if I close and reopen the Access database, then all of a sudden it finds new orders, some of which may be several hours old or more.

I log all the connections, and it seems to be connecting and the JSON values it returns seem correct, except after a while it doesn't see any orders until I restart the database.

Anyone have any ideas about this?

Thanks!

EDIT:

Thank you everyone for your replies. I got more help with this than I thought I would, and I learned a bit.

I haven't solved this problem, but I decided I'm just going to implement a workaround instead. I'm going to split the program into two parts: the part that does the downloading in one file, and everything else in another (main) program file. Then, once a minute or whatever, the main program will open the download program, which will download any new orders, and then close itself, and the main program will take over with the viewing and printing of the orders.

I've been testing this process overnight and it seems to work fine, so that's what I'm going to do. Still, it's frustrating to not know what was causing this problem.

But thanks again to those who replied!

EDIT 2: I continued to try to resolve this without the workaround, and now it is resolved! See my comment at the end. Thanks, everyone!