Microsoft Access

219K

Solutions

51K

Contributors

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

Hello,

Within Outlook, I can create a link to a specific Outlook task with the EntryID like this and it works:

Outlook:00000000EA07185E98285E46A47DB888F7FB67650700B8FC64BF9761D44E9E0C88AC38FCAE2E00000000011200000311CC1B8908484EB9B38F412A8EDDCD000026A187C00000

If you paste this into an email, create a hyperlink out of it, and send it to yourself and open the email, it brings you to the task.

Now I am in Access query query design view.  I've brought this address into a field and cannot figure out how to make  link out of it.

Thanks for your help.
Joel
0
Exploring ASP.NET Core: Fundamentals
LVL 12
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

Failure on SQL Statement:  Too Few Parameters expected 1  

Dim strSQL As String
strSQL = "UPDATE UserActivityLog SET UserActivityLog.Activity2 = ""LogOff"" AND UserActivityLog.LogOffTimeStamp=Now()" & vbCrLf & _
"WHERE (((UserActivityLog.Activity2) Is Null) AND ((UserActivityLog.UserName)=[Forms]![frmLogin]![Name1]))"


CurrentDb.Execute strSQL, dbFailOnError

Open in new window

0
Hello Again Experts.

I must apologize in advance for my brevity, but I recently injured my hand in the wood shop, and typing is somewhat difficult.

The situation that I am dealing with right now is that I am very frequently experiencing conflict between changes that I make in my Access tables/queries, and keeping my VB.NET Windows Forms app in sync with these changes. For example, if my program is stable with no DB issues, and I decide that I want to add a new field/column to a given table using Access, when I return to my program, it invariably produces errors related to the changes I make.

Today I made a change to an Access Query which is attached to a DataGridView in which I connected a new table to get a related field value. The Query works fine in Access, but now not only does the program fail to compile because of the DGV association, but the Data Source Configuration Wizard shows what before was a View is now a broken Table!

I'm pretty sure that Visual Studio stores a boatload of data pertaining to my database, and that needs to agree with the MDB file, so I just need to know that correct way to make minor changes to my tables and queries. Ultimately what I'm hoping to accomplish is to find a way to have ONE source for my database definitions, and just have a file (or files) store the data.

I realize this is not a lot to go on, but that's all I can offer for the moment.

Many Thanks,
Tony G.Example Error Condition
0
A bit of a weird one here;   I recently updated on computer to a solid state drive that is about 30 times faster for read/write than a traditional HDD.  This PC both holds an MS Access back end as well as runs the front end that connects to the tables in the back end.   We have many other computers on the network that connect to the back end.  All computers till run MS access front end with no issues.  but, the computer that actually contains the back end on its SDD gets constant read/write conflicts since the upgrade to the faster drive.   Anyone experience this?  Is it possible the hardware is now to fast for MS ACCESS to handle.  It seems the tables are being written before the form can understand that it asked for the save?
I hate to go back to the standard HDD just because the new drive is too good for an old timer like ACCESS to use.  I am on latest version of OFFICE/MS Access.    Any insight on reducing or eliminating these read/write conflicts is greatly appreciated!
0
I am using an Access 2016 split database with the backend table on a network folder. The front ends are on each computer. That said, when I try to open the tables in my front end, I am getting an error saying "recordsets are not updateable". Further, if I try to open the tables with another user using the tables from another computer, I get locked out.
0
Im trying to create a nested IFF function to turn this excel function in access. The basic excel formula is =IFERROR(SUM(COUNTIFS('Import Report'!E:E,{"CM","MMNRO","MMROI","PMCM","PMINS","PMOR","PMPDM","PMREG","PMRT"},'Import Report'!K:K,">="&(A2-1/24),'Import Report'!K:K,"<"&(C2+23/24),'Import Report'!F:F,"*COMP*",'Import Report'!O:O,">="&(A2-1/24),'Import Report'!O:O,"<"&(C2+23/24),'Import Report'!X:X,"LEWMXTCHE"))/SUM(COUNTIFS('Import Report'!E:E,{"CM","MMNRO","MMROI","PMCM","PMINS","PMOR","PMPDM","PMREG","PMRT"},'Import Report'!K:K,">="&(A2-1/24),'Import Report'!K:K,"<"&(C2+23/24),'Import Report'!X:X,"LEWMXTCHE")),0)
0
I'm trying to figure out how to use a command button onclick event to spell check an entire Access tabbed form.  Does anyone have a solution?

I have tried this code but it gives me an error that reads:

"Databasename can't move the focus to the control txtExpirationDate"  The field is set to TabStop = No but I tried TabStop = Yes also and got the same message.

Private Sub cmdSpellCheck_Click()

    Dim i As Integer
    'Turning off warnings to prevent completion message after each control
    ' is checked
    DoCmd.SetWarnings False
    'Loop through each control on the form
    For i = 0 To Me.Count - 1
    'Check to see if the control is a textbox
    If TypeOf Me(i) Is TextBox Then
    'Verify that focus is set to the control
    Me(i).SetFocus
    'Select starting point and select the contents of the control
    Me(i).SelStart = 0
    'Verify that the control contains data and select the contents
    If Len(Me(i)) > 0 Then
    Me(i).SelLength = Len(Me(i))
    'Run the Spell Checker on the contents of the control
    RunCommand acCmdSpelling
    End If
    End If
    Next i
    'Tell user that the check is complete for this record.
    MsgBox "Spell Check is Complete"
    'Turning warnings back on
    DoCmd.SetWarnings True

End Sub

Open in new window

0
I need to link to a SharePoint list from within Access and only retrieve the records for a particular view of the list.
It is not practical to pull down the entire list and then filter/SQL search within Access.
I want Access to only see the records in a particular view and only those columns in that view.

This is all working using Excel with a linked table and refresh all to update it from SharePoint I'd like to avoid the users
having to open a workbook at all  - just open access and the data is there (perhaps asking them if they want to refresh the data from SP)

I know to how directly link to a SharePoint list from Access - but that tries to bring down all records and all fields
(over 2000 records and 240 fields)  - not practical for end users.

I'm experienced in VBA, SharePoint, .. etc so a VBA solution is fine.
Environment:
Excel/Access 2016, SharePoint 2016, full control on the site w tons of workflows, JS, etc..
No infopath, No Access web apps, must work in FF and IE

so to summarize -
How to establish a link to a particular view of a SP list in access  - including refreshing etc
Specific examples, VBA code, ... sought
thanks
0
For a stored query (name is "CREAT_Plot") like the following in Access, what would be the vb.net or C# code to call this query and also pass a parameter in place of "[Reports]![TestReport]![Subject]"

SELECT CREAT.Subject, CREAT.CREAT, (IIf([CREAT.TargetDays]=".",".",Val([CREAT.TargetDays])*24)) AS TargetDays
FROM CREAT
WHERE (((CREAT.Subject)=([Reports]![TestReport]![Subject])))
ORDER BY CREAT.TargetDays;

I have googled about this but could not find an example to call stored query that prompts for input parameters.
Please kindly give me small code snippet to achieve this one.

Thank you
0
I have a table having fields as Country,Product,Years,Value. I want to subtract Values of two product having same Country and Years.

Below is a sample data:

ID      Country      Years      Product      Values
1      4                 2010      CO                50
2      4              2011      CO               200
3      4              2010      TO               50
4      4             2011      TO              122

Desire Result:
Country      Years      Product      Values
4             2010      CO-TO             0
4             2011       CO-TO         78
...
Thank you.
0
Big Business Goals? Which KPIs Will Help You
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

I have a form “frmObrasAlbum” where I have one combo, one textbox and a subform “frmInterpretesSub”.

I need to trigger a unique event in the subform when I leave the parent form (and entre in the child form), so the child form is automatically filled by a vba procedure (then I can edit the default values or accept those generated by the code).

My problem is, I cannot use an event of one of the controls of the child form because when I edit the default value of that control the vba code is triggered again (because it is linked to that control).

I tried to use “form” events, like on enter/set focus/got focus, but I does not fire the vba procedure.

Can someone give me an advice of which way I should go?
0
I have an Access 2016 ADP (Project) as a front-end to a SQL Server 2008 R2 Express database.
This arrangement has been running OK for the past 8 years.

What I am currently trying to do seems relatively simple, but I am having trouble achieving it.

In Access Form-A,  bound to Table-A, I have a combo box that fills a field with a value from Table-B.
If an entered value in the combo box is does not currently exist in Table-B, I use the "NotInList" event to add the new value to Table-B then immediately open Form-B (bound to Table-B) so that a couple of other pieces of data can be saved for the new record. This works OK up to a point.

Form-B is a continuous Access Form and I would like to immediately jump to the newly entered record in the list of all records so that the operator doesn't need to go scrolling down the list to find the new record.
How do I achieve this?

I have been trying to use "bookmarks" from recordset clones without success. Other on-line sites suggest using the "RecordsetClone.FindFirst" method, but my ADODB.Recordset clones don't seem to offer the "FindFirst" property.

Any suggestions gratefully received. Thanks.
0
I am trying to generate a chart on which I have to plot multiple line series. (I have no clue how to send data for multiple line series.)
As a first step, I tried creating a chart and plotting one line series on it.

Here is how the data is

Subject      CREAT      TargetDays
22-001      .      0
22-001      1.4      24
22-001      1.1      48
22-001      0.8      480
22-001      0.8      672
22-001      0.8      216

While creating a chart on report, I followed the following steps:
1. I have selected the query that is generating the above data
2. I have included all fields that will be used for this chart.
3. I have selected the chart type as 'Line Chart'.
4. I have dragged and dropped the fields that are to be shown on x-axis and y-aixs. In my case, x-axis values are 'TargetDays' and y-axis values are CREAT
5. Then I linked Subject of this chart to the Subject of the report on which this chart is placed.

I saved the report and tried to run it. But interestingly and confusingly this is what is generated (as shown in the attached image)



I have no clue why would it generated a chart like that which is completely out of data that I am passing into it. Any help in this regard is highly appreciated.
0
Hi Experts,

I'm getting the following error when trying to import an Access table to SQL Server (2008).

- Copying to [dbo].[Skilled_Nursing_Visit_Note] (Error)
Messages
Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Invalid date format".
 (SQL Server Import and Export Wizard)
 
Error 0xc020901c: Data Flow Task 1: There was an error with input column "Date_Of_Birth" (382) on input "Destination Input" (284). The column status returned was: "Conversion failed because the data value overflowed the specified type.".
 (SQL Server Import and Export Wizard)
 
Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "input "Destination Input" (284)" failed because error code 0xC020907A occurred, and the error row disposition on "input "Destination Input" (284)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.
 (SQL Server Import and Export Wizard)
 
Error 0xc0047022: Data Flow Task 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Destination - Skilled_Nursing_Visit_Note" (271) failed with error code 0xC0209029 while processing input "Destination Input" (284). The identified

Open in new window

0
Hi Experts,

I have a database that is working for years, but lately users are getting "unrecognized format" message on the back end file, this happens while doing certain operations in the front end, like adding some entries to the table...

See attached errors.

Users have Access 2007.
Server :       Windows 7 Pro – 64 Bit SP1
Uesrs PC:    Windows 10 Pro – 64 Bit
Untitled1.png
Untitled.png
0
I am trying to write vba code in MS Access 2016 to close a subform within a navigation form
0
Hi,

I'd like to create charts based on selected listbox items & entered dates from a form.

The first graph works fine, but the second one isn't loading the correct data.

I think the problem is that the second table uses "ID" instead of "WorkerID" and the main form's record source

is based on a specific table.

I've tried to create relationships between tables  tWorker,tReg and tAbsence, but it did not recognize WorkerID in tReg.

Is there a solution to this?

Thank you.
Charts_14-09-2018.accdb
0
I am doing an internship at an insurance Company and I have been tasked to create an employee/facility management database with Microsoft Access 2016.

Although I am still in an early phase of development (eliciting information/requirements), I have been asked whether it is possible to display a different ribbon based on the login provided.

I am using RibbonCreator and I understand that it is possible to have several ribbons in an Office document.
Is there a way with VBA to change the ribbon based on the login data provided (there are only four logins)?

It looks like that the data is stored in a table called USysRibbons with the following structure:
lngID 
RibbonName
Ribbon XML

Open in new window


I understand that it is possible to change the Ribbon inside Access, but it requires a restart of the application.
Would the loading of a different Ribbon with VBA require the same procedure?

Another idea which I have in my mind is that only one customised ribbon with several tabs is shown.
I would then hide or show these tabs based on the login provided.
Is this an easier approach?

Thanks in advance for your feedback which I can put inside the functional requirements.

Massimo
0
I have an ODBC connection to (Sage 100 ERP) databases with Microsoft Access.  We have three companies that each has their own separate database.  I have to log in each time I access a different database.  
Is there a way to log out of an ODBC connection?  Currently, I exit MS Access to log out of the ODBC connection and then open MS Access again to log into another Sage database .
Thank you for your help,
Jack
0
Newly released Acronis True Image 2019
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Is it possible to generate chart (as shown in the attached image) along with various information on the left side of chart and below the chart using MS Access?



The information for the plots on the chart as well as different labels is coming from different tables.

How to accomplish this?
So far I have written few queries to return data for multiple series.
Also I have created few labels on report and linked them to table fields.
I am not getting how to plot multi series line chart and also the data below chart.
0
I have several forms that I want to replace the small hard to see recordset counter at the bottom of the form with my own code.  What I have so far is:

Private Sub Form_Current()
   Dim rst As DAO.Recordset
     Dim lngCount As Long
     Set rst = Me.RecordsetClone
        With rst
            .MoveLast
            lngCount = .RecordCount
       End With
     Me.txtSongCount = "This is record " & Me.CurrentRecord & " of " & lngCount
End Sub

When the code gets to the line .MoveLast is results in an error Run-time error '3021', No current record.
0
Hi

I am looking for access vba script to activate an excel sheet by its name, not by its index number.

Please have a look
Thanks
0


is it possible to generate a chart in access report (as shown in the picture) in addition to the different information shown on left side of the chart and below the chart.
The data that is plotted on chart and also all the information labels are coming from different tables.
Please let me know how to accomplish this.

Thank you
0
I would like the form data to be aligned horizontally instead of vertically. Here is the snapshot of form that is currently displayed.

Current form alignmentI would like to change this so that it looks something like this:

Subject      56-020      56-020      56-020      56-020      56-020

TargetDays      .      0      1      2      6

MAP      80      67      84      73      85


Sorry, I was unable to format this as a table. But I hope what I am trying to ask is clear. I just want to change the alignment so that column headers are showed as row headers and their corresponding values are display horizontally instead of vertically. Please kindly let me know how to accomplish this in MS Access form. Thank you.
Form_Snapshot.PNG
0
MS Access append query to SQL Server table.  SQL Table is blank but does have identity column.

When I run the SET IDENTITY_INSERT [Table] ON;

I try to run the Append Query in MS Access, but get a Key Violation.  

If I wait X minutes then run the same Append Query again, it adds the records.

I am wanting to speed this process up as I have many tables to work on.

What is the best way to do this?

SQL Server 2012 R2

Thanks,
0

Microsoft Access

219K

Solutions

51K

Contributors

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.