Microsoft Access

221K

Solutions

52K

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

Why do I sometimes get empty records in a table when I import from an Excel file?  Here is an example of my VBA code for the import:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblTEMPBaselineSurveyDataImport", Me.FileList, True
0
Become a Microsoft Certified Solutions Expert
LVL 20
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Hi Experts,
I have a question about Combo box in access 2016.  The combo box was working fine in my program before I convert to Office 365 Access 2016.  Now the combo box in Access 2016 has a problem that I couldn't figure it out why it happen and don't know how to fix.  The problem is:
I have a combo box that has a list of items from a query, the length of the items some are short and some are longer.   when users select from the combo box, the shorter length items show no problems, the users can see the item in the combo box right after they selected.  But there is a problem with the longer text item, it cuts off the first half part of the item right after the users select and only shows the other half part of the item in the combo box until users click on the any field in the form then the whole item shows in the comboBox.  I don't know why it happen in access 2016.  Any help how to fix would be very appreciated
0
I am using ms access as a front end for a mysql database.   I need to back up the database from where the ms access engine is located...i mean from a pc that is not the server.   How?
0
The communication code VBA for RS 232 has failed, it cannot transmit any data to the Rs 232 gadget kindly help me to locate where the problem is. There are no errors whatsoever during transmit ion but the data does not go or written to the com port 4. The json part is okay and confirmed by the users I’m also able to see the results by using text file instead of on an Immediate window. The requirement here is to write data, send and receive data from rs 232 gadget.

VBA CODE RS 232

Dim json As String
    Dim intPortID As Integer ' Ex. 1, 2, 3, 4 for COM1 - COM4
    Dim lngStatus As Long
    Dim strError  As String
    Dim strData   As String
    Dim lngSize As Long
    intPortID = 4
    ' Initialize Communications
    lngStatus = CommOpen(intPortID, "COM" & CStr(intPortID), _
        "baud=115200 parity=N data=8 stop=1")
    
    If lngStatus <> 0 Then
    ' Handle error.
        lngStatus = CommGetError(strError)
    MsgBox "COM Error: " & strError
    End If
    

    ' Set modem control lines.
    lngStatus = CommSetLine(intPortID, LINE_RTS, True)
    lngStatus = CommSetLine(intPortID, LINE_DTR, True)

    ' Write data to serial port.
    strData = JsonConverter.ConvertToJson(transaction, Whitespace:=3)
    lngSize = Len(strData)
    lngStatus = CommWrite(intPortID, strData)
    If lngStatus <> lngSize Then
    ' Handle error.
    End If
Exit_CmdConertJson_Click:
Exit Sub
Err_Handler:
Resume Exit_CmdConertJson_Click

' Read maximum of 64 bytes from serial 

Open in new window

0
Help,

Our client's IT folks moved our shared back end data file to a virtual server and we now have performance issues.  We have 3 users with usual front-end back-end split out.  We cannot move the back end to SQL as the client refuses it.

Performance was Ok until the move. IT says it's the application but it works just fine in a non-virtual set up.

How can I explain that even though their virtual server is now more powerful.  

I don't like finger pointing but...
 
Thanks, Bill
0
Users and privileges :

Hi,
I have a database with this panel:
Customers
Invoices
Products
Users

I need to give privileges to users.  For example:  some users can only access Customers and Products, but not Invoices.  Some users can only see Products.  Some users can access all.

How can I create a table with the users, store the privileges (yes/no) by user and also use this information in the Panel to be able to activate or not the different options according to the privileges?
0
Migrating from a faulty WIN 2012 Server that has numerous group policy errors reporting, the domain controller reports offline majority of time which has caused havoc. Due to this we're buying a new server which has already arrived and we're testing apps now.

For the record I do not know much history regarding the network the old IT left and a friend's been running the network on the side without paying much attention to the server. They also don't know the user/group information in Access.

My question is this. I copied the Access database over to the new server and mapped a drive to it on the computers still in the old domain. I haven't done any server portion of the migration yet. My plan is to get access working without errors.

When a user accesses Access alone it works fine, when another user accesses I get the following error
access read only
During testing user1 and user2 were able to open the database but user1 was not able to make any changes. User1 gets a pop up that states it is in read only mode. Then there's the option to make another copy and save it as a different name. Haven't tried this yet because it doesn't seem like it will correct the problem. User2 can make changes, view the updates, and open and close without any errors. User1 gets the error messages mentioned above.

Testing between 2 users now there are a total of 7 users that will eventually need access.
0
How can I find records with same latitude and longitude values. please see attached image.

Thank you,
Capture.JPG
0
I have several macro's I run at night doing some number crunching for reports via scheduled task.   For years, they have run flawlessly on my Win 2008 R2 (64 bit) server - physical.  Access 2007.  They run in 17 minutes.

Time to upgrade a little.  I have moved the databases over to a fresh 2012 R2 server (virtual, 64 bit).  The run time for these macro's is now 45 minutes with Access 2007.  Decided I needed to upgrade my Access to 2016, hoping that would help ... no change.

Both servers are nearly identical (processing speed, ram, pagefile), except one is physical and the new one is virtual.  My IT guy tells me that shouldn't make a difference.  The databases get compact/repaired every day.

I'm at a loss as to why they run so slow on the new 2012 server ... has anyone seen this before and/or have an idea what I can do to get the run time down?

Thanks,
Max
0
I am trying to import data from an MS Access query into Excel. I swear that query was visible from Excel.

I recently made some changes to the query and it runs fine within MS Access. But that query is no longer visible from Excel (it is not in the list of queries I can import).

What might have happened?
0
JavaScript Best Practices
LVL 20
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

I am opening my Microsoft Access database and I am getting below message. What can I do to open my database?

The Microsoft Access database engine could not find the object "MSysDb"
0
Is there a way to add a UNIQUE constraint to a table in MS ACCESS . The table is called Bills_Transactions, it is a detail to the master table Bills_Headers.
Each Bill header can have several transactions records, However ONLY ONE TRANSACTION can be status ACTIVE per Bill_Header_ID.
For example , if one Header_ID has 3 records in the Bills_Trx table, only one record of those 3 can be Status=Active

How can this be achieved in MS ACCESS ?
0
I wanted to create a time entry with ms access ,with adding new projects , editing new projects,allocating employee to a project ,and the employee to enter time entry for that project. Can anyone help me to build this project on an urgent basis please
0
We need to connect a Access db to the Power BI via the gateway (so we can have an automatic refresh).
I believe we need to use the ODBC connection to be able to have automatic refresh.
I have not have much success implementing it.

Has anybody else successfully connected via the ODBC?

We have successfully connected a SQL database to the Power BI using the on-premises Data Gateway, refreshes automatically 8 times a day.

Our setup:
Azure cloud server (Ms server 2019)
Terminal services
SQL database
Ms Access (2019) database, split FE and BE both in Access (no plans for SQL backend for Access)
on-premises Data Gateway
0
Hi Experts,

I have a client MS Access 2010 app that loads reports in Preview. Once loaded they used to have a button that allowed them to Export to Excel. Like this...

With the Export to Excel Option
But, for security reasons, I'm using a customized USysRibbons table to hide the Privacy Options from the end users. Here's the XML code:
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
      <ribbon startFromScratch="false">
      </ribbon>
      <backstage>
      <button idMso="ApplicationOptionsDialog" visible="false"/></backstage>
      </customUI>

Open in new window



And now they no longer have the option to Export to Excel from their Print Preview menu. Take a look...

Without Export to Excel option
I'd like them to get that option back. Can any one direct me on how to do this? Is there a modification to the XML Code that would do that?

Thanks,
Eddie
0
I have multiple PST files (not currently being used in any OUTLOOK installation).   I would like to create a list in EXCEL or ACCESS, and search for matches for anything on that list within those PST files.   Does anything like this exist.... or could something be built using VBA in EXCEL or ACCESS?

Thanks
0
I do a lot of development on MS ACCESS, but the interface for writing text SQL queries is very simple. I need to use a 3rd party tool for editing SQL text queries.  I tried using Oracle SQL Developer, as many sites on internet confirms it supports MS ACCESS, however that didn't work with me despite several attempts. Please recommend another tool ?
0
Hello Experts,

This is the first time I've attempted to do a HTTP post using Multipart/Form-Data Encoding in VBA.  I'm having some trouble figuring out where everything should go in formatting the request.  I'm going to post the instructions and what I have so far.  The file being posted is base64 encoded, which isn't an issue.  I just don't know how to literally string all this together into a properly formatted request.  As you'll see, I didn't attempt adding most of it.

The Instructions:

1. Post a multipart/form-data encoded message (see below for details) to the following URL: https://www.anysite.com/anyFeed/anyFileUpload.asp

2. The header of the http message must contain the login credentials. This is done with an http header formatted as follows. The username and password portion of this line, including the colon in between the username and password, must be base64 encoded.

Authorization: Basic username:password

The username and password portion of this line, including the colon in between the username and password, must be base64 encoded. For example:

Authorization: Basic QWxhZGRpbjpvcGVuIHNlc2FtZQ==

3. The body of the http message must contain an embedded file in the required multipart encoded format (see below for details).

4. For automatic submission, post the embedded file over HTTPS using multipart/form -data (MIME) encoding.  Below is an example of the HTTPS message to be submitted.

5. Each line of the message is terminated …
0
I am running this line of code in Excel VBA

Set db = OpenDatabase(stDataPath)

I have checked the value in stDataPath ("H:\Order book\SOOsieorderbook.mdb") and it is correct.

For one of my users the line of code crashes with the error:
"Cannot start your application.  The workgroup information file is missing or opened exclusively by another user."

I don't have a lot of experience with workgroup information files, but I know what they are.  There isn't one for this database.  If the user tries to open the database directly, it opens fine.

The only thing I've noticed is that the users machine is referencing Access 15 in the VBA, when she is using Office 16, and the other users in the company have the Access 16 reference.  

Any thoughts about why this user could be getting this error?  What do I need to check.
0
Learn SQL Server Core 2016
LVL 20
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Hello Team

I have been running a reports automation from MS Access. Nothing to complicated, just using access to open up particular files in Excel, delete specific detail and then transferring query detail back to Excel.

This process has been working fine for quite sometime but recently there has been a system update within the Citrix operating system and since the update my automation has been crashing when it gets to the DoCmd. TransferSpreadsheet method.

What I have discovered is, if I step through the code one line at at time or even apply a break before the  DoCmd. TransferSpreadsheet methods are reached, the code will continue to be processed without any issues.

From this observation, I am beginning to think that it may be a delay occurring within the Citrix environment as the only difference as far as I am aware of is the speed at which the code is processed in comparison to the step code execution and the event trigger code execution.

If the issue is due to the speed at which the code is being processed or attempting to be processed from the trigger event, can anyone suggest what would be the best way to create a delay in the processing of the code before it reaches the TransferSpreadsheet method as it's as though extra time is needed before the TransferSpreadsheet code is executed.

Thanks in advance.

Dale
0
I have tried all the solutions I can find here and any others online I could find, there always seems to be some factor which fails.
0
We are moving to Access 2019 64 bit from Access 2016 32 bit.  I am having problems updating this code to make it 64 bit compatible.  Thanks for your help.  

Option Compare Database
Option Explicit
Private Declare Function GetComputerNameA Lib "kernel32" (ByVal lpBuffer As String, nSize As Long) As Long
Private Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Public Function GetComputerName() As String
'On Error GoTo Err_GetComputerName
Dim Username As String * 255
Call GetComputerNameA(Username, 255)
GetComputerName = Left$(Username, InStr(Username, Chr$(0)) - 1)

Exit_GetComputerName:
    Exit Function

Err_GetComputerName:
        MsgBox Err.Description
        Resume Exit_GetComputerName

End Function

Public Function GetCurrentUserName() As String
'On Error GoTo Err_GetCurrentUserName
 Dim lpBuff As String * 25
 Dim ret As Long, Username As String
   ret = GetUserName(lpBuff, 25)
   Username = Left(lpBuff, InStr(lpBuff, Chr(0)) - 1)
   GetCurrentUserName = Username & ""

Exit_GetCurrentUserName:
    Exit Function

Err_GetCurrentUserName:
        MsgBox Err.Description
        Resume Exit_GetCurrentUserName
End Function
0
update-location-with-combo-box-valu.pptx
update-location-with-combo-box-valu.pptxneed help updating new location from combo box into a subform query in access using vba.  please see attachment.
0
Hi

I have a form (tabbed form), of which Tab1 have a combobox called cboDrawingNum and a list box called lboEQARevEquipList. I want to achieve the following:

FIRST PART:
The form is used to assign a Drawing No to a Equipment in the list box. Previously there was no constraint of each Equipment to have a Drawing No. But now there is a requirement for each Equipment in the List box to have a Drawing No assigned ONLY when list box is not empty.

So now when I have a Equipment or more than one Equipments in the List Box and if I do not select a Drawing No from the combo box I get a Message "You MUST assign a Drawing No" and "access won't allow me to leave the Tab1 until I select a Drawing No. This same functionality has to me developed for Tab3 as well.

I was thinking of using a validation rule similar to one available for Text Box form control but I did not find it for combo box.

SECOND PART:

If user empties the list box and leaves the Tab1; the stored value of Drawing No is deleted from the table for that Equipment which was stored upon selection.


The control names are listed below:

"cboDrawingNum" is the name of Combo Box
"lboEQARevEquipList" is the name of List Box

Guidance and advice is appreciated.
0
We have been using the following lines of code in many places in our Access application and starting today its giving an error.
         Dim FSO As Object
         Set FSO = CreateObject("Scripting.FileSystemObject")

The error is 429 ActiveX cannot create object.
This is on Server 2008 and we are not experiencing this issue on another Server 2008 running this program and both show having the same identical Windows updates.
I have tried removing the last updates for the computer, but this did not resolve the issue. Regedit Scripting.FileSystemObject  permissions appears to be the same on both servers.
What else can I try to resolve this issue?
0

Microsoft Access

221K

Solutions

52K

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.