Microsoft Access




Articles & Videos



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 an Access database that I am writing to.  The table I am trying to write to has two fields, namely, an AutoNumber field and a Number field.  I cannot write to the AutoNumber field.  Another program that writes to this same table is able to write to the AutoNumber field.  How is this possible?  When I browse the table in Access and try and change any value in the AutoNumber field the error message is: "Control can't be edited; its bound to outnumber field X".  Data written from another program into this field allows for duplicates which it should.  I find this amazingly weird - that the the field is an AutoNumber field and can be written to by a program assigning duplicates into the field which is exactly what is required.

Any ideas?
Back Up Your Microsoft Windows Server®
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

I have my cpanel account (hostgator) my user and password

I have a domian

I hava a database robrodp_respuestas
it has a privileged user  robrodp_roberto
user has a password *****
$servername = "localhost";
$username = "robrodp_roberto";
$password = "*********";
$dbname = "robrodp_respuestas";

I use the following code:

$servername = "localhost";
$username = "robrodp_roberto";
$password = "*********";
$dbname = "robrodp_respuestas";

// Create connection
$conn = mysql($servername, $username, $password);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
echo "Connected successfully";

Open in new window

When I run it I get this error:

Warning: mysql() expects parameter 3 to be resource, string given in /home2/robrodp/public_html/ on line 8
Connected successfully

What am I doing worng?
Hello Experts.

I've made some good progress with learning how to interact with an Access database through VB.NET. The attached project includes some useful techniques for populating ComboBoxes with multiple columns using LINQ queries, and leverages Access' ability to generate Queries (which VS calls 'Views') for the purpose of connecting two related tables for populating the ComboBox list items, and any data bound form objects and DataGridViews.

This project uses two tables; one for a list of Players (which I call 'Roster') and one for a list of Teams. The Roster table has a Teams Key that joins to the Teams table which includes the Team Name attribute. I also decided that I wanted to include a picture of every player so Team Captains would have an easier time identifying who's who ("which of you guys is John Doe?"). So it was easy enough to add a new column to the Roster table in Access of type OLE/Object. I then added the code so the image file could be retrieved and attached to the bound PictureBox. That all works good, but since the PictureBox is only a tiny 120x90, I want to be sure the image file is sized correctly so it doesn't bloat the Access table too much.

To that end, I found a nice solution but it was in C#, so I'm not sure if I was able to convert it properly to Visual Basic. But the real head scratcher for me is understanding the concept of an IO.Stream which is how this image resizing solution is rigged. That leads to my real question -- how do I
I have an Access 10 db with many tables. One of which contains over 20000 records with a long int PK called OrderID. The user needs to find up to 10 of those records and view them via an continuous Access Form. These records are not close to each other so scrolling is a no no. I thought the "IN" function within an Access query would do the trick.  The question is how do I pass a variable list of OrderID's in to a IN function within a query, eg IN(20223,19001,12544)?

Hello there I have looked through the interweb and cannot seem to find anything except TRIM to remove spaces.

Unfortunately Trim remove pre and post spaces, which I do not require

I was to remove the spaces within a field

eg. Te Awe Awe Gardner --> TeAweAweGardner

Any help would be great
Hello Experts,

Just need confirmation. If I create a database application in MS Access 2010  can I open later in MS Access 2013 with all functionalities? Our organization is moving over to Office 2013 in a few months and I just want to make certain that all the applications that I've created in MS Access 2010 will still work in Access 2013.

Can anyone explain how I change my minus results from a minus to a plus in a Query?
I have a file with over 5000 records I need added to a table of mine in access. Well one of my fields is call SKU. The SKU field is required and is Unique. I use a Function called UniqueString for when I insert new records one at a time. But I need to use this function to generate 5000+ Strings at a single time so I can copy and paste the row into my excel file before importing into Access?

How can this be accomplished? Thanks for the help and here is UniqueString:
Option Compare Database
Option Explicit

Function UniqueString(ByVal parmLen) As String
    Const cAlphabet = "ABCDEFGHJKMNPQRSTUVWXYZ0123456789"
    Const AlphabetLen = 33
    Dim lngLoop As Long
    Dim lngOffset As Long
    Dim lngPosn As Long
    Dim GUID As String
    Dim GUID_Trailer As String
        GUID = GUID & Replace(Mid(CreateObject("scriptlet.typelib").GUID, 2, 36), "-", vbNullString)
    Loop Until Len(GUID) >= parmLen
    UniqueString = String(parmLen, "*")     'initialize output string
    Select Case Len(GUID)
        Case parmLen
            'convert entire GUID to string
            lngPosn = 1
            For lngLoop = 1 To Len(GUID) Step 2
                lngOffset = "&h" & Mid(GUID, lngLoop, 2)
                Mid(UniqueString, lngPosn, 1) = Mid(cAlphabet, (lngOffset Mod AlphabetLen) + 1, 1)
                lngPosn = lngPosn + 1
        Case Is > parmLen
            'use remaining byte values as increment, mod 256

Open in new window

I have a main form.  On the form is a combobox that the user select an accountID from.  The rowsource of the combo box is :

SELECT tblAccounts.AccountID, tblAccounts.AccountName
FROM tblAccounts
ORDER BY tblAccounts.AccountName;

The on the form I have a tabbed control that contains another form.  That for has a query that limits the records being displayed with this in the criteria:

AccountID = [Forms]![frmMainForm]![cboAccountID]

But the form on the tabbed control shows no records.  And when I change the selection in the main forms combobox, it still shows nothing.

I have this in the after update event of the combobox:


What am I doing wring?
I have a report that shows part numbers and what we have on order for these certain part numbers. Each line is a different order. So in my query there may be a part number with 3 or 4 lines and each line has a different on order balance. Basically I am trying to figure out how to add the multiple on order quantities and display them so each part number will have one line which will be total on order quantity. I attached a couple documents you can look at might help you understand more. Any help with this would be much appreciated!
Free Tool: Site Down Detector
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

I know it sounds stupid, but i try to get an average from the query and instead of 11.35 it gives me 11.3507588532884
I was using val function, i was using "Standard". "Fixed", "#,###.00" and so on...
My query should return 11.35 or even better 11.4

need your advice, please
I am having a problem with some VBA code when it runs within a DAO Transaction. The code works fine when it is NOT running within a DAO transaction. Here are the specifics of the problem:
I modify a QueryDef.SQL property, and then I use that QueryDef in a DoCmd.TransferSpreadsheet acExport command. When that command is run I get the run-time error "3066: Query must have at least one destination field." In debug mode I can see that the SQL for the QueryDef is set to the value that I set it to in the previous step. It's a valid SELECT statement, and again, it normally works just fine when I remove the DAO transaction surrounding the code. Interestingly, when I open the query object in Access it is blank. Another interesting fact is that while the debugger is waiting at the DoCmd.TransferSpreadsheet command I can use the Immediate window to commit the transaction, and then when I resume the execution of the code it will work just fine.

So, it appears that the SQL in a QueryDef cannot be modified during a DAO transaction, but does anyone know why?
Hi, i have been trying to filter a full report, by a parameter which i input by text.
As i open the report, a text box appears, to enter a first or last name, so that the report filter by the entered name and opens. So far i can just do this by hand selecting the "name" , text filter, "contains" and then filtering by what i input. is there a way, maybe to,  access.PNG input by hand here? ive tried many, of vbs scripts and stuff, nothing works. thanks in advance
I'm using Access 2013. I have a text field and I want to check for entry of certain characters only and give an error if an incorrect character is entered. I want to limit the entries to "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789-" . I want to check when the save (or update) button is clicked. I do not want to check each character individually when entering. I will use the UCASE to shift the letters to upper case.

Any help will be appreciated.
I have a PosgreSQL database where I have an array field with 4 values.  This query will extract one value at the time
select public.addresses.address[1] from public.addresses

Open in new window

I'm linking the table to an Access database and the array I have above does not work there. How can I do this in Access?

Here is are my results from PostgreSQL and this is what I want to do in Access
Last I've read, there is no formal security in MS Access, but if you want to expose objects to only certain users, is it acceptable to use something like the code below to determine what objects users see...purely from a workflow perspective?
Dim CurrentWorkbenchUser As String
CurrentWorkbenchUser = Environ("USERDOMAIN") & "\" & Environ("USERNAME")
If CurrentWorkbenchUser = (name users) Then (make object visible)

Open in new window

Using ACCESS 2010:  On a continuous form I am attempting to set TransactionAmt to disabled, background Black with white lettering if the [ParcelServiceMilestoneActiveInd]=0.

On the control for the TransactionAmt , I selected the option Conditional Formatting.
I have set the background color for disabled to Black with white lettering.  The preview looks correct, when I select Okay the screen indicates light gray.  Is there anyway to set it to black?

Strange behavior, please look at the attached document.
Hi All,,,

I managed to create a Multivalued Combo Box on a field in the Data Sheet view of a table by following the wizard and some literature.

It is working well in the table Data Sheet view, as per the attached image.
I dealt with many Combo Boxes but this is my first use of a Multi-Valued Look Up field.

Now I want deploy the same in a form. How can I do This.

Thanks in advance.
Guys, This must be very simple but!!!!!!! My piece of code loops through a query I have and what it should do is work out how many records then loop through every record. What it is doing is looping through fine but missing out the last record.

Private Sub cmdEmail_Click()

On Error Resume Next
    Dim rs As DAO.Recordset
    Dim strpath As String
    Dim stDocName As String
    Dim mypath As String
    Dim sExistingReportName As String
    Dim sAttachmentName As String
    strpath = Forms![Main_Menu_frm]![temppdfloc]
    Debug.Print mypath
    'recordno = Me.Recordset.RecordCount + 1

DoCmd.GoToRecord Record:=acFirst
While Me.CurrentRecord < Me.Recordset.RecordCount
    sExistingReportName = "Bacs_report_All"    'Name of the Access report Object to send
    sAttachmentName = Me![PAYEE_NAME] & " Bacs Remittance"    'Name to be used for the attachment in the e-mail
    mypath = strpath & sAttachmentName & ".pdf" '"Bacs.pdf"

DoCmd.OpenReport sExistingReportName, acViewPreview, , , acHidden
Reports(sExistingReportName).Caption = sAttachmentName
DoCmd.OutputTo acReport, sExistingReportName, acFormatPDF, mypath, False

    Dim objMessage As Object
Set objMessage = CreateObject("CDO.Message")
 objMessage.AddAttachment mypath
 objMessage.Subject = "Bacs Remittance for " & Me![PAYEE_NAME]
 objMessage.From = ""
 objMessage.To = Me![SU_EMAIL]
 objMessage.TextBody = Me![PAYEE_NAME] & ":" & _
      vbNewLine & …
Independent Software Vendors: We Want Your Opinion
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

I created a link to a table in SQL Server 2008 R2 from Access 2010.  One field does not appear but is visible in SQL Server Management Studio.  The field appears to be an integer type (int, not null) and the values in it are that of a four digit year such as 2017.  If I create a query in SQL Server Management Studio the field appears.  I tried refreshing and recreating the link but no improvement.  Any suggestions on what I should try next?
I'm doing some work for a client that receives a '.txt' file from one of its suppliers.  The file should be broken down into 137 byte records but the input file has no carriage returns. So, when I try to import the file I get message that the field being import is too long. "One or more rows in your data files is too many characters.  The maximum characters per row is 65000.

The supplier says they can't reformat the file its generated from some automated process in their system.

Is there a way from within Access or VBA that I can read this file in chuncks of 137 bytes?  That way I can process the records as they should be.  I tried setting up an import specs of 137 bytes but it blows on the same 'maximun characters per row is 65000 message.

If i have 30 controls on a form, say 25 text boxes and 5 combo boxes, and i want to disable all of them, I would set the form property AllowEdits = False when the form loads.

Now, if i have a command button for users to be able to edit the record, I can have the OnClick event set the form property AllowEdits = True.

But, if there are six fields that must always be Disabled, should i explicitly set the enabled property of each field, or loop through all the controls by control type Except for the six controls to enable them? How do i refer to six controls in an if statement...if not one of these six, then set Enabled = True?
I'm still learning Access, but I have a database where I need records from 3 tables to append into 1 table.  I can do that for 2 of the tables, but I continue to fail with the 3rd table.  This table may have many records, but I need the field values from only the most recent record to be appended into 1 table that will have several records with matching fields with no value.  For example,
Table 1 will have StudyID(PK), Site, StudyDate, ProjectManager, Company, Fee1, Fee2, Notes, DateModified,  I need those values to be appended into another table for all the records that currently have no values in the matching fields.  This new table will have its own PK.  Table 1 data is input via a Form where the user has comboxes for choosing or entering values for Site, StudyDate, Company, Project Manager and the remainder fields are text boxes.  It's confusing, so I'll try to write out an example here....
Table 1: Several records for fields A, B, C, D
Table 2: Several records for fields A, B, C, D, E, F
Table 3: Several records for fields G, H, I, J
Final Table being appended into contains all records for Table 1 & 2 for fields A-F.  Also includes Fields G-J which are null across all records.  The newest record in Table 3 is appended into the matching fields everywhere it is null in the current recordset of the Final Table.
I hope that makes some sense.  I may be going about this in some un-normalized fashion, so please let me know if that's the case and there's a much better way …
After this code runs and adds a record to the recordset, I am unable to add any more entries into the record. I believe the record is getting locked by the database since it detected changes, but I'm not sure how to remove that through access. I have verified this code works properly besides the unintended side effect of being unable to repeatedly do it. Does anyone know how to unlock the record so everyone can access it after this runs?

    Set ClientRS = CurrentDb.OpenRecordset("Client Contacts", dbOpenDynaset, dbSeeChanges)
    With ClientRS
        ![id] = ClientID
        ![Full Name] = FullName
        ![Phone Number] = PhoneNumber.Value
        ![Company] = "Other"
    End With

    Set ClientRS = Nothing
    DoCmd.Close acForm, "Add Phone Record", acSavePrompt
End Sub
The user provides my code with a SQL query that would look like this  for example:

select DISTINCT *from employees a INNER JOIN orders b on b.[Employee ID] = a.ID

I want to find a way to populate the datatable I create in code with the query columns and information but not actually get data. Is there a way to do this? The code also accepts queries from MySQL, MS Access, CSV as well as SQL.

Microsoft Access




Articles & Videos



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.