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

Dear team,

Am very new to ms access database.

I have  developed an application in 2016 access and moved  .ACCDE file to testing environment but when testing team try to open they are facing the below error.

This database was created with 64-bit version of Microsoft access. Please open it with 64-bit version of Microsoft access.  

Please kindly let me what changes should need to do in order to successfully open the application in any version (from 2010 to 2016) of Microsoft with 32 or 64- bit.

I need this data entry form bit to work and I have been at it for days...
There is a person starting tomorrow and they are going to be sitting around doing nothing if I cannot get this working. [size=6]I REALLY REALLY NEED HELP...Please !!![/size]

 Link to Database:!AvfY5Xnb5svugapGBlUI8fZXkxRihg  ( Sorry it is 98 MB zipped so I had to just provide a link to it)

Here is what I cannot get to happen:

Problem 1

Unbound form named: f_MCRs_View has two controls:
A list box named lst_MCRs_Only, based on the query:  q_MCRs_Only
Column(0) is the full pathway to the file being displayed in the Web control
Column (1)  Last folder number created...the folder number
Column (2) FRAME,  which should be 01.pdf only
Column (3) t_files ID,  the actual file ID of the MCR (note: I screwed up the should be Files_ID, but have not had time to fix it throughout)

and a Web control named web_Current_MCR, which displays the .pdf which the data entry person will input into f_t_MCRS.

f_t_MCRS is bound to the table named t_MCRS.

Process Overview
Data Entry Operator navigates to a folder which contains many sequentially numbered subfolders. In each subfolder there is a .pdf named 01.pdf which we use to figure out which scanned item in that folder is the .pdf we need. ..
The data entry operator does this by selecting the form f_Import_Files, navigates to the folder of .pdfs to be data entered, and clicks on the button Click …
I am wondering if there is a preference way to make a table that contains fields not available in a query.

I have tried using a make table query using expr1, expr2,  etc As Field name ,which allows me to create a new fieldname, however, it prompts at each newly created field...

The database will be split and located on a local machine, however, it may eventually be placed on a server if we can afford one some day...

Is there a preferred way to create a table which includes new field names as well in the new table?

Thanks for your Expert Advice and guidance...

I have software that is used for manufacturing.  It has shop orders, each of which has a list of components needed to produce the finished good (think of it as a recipe's list of ingredients).  I have a macro that determines what these components are, one shop order at a time, then appends those components to a table named Shop_Order_Components.  I want to find a way for the macro to read the records in the table named Shop_Orders, append the component records to Shop_Order_Compents, then repeat on the next shop order record, looping through to the last order in the Shop_Orders table.

Thank you in advance for your assistance.  I greatly appreciate it.

Sorry people again , is it okay if I attach a form to the table USystem ( Custom ribbon creation)  to help capture data correctly rather than entering data directly in a table data sheets.



I am using the "Application.FollowHyperlink strFilePath, , True" line
of code from within access forms to launch any file type I want (e.g.,
xls, doc, pdf, etc.). The files open up okay, but open up in the
background. How do I make them open in front where they are visible?
I have tried various things like "Appliction.Visible=True / False"
and "Screen.ActiveControl.Visible=True / False" but have not been able
to figure it out.

Anyones help with this would be very much appreciated.

Thank you.
Private Sub cmdchemicalsearchexport_Click()
    Dim x1 As Excel.Application
    Set x1 = New Excel.Application
    x1worksheetpath = "D:\test\"
    x1worksheetpath = x1worksheetpath & "ReportChemicalSearch.xlsx"
    x1.Visible = True
    x1.workbooks.Open ("D:\ReportChemicalSearch.xlsx")
    Dim db As Database
    Set db = CurrentDb
    Dim rs As Recordset
   Set rs = db.OpenRecordset(Me.RecordSource)


        x1.sheets("sheet1").Cells(1, 1).Value = "Trade Name"
        x1.sheets("sheet1").Cells(2, 1).Value = "Supplier"
        x1.sheets("sheet1").Cells(3, 1).Value = "Category"
        x1.sheets("sheet1").Cells(4, 1).Value = "Physical Appearance"
        x1.sheets("sheet1").Cells(5, 1).Value = "Active Ingredient"
        x1.sheets("sheet1").Cells(6, 1).Value = "Regional Availability"
        x1.sheets("sheet1").Cells(7, 1).Value = "EPA#"
        x1.sheets("sheet1").Cells(8, 1).Value = "Comment"
        x1.sheets("sheet1").Range("A1:A8").Font.Bold = True

    Dim rownum As Long, colnum As Long
    Dim i As Long
    i = 0
    rownum = 1
    colnum = 2

    For Index = 1 To rs.Fields.Count
    If rs.Fields(i).Name <> "SDS" And rs.Fields(i).Name <> "CID" Then
        Do While Not rs.EOF
        x1.sheets("sheet1").Cells(rownum, colnum).Value = rs.Fields(i).Value
        colnum = colnum + 1
        rownum = rownum + 1
Hi I have a database that's been working fine in Access 2013 but since the upgrade to Access 2016 I am getting an error in the VBA code that exports to excel.

The error is:
Unexpected error occurred: Type mismatch - Number: 13

Below is a snippet of the VBA code, I trapped the error to the specific line noted:

Sub MonthlyReport(xlWs As Excel.Worksheet, xlRng As Excel.Range)

    Dim db As DAO.Database
    Dim iRow As Integer
    Set db = Access.CurrentDb()
    iRow = 1
    Set xlRng = xlWs.Cells(iRow, 1) '**THIS LINE CAUSES THE ERROR
    With xlRng
        .Value = "Supplier"
        .Font.Bold = True
        .Font.Color = vbBlack
        .Font.Size = 10
        .Font.Underline = True
    End With

Can anybody explain why this is happening.
I currently have a Module that does this perfectly:

Option Compare Database
Private Sub Command424_Click()
Command424.HyperlinkAddress = ""
End Sub

I'm not schooled with Modules/VBA code hardly at all, so if you can... school me up! :D

My Form name is "Grab_Data"
Field Name I'd like to copy is "WebSearchTXT"
And it'd be a bonus if it would first Filter By Selection on the field "ITEMNO", open the web browser and paste the copied info!

It would be for just the current record or one record at a time that has the focus that I'd be working on (if that helps anything?)

Thanks!  Kevin
MA Access - vba to report horizontal data to vertical data.

please do need full
I am getting the following error when I run an MS Access Query:

The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data

The query is not trying to add, insert or paste any data. It is simply a SELECT query with two tables as follows:

SELECT tableA.[Task ID], tableA.PID
FROM tableA LEFT JOIN tableB ON (tableA.[Task ID] = tableB.[Task ID]) AND (tableA.PID = tableB.PID);

As you can see there are two joins. The error disappears when I do ANY of the following:

1) Delete any one of the two joins and keep only the one other
2) Replaced LEFT JOIN with INNER JOIN and keep both joins

but that will not achieve what I am trying to achieve with the SELECT query.
Hi there:

I am trying to get the top 5 problemcodes_nonus and only the corresponding top 3 conclusioncodes_nonus for each complaintmonth.  Table and query in attached db.

Many thanks!

Hi Experts,

I have a report which has setup under sorting/grouping the ID field.
However I would like that sorting should be on something else, like follows.

I need the grouping should be by ID column as would like something to get printed on the ID's footer section.
In Addition, after each ID I want to start a new page.

However the sorting should not necessary be by ID column, as the user wants to select the order by column..

Hope that makes sense..
I'm attempting to convert a query from MS Access to SQL Server and I'm having a lot of trouble.  My command of T-SQL is moderate at best.  This is the query from Access:

TRANSFORM Sum(AR_Records3.AmtS) AS SumOfAmtS
SELECT AR_Records3.CoCd,
       Sum(AR_Records3.AmtS) AS [Total AR]
FROM AR_Records3 LEFT JOIN T_AgingBuckets ON AR_Records3.DPD = T_AgingBuckets.DaysPastDue
        INNER JOIN T_ComResFlag ON AR_Records3.SDst = T_ComResFlag.SDst
WHERE T_ComResFlag.ComRes='Com' Or T_ComResFlag.ComRes='Res'
GROUP BY AR_Records3.CoCd,
PIVOT IIf(Ar_Records3!DPD>365,'6-366 and Over',IIf(Ar_Records3!DPD<1,'0-Current',[AgeBuck]));

How can I write this for SQL Server?

I would like to create a pdf editable form with specific information about products for QA checking so these can be ticked off / completed on the PDF.

This could be from a report or vba code to populate a new pdf or a standard/template with the product info / fields.

Ideally I would like to have text fields, drop down selections, date field and a table with a tickbox in the last column.

Has anyone found a way to do this.

We are using Office 365 and have setup a SharePoint site.  For some reason, SharePoint stopped allowing me to edit the main page.  I can go into edit mode and I can edit the first header bar, but the three columns are not editable in the GUI.  I can click on the column and select edit source button at the top of the page and edit it that way, but I am not good with HTML and want to use the GUI interface.

I wouldn't be surprised if one of the owners of the page did not add a carrot to the end of their html code.
Error MessageHello,

I am having an error when I try to import data in a spreadsheet to an Access table using the following VBA code:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "TempTable", strInputFileName, True, "Output$"

The error message that I get is:
"Access was unable to append all the data to the table. The contents of fields in 0 record(s) were deleted and 0 record(s) were lost due to key violations"

Attached is a screenshot of the error message.

I am importing to a temporary Access table, and then running an append query to transfer the contents in the temp table to the final destination table in Access. The contents of the temp table are cleared after this second step. The error message is related to the first step (Excel to temp table) only.

If I choose to proceed anyway on the error message, the data transfers without any issues. Interestingly, I only get this error sometimes.

I looked at data types and there are no issues. I am not using any autonumber fields in the destination table

Any help will be greatly appreciated.

Does anyone have an Access DB that will download stock quotes? I have been using one for many years but it stopped working recently. When I do the download, I receive "Error #9: Subscript out of range". I receive the identical error using back-up copies of the DB on other computers.
  If someone has the basic download part, I can adapt it for my purposes. Or, if someone can suggest a solution to the problem I have encountered, that might work, also.
I have many folders I can create via a "on click" cmd that creates folders of "peoples names". Inside each folder are 20 images with the same name. (but different image) EG each folder has files called
1 (1).jpg
1 (2).jpg
1 (3).jpg
 Is there away of displaying the twenty images based on the user ID or name I select? Images are all located in a s:\data drive.
       s:\data\sally ....... etc
I have at present
Private Sub txtPictureLocation8_Click()
  Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
fDialog.Title = "Select a folder"
fDialog.InitialFileName = "C:\"
If fDialog.Show = -1 Then
  Debug.Print fDialog.SelectedItems(1)
End If
End Sub

it errors on Set fDialog . can anyone see my mistake?
Hello All

I have to export data from an Access table to an Excel spreadsheet.  I did a simple DoCmd.TransferSpreadsheet acExport but now the user wants some formatting.

When I do the exporting now it exports just fine, but the columns are not wide enough in some locations.  Personally I would think that he could simply doubleclick to widen them but he doesn't want the bother.

I remember I did this once before a slightly different way where I set up an excel spreadsheet like a template and just filled in a range (Like A1:A15).  The problem with this is that the amount of rows in this spreadsheet could fluctuate between 1 and around 50.

Additionally He would like a total produced and some text to show up under the individual lines.

Ideally by pressing a button in the Access database it would create the xls file completely formatted.

Is any of this possible?
i had a slow running access application which had a backend  of sql. so my manager suggested i copy all the records to a local table in access and then work with the table.

Private Sub Form_Load()
Call Copy
End Sub

Sub Copy()
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM WorkTable"
DoCmd.RunSQL "SELECT [dbo_Load Table].* INTO LoadTable FROM [dbo_Load Table];"
DoCmd.SetWarnings True
End Sub

Open in new window

getting the error on the simple copy, i dont have the local loadtable opened. so is  there a way i can just unlock the  table before starting the load?
Experts,  with the rise of ransomeware, we have all become more concerned about the accessibility of data to individual user accounts.  I have been discussing this issue with my programmers and have a question relevant to our Access code as well as other code we have developed in Visual Studio...

It seems to me that a reasonable protection of file data would be to use a separate, explicit credential to files and directories that contain application data, rather than using the credential authentication of the application user.  This method would reduce the "attack surface" of shared data in the event that an individual user's workstation became infected.

Does anyone have some insight how to do this effectively without creating a connection to the data share that was discoverable by a rogue app grammatically?  For instance, you can use the command line "net use" command to create a connection to a share using a credential other than your own, but this connection becomes easily visible to any application.
Haven't been here in a while.

I am trying to setup the highlight selected record in a continuous form.  Each record has a [delete] button and a [Appt_ID] box.

There is a txtActiveRecord box in the header.  The Delete_OnClick event sets "Me.txtActiveRecord = Me.txtAppt_ID" as the first item in the procedure.  CF then kicks in on "expression is: [txtAppt_ID]=[txtActiveRecord]"  and sets one of the controls to a particular colour. The rest of the On_Click event asks the user "are you sure" etc, and if clicked "No", it all returns to normal.

This works perfectly, EXCEPT the CF doesn't kick in until after the procedure is completed.  i.e. too late.  

However, when I step through the event line by line, the CF kicks in right on cue.  i.e. a the "Me.txtActiveRecord = Me.txtAppt_ID" line.

Where have I gone wrong?

Good morning.

We have a database and it is run in Access 2000.

On a report with a GIF that has a transparent background as part of the time. The image control for this gif backcontrol is set to Transparent.

One some machines (setup in the same way) the gif is transparent and others it isn't. I have tried changing it to normal as well just in case it was counter acting the transparency.

Any ideas?

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.