Microsoft Access





Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

Share tech news, updates, or what's on your mind.

Sign up to Post

I have a delete Command Button create on a split form which does not working. I take so many time. So, I browsed through the discussion board and found a suggestion.

Thanks you
Hi, guys.  
I have the query shown in the attached file which produces the displayed results.  
What I want to know is would it be possible to construct a query that will produce four additional fields with running totals for each week for each associate.
In other words, I would like to have fields labeled “Total Reviewed,” “Total Passed,” “Total Failed,” and “Failed Pct” that would be incremented for each week for each adjudicator.  
The row for the first week for Adams, Rashod would have in those fields 1, 1, 0, and 0.00% respectively.  The row for the second week for Adams, Rashod would have in those fields 2, 2, 0, and 0.00% respectively.  The row for the first week for Aleno, Myrta would have in those fields 2, 2, 0, and 0.00% respectively.  The row for the second week for Aleno, Myrta would have in those fields 4, 4, 0, and 0.00% respectively.
Is there a way to construct a query to produce this result?  Otherwise, I would have to export the results of the current query to Excel and manipulate the results in Excel.
Thank you very much for all your help.
Errol Isenberg
Government Operations Specialist

 I am running the below code and getting lock violations even though I am closing all forms prior to the update statement.

 When I grab the code from the Immediate window and run it as a Query I do not get the lock violations.

 Not understanding the difference.

 Dim strSQL As String
 Dim temp_CRAS_CAR_Id As Double
 Dim Tmp_Testing_Start_Date As Date

 temp_CRAS_CAR_Id = Me.CRAS_Car_id
 Tmp_Testing_Start_Date = Me.Actual_Testing_Start_Date

 DoCmd.Close acForm, "Frm_Reviews", acSaveYes
 DoCmd.Close acForm, "Frm_User_Dashboard"

 strSQL = "Update tbl_Review_Main SET Actual_Testing_Start_Date = #" & Tmp_Testing_Start_Date & "# Where CRAS_CAR_Id = " & temp_CRAS_CAR_Id & ""
 Debug.Print strSQL
 DoCmd.RunSQL strSQL

 strSQL = "UPDATE tbl_Review_Plan INNER JOIN q_Review_Plan1 ON (tbl_Review_Plan.Step_Order = q_Review_Plan1.Step_Order) AND (tbl_Review_Plan.CRAS_Car_id = q_Review_Plan1.CRAS_Car_id) SET tbl_Review_Plan.Planned_Date_On_Actual = [q_Review_Plan1]![Planned_Date_On_Actual]" _
 & " WHERE tbl_Review_Plan.CRAS_Car_id= " & temp_CRAS_CAR_Id & ";"
 Debug.Print strSQL
 DoCmd.RunSQL strSQL

 DoCmd.OpenForm "Frm_User_Dashboard", acNormal
 DoCmd.OpenForm "Frm_Reviews", acNormal, WhereCondition:="[CRAS_Car_id]= " & temp_CRAS_CAR_Id
I created a database,  split it, and then I imported the tables to MYSQL. How do I connect the frontend of my db to the MYSQL backend?
I am currently putting together a new Access 2016 Database. I presently have a main form which uses names as the main table. In a subform, I have an appointment form which is comprised of linked tables. In this case the Appt form has a table that includes project, time and date information that is linked to a table that included address and contact information. These forms are connected as a one to many relationship with contacts being the one and appt time/date/project being the many (One contact can be associated with many appts). The main form is connected to the subform with a one to many relationship, the name being the one and the appts being the many.

The Tables and Fields are as follows:

Main Form
Control Source - Table:

Control Sources - Tables:

(These tables' fields are combined into one subform related the to the main form)

My question is this, I want to be able to type into one of fields in ContactDetailsTable and see if that record already exists and if so associate the entire rocord with the appt record.  If not, then I want to add a new record to the ContactDetailsDatabase. What is the best way to do this, can you?

IN other words, is there a way to bring up a combo box in say Company that will …
I'm trying to import tables from my Access database, I was actually successful at importing one table, but since haven't been able to import anything. I'm using the same data source and I can see the tables but they won't import to the MySQL database I selected.
I have a database providing bid options to clients. Every bid has a "GOOD-BETTER-BEST" option - always. I want to display the data on the report with the headings on the far left and then the "GOOD-BETTER-BEST" data across horizontally... kind of like a slot machine. I have tried a cross-tab query and it will not allow me to show all the details the way we need to. Please help or advise if this simply is not possible in Access 2016
I have a combobox whose rowsource is a query. It should display 4 columns. The problem is that when it is first run the 4th column does not display. If I close the form and reopen then the 4th column will display. I've determined that the query is correct but the column returns a null value. I have tried requery but it doesn't help.
Hi - I'm trying to sum the amount in a report that is Grouped by Orders, the group will show Capital and Expense orders.  Trying to show separate total amounts for Capital and Expense orders in the Report Header.  

I'm using a text field with    =Sum((SELECT [Amount] WHERE [OrderType] = "Capital")) but getting an #Error

Could anyone advice how I force a zero into a blank field in a query:

I have 2 cost columns and just want to deduct one from the other, so I need to force a zero into one of the columns where is doesn't return a value.
I have a very single macro in access which is executed when I create a new record in table1   (after insert) , table1 has 2 fields: date of start and date of end.
When I add a new record, macro should create so many records in table2 as the intervale of dates I have entred in table1.
for example, if I enter in table 1 : 1-1-2017     1-23-2017
my macro should create in table 2 next records:

for any reason the macro creates only 8 records, from 1-1-2017 to 1-8-2017, I think maybe is caused by sequrity reasons in macros of access.
Is there any solution?
thank you
In MS Access 2010 a user is encountering the message "ODBC Call Failed" when trying to preview a report. The data source for the report is a linked table from SQL Server 2008 R2. Another user using this same machine can execute the report preview without any issue. Guessing here that there is a SQL Server security issue with the user that is having difficulty. However I can find no obvious difference in the SQL Security between the two. The ODBC bridge is set up the same for both users as a Trusted Connection. Any idea what the issue might be here ? Not sure how to troubleshoot this. Thanks.
I have linked tables in a sql database in my Access database.  I need to build a function that will accept the name of a linked table, and return the name of the server on which the table resides.  

I already know how to query MSysObjects to get the .Connect string, which tells me the name of the DSN used for the linking
((SELECT  *FROM MSysObjects WHERE (((Type)=4)) OR (((Type)=6))  or   CurrentDb.Tabledefs("mstLookupTb").Connect or Dlookup("Connect", "MSysObjects", "Name = 'mstLookupTb'")).  I don't need the name of the DSN;  I need the name of the server.  In this screenshot, I need to get "Sandbox01", not "PaulsSandbox".
User DSN "PaulsSandbox"
Dear Team ,

Kindly assist me for the below requirement  I have template in excel where i like to make it in excel as these value entries will exceed around 300 -400 lines . Based on this i will filter accordingly the categories for reporting .

Can you please help me out and advise, how can  i create the excel field as attached in access

Highly appreciate your support

Im not a programmer so please bear with me.... i need help filtering time in my report...

the code the im using is

the result shows all the time from 7:30 to 8:00 and 12:30 to 13:00 now i want to only show the time range from 7:30 to 8:00 ..anyone please help... thanks
I placed 22 command buttons in a Form Header, but when I want to open the Form, the command buttons
resize themsleves and the captions are not visible. How can this prevented? Is this happening because
there are too many command buttons in the Form Header? If so, what can I use instead of command buttons?
I have a form with a combo box, the dropdown list have all the queries.  My idea was to selecta query the userend wants to view, and it reveals the current query.  The form does not know about the combo box, i'm a new programmer;  i am not sure how to perform this action through a selection.  

I'm not sure i should even have a combo box to view any query a userend should select to see.  My destination is to have a button on a form to do select (view any query).

I am planning a 5 a-side tournament and would like a simple database of players, teams and matches up to the final and some forms.

I have simple sql experience in tables, relationships and sql and would like to be able to extract relative data from the database.

I would like to use the Microsoft Access database as I have it.

Do you know where i can get something like this?

I currently have a sql function that I need to create in MS Access either by function or query.  Please help.

USE [pim89]
/****** Object:  UserDefinedFunction [dbo].[get_MN_Format1]    Script Date: 6/8/2017 4:47:51 PM ******/

ALTER FUNCTION [dbo].[get_MN_Format1](
        @pStr   nvarchar(4000)
) RETURNS nvarchar(4000)
DECLARE   @pos int, @sFormat  nvarchar(4000) , @candidate nvarchar(4000)
SET @pos = 1 ---Init variable to enter the loop
SET @sFormat = '' --Init return variable  
WHILE @pos>0
        SET @pos = PATINDEX ( '%[^0-9a-Z]%', @pStr )---Any character not number 0-9 or letter a-Z
        SET @candidate = CASE WHEN @pos<=0
                  THEN isnull(nullif(CAST(LEN(@pStr) AS NVARCHAR(4)),'0'),'')
                  ELSE isnull(nullif(CAST(@pos-1 AS NVARCHAR(4)),'0'),'')+SUBSTRING(@pStr, @pos, 1) END
        SET @sFormat = @sFormat+ @candidate
        SET @pStr= SUBSTRING(@pStr  , @pos+1 , LEN(@pStr)) ---cut to get the next string until no more format char
---optional not to shows the zeros -replace them with empty, I like it better
        RETURN @sFormat;


I am a novice learner picking off where one co worker left off with a Mircrosoft Access Database. When attempting to run a Macro, I am receiving a Run Time error of 3251. Operation is not supported for this type of object. It errors at Mytable2.Seek "=", ss. Any help is greatly appreciated!!

Public Function prrtf()
       Dim MyDb As Database, MyWorkSpace As Workspace
       Dim Mytable As Recordset, Mytable2 As Recordset
       Dim xsw As String, lastemp As String, emp As String, FIL1 As String
       Dim yr As Integer, per As Integer, mth As String, hrs As Double, nmth As Integer, nyr As Integer
       Dim fyr As Integer, gross As Double, ss As String, lastss As String, ln As String
       Set MyWorkSpace = DBEngine.Workspaces(0)
       Set MyDb = CurrentDb()
       'Set MyDb = MyWorkSpace.Databases(0)
       ' Open Tables
       Set Mytable = MyDb.OpenRecordset("NewAlldata")
      'Mytable.Index = "SSNandPeriod"
       Set Mytable2 = MyDb.OpenRecordset("PERSONAL_DATA")
       'Mytable2.Index = "SSN"
       xsw = " "
       lastemp = " "
       lastss = " "
       nmth = 0
       nyr = 0
       'FIL1$ = "O:\DATA_\ACCESS\RETDWNLD\names"
       FIL1$ = "O:\Data\RETIRED\retdwnld\names"
       Open FIL1$ For Input As #1
       If EOF(1) Then GoTo finalnm
       Line Input #1, ln
       ss = Mid$(ln, 46, 3) + Mid$(ln, 50, 2) + Mid$(ln, 53, 4)
       Mytable2.Seek "=", ss
       If Mytable2.nomatch Then GoTo nextnm
       GoTo …
I have a problem regarding pdf report generation in ms access i have a code as
Option Compare Database

Private Sub Command2_Click()

    Dim bytz1 As Byte
    For bytz1 = 1 To 36
    Me.RecordSource = "SELECT Districts.ID, Districts.District FROM Districts WHERE (((Districts.ID)=" & bytz1 & "));"

    DoCmd.OutputTo acOutputReport, "MS datapack - landscape", acFormatPDF, "C:\Users\Lenovo\Desktop\PMU\" & Me.Text2.Value & ".pdf"

    Next bytz1

End Sub

Private Sub Detail_Click()

End Sub

when i run this code it gives error as compile error method or data member not found.
I set up a MS Access front end with an ODBC to a MS SQL Server database.  When I do a search via an access form, it is taking over 2 minutes to return the result.  I'm not sure what is causing the latency.  I configured the ODBC to use the server ip address + MSSQLSERVER,Port #.  I experienced the same amount of latency using port 1433 and 1434.  Any suggestions as to how to resolve the latency would be greatly appreciated.
Access Web App
I want to append records to a table w/ Lookup fields. That doesn't seem to work using a data macro. Its to many records to type them in individually. Any idea?
Best, TT
I'm trying to run a query based on a form's text box.  I created the text box from Access' When running, I receive an error from the form:  "The Microsoft Access database engine does not recognize 'Forms![Reports Selection Form]![DTPicker7_Fr]' as a valid field name or expression."

I have other database files wherein the process works.  Any thoughts what might be the cause of the error?
Thanks in advance!

My software uses Actian 12 (Pervasive) WGE. x32 with SIMBA ODBC drivers. We have a client with a query in Access 2013 32-bit and our log files are showing that it is logging in and out of our dissents of times, slowing the query to an absolute crawl and basically unusable.

All of the DSN's work outside of these queries and the connection is lightning fast when testing. The crawl only happens with these queries.
It last worked when they were using Pervasive v11 WGE/SIMBA ODBC drivers. I am not competent in Access at all! None of us really are but we do see the problem in our software. It looks like it is requesting our software credentials every time a new record is being brought over into Access.

Any input would be greatly appreciated.

Microsoft Access





Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.