Microsoft Access

218K

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

I am writing a conversion that is totally data orientated.  Reading legacy records in an old format then reformatting and writing them many time.  Some files with several million records.

I was having some issues and got some very good advice on EE, which was to use this statement to increase the number of locks permitted.
DBEngine.SetOption dbMaxLocksPerFile, 50000

Open in new window


I used the statement in the procedure I was having issues with and it solved the issue.  Now I am wondering what the scope of the statement is.  Meaning, do I have to put it in every module doing the heavy processing or is it a 'set once and it becomes the default throughout your application, until you exit the application.

As a precaution I was thinking of putting the statement at the beginning of every one of my heavy processing routines.  However I am guessing there is overhead related to this command so I don't want to execute it unless necessary.
0
Hire Technology Freelancers with Gigs
LVL 11
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Calculate on form 2 textboxes the hours of the day difference:  8:00 am   10:07 am  

Need to know difference in hours minutes

How would I calculate this?
0
Access 2010
Excel 2010

I'm trying to get to an excel workbook(without actually opening it),
and Delete the rows below the column Headers(row 1)..but this is not working below

Screen.MousePointer = vbHourglass
          Dim strDame As String
          Dim DBs As DAO.Database
          Dim rst As DAO.Recordset
          Dim STRSQL As String
          Dim xlApp As Object
          Dim xlWb As Object
          Dim xlWs As excel.Worksheet
          Dim recArray As Variant
          Dim strDB As String
          Dim fldCount As Integer
          Dim recCount As Long
          Dim iCol As Integer
          Dim iRow As Integer
          Dim accExcel As excel.Application
  RecNo = 0

STRSQL = "SELECT * FROM Import_Data"
   
 
   Set DBs = CurrentDb
   Set rst = DBs.OpenRecordset(STRSQL)
   Set accExcel = CreateObject("excel.application.9") <---- error here 
   
   accExcel.Workbooks.Open "H:\Escalation_Import\Excel_Sku_Import.xlsx"
   accExcel.Visible = False
   accExcel.UserControl = False

 Set xlWs = accExcel.Worksheets("Import_Data")
 xlWs.rows("2:65536").ClearContents

 ' Close ADO objects
 rst.Close
 Set rst = Nothing
 ' Release Excel references
 Set xlWs = Nothing
 Set xlWb = Nothing
 Set xlApps = Nothing
 Screen.MousePointer = vbArrow

Open in new window




Thanks
fordraiders
0
I have a continuous form that displays records from a table that is the record source.  For each record displayed I have a command button that when clicked I want just that record to be copied to a separate table.  What I have now is that all records in the continuous form being copied to the separate table.  I just want the one with the command button to be copied.  How can I do this?
0
Hi,

I have a form with 6 reference fields.  A user can click a checkbox next to each field to enable that field.  To make data entry easier, I am trying to code the following:

On the Enter key being pressed, move to the next available (enabled) field out of the 6 Reference Fields.  If only the first 3 fields are enabled, hitting enter after entering information in the first field will move to Reference 2, after hitting enter, move to Reference 3, after hitting enter it would now ADD the record to the table and move to Reference 1 to start all over again.

Thanks,
Anthony
0
Need to calculate about 6 textboxes on a form by adding 4 and dividing by the sum of the other four

Some of the textboxes are blank and causing a #Num! error.  I am currently using this formula:

=CDbl(Nz((CDbl(Nz([vol1],0))+CDbl(Nz([vol2],0))+CDbl(Nz([vol3],0))/=CDbl(Nz([time1],0))+CDbl(Nz([time2],0))+CDbl(Nz([time3],0))+CDbl(Nz([time4],0))
0
Hi Experts

Is there a way to insert some lines in the details section of  the report like the attached income statement.

Regards

Chris
financial-Statements.docx
0
We currently deploy Office 365 but exclude Access from the install to limit the number of people that can create databases.  
Is there a way to install Access on those devices that do require it without having to send out a fresh Office 365 package with Access enabled?
0
Hi

I have an access database split between front end and back end. Everything works well for users on Windows 7, but I've got 4 new laptops running Windows 10 Pro 64 bit and I get a problem running code that works on Windows 7 64 bit.

Database front end is mde, but with all references changed to be appropriate for the environment.

I have tried WorkingSetSleep, MaxLocksPerFile (in both registry and code), DBEngine.Idle, new instance of DAO.Database specifically for the code in question, closing recordset and database between reads of database, using Sleep() between reads of database.

Anyone got any ideas?

Thanks
0
I have a client who utilizes an Access database installed on a 9 year old Windows 2008 Small Business Server for customer appointment scheduling and background data.

In an effort to improve the data flow speed I'm wondering if moving the database to a new network attached storage device would be an improvement?  The maximum number of simultaneous data calls from different users is 7 although on most days 3 or 4 users would be normal.

Any suggestions?
0
Free Tool: Site Down Detector
LVL 11
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.

Adding awesome vector clipart to LibreOffice

I was recently poking around with LibreOffice and figured out how easy it is to add great vector clip art to ones LibreOffice gallery collection.  My experience with both LibreOffice and the site that provides so many awesome clip art images in vector and many other formats was so pleasant, I figured I would post this tutorial to give then both some love.

Step one in the tutorial is where we download the vector images we want to add to our gallery. This is where flaticon.com comes in.  They have over six hundred thousand icons - all of which you can download in multiple image formats including .png, .svg, .eps and .psd.  Many of the icons are available with their free account but you can get full access to a richer set of icons with their premium plans. Plans range from about $7.50 to $9.99 per month at the time of this writing. If you like their work, consider getting one of their premium plans.  

Gallery1.PNG  Gallery2.PNG











Once you are on flaticon.com, if you are a free user look for one of the icon packs with the [S] in the top left corner - this means the pack is available for free. Icon packs are saved as zip files by default, so you will need to unzip the folder to a familiar location on you hard drive such as Photos folder if you are using Windows - as I am in this tutorial.

With the folder unzipped, you are now ready to fire up LibreOffice. I am using LibreOffice Version: 5.3.4.2 (x64).



Gallery3.PNG
0
i have a web browser control in ms access that displays a fallible PDF. How do i save the changes made to the PDF before closing it?
0
Hello Everyone,
   I have a Microsoft Access database that I had someone code a page to make it web accessible.  However, when I put it on my Win 2012 R2 web server, I get an internal error 500.

500 - Internal server error.
There is a problem with the resource you are looking for, and it cannot be displayed.

The path to the DB is set properly, I set up the ODBC connection on the server and ASP is working fine.

Any guidance on what might be wrong?  Maybe a reminder check list for having web enabled Access DBs?  I haven't done this in years.. .my memory might not be great, and maybe 2012 has different requirements...?

Thanks!
0
I have several subroutines in my Access 2013 application that write to the same output table.  Currently each subroutine opens the output recordset, writes records to it, then closes it.

There are six subroutines and the are executed hundreds of time from a processing loop.

This processing takes a very long time and I am looking for ways to improve the time.

The processing loop and subroutines are all contained in the same module.


Module

Option Compare Database

Option Explicit

DIm wkVar as string
.
. other module wide definitions
.
private sub outsideProcessingLoop()
'
' Can I open the output file here one time such that the called subroutines can write directly to it, wthout having to open and close 'it every time?
'

Do while .....

       processSomeRecords()
       processOtherRecords()

enddo 


end sub

' Typical handling of output file in each of the processing subs

private sub processSomeRecords()
'
Set db = getCurrentDbC
Dim rsOut As DAO.Recordset
Set rsOut = db.OpenRecordset(outputTable, dbOpenDynaset, dbSeeChanges)
.
. processing logic
.
'
rsOut.Close
Set rsOut = Nothing

end sub

private sub processOtherRecords()
'
Set db = getCurrentDbC
Dim rsOut As DAO.Recordset
Set rsOut = db.OpenRecordset(outputTable, dbOpenDynaset, dbSeeChanges)
.
. processing logic
.
'
rsOut.Close
Set rsOut = Nothing

end sub

Open in new window


Is there a way I can define the output file in the header of the module, above all subs and/or functions, so that I will only open it once and all the subroutines can write to the open output file without having to open and close it?

IF so how would this look?
0
Hi all

My Access skills are Very rusty and I'm struggling to do what I know should be relatively simple.

I want to create an enquiries/tasks database for my team to use, and I'm basing it on the in-built Tasks database that comes with Access 2010.  That's fine as far as it goes, but I want to be able to customise it to record the customer who has made the enquiry (which will then be assigned as a task to a team member).

I want a facility whereby the person recording the enquiry can select a contact name.  All the contact details (name, email, organisation etc) are in linked tables from another Access database.  I have put together a query to pull all these details into one place, so I can see that the natural thing to do would be to have a combo box from which to choose the contact details.

I've managed to create the combo box, and managed to ensure the ID for the contact is stored against the enquiry so that we can pull back records in future of who has asked what etc.  What I want to be able to do, though, is to enable a search facility (via a parameter query on surname or similar), display all contacts that match the criteria, have the user select the person they want, then display the details of the selected contact on the enquiry form so that when users scroll through enquiries they can see who has asked what.

If it were up to me I'd store all this data in tables and have done with it, but I'm trying to pull this together for colleagues who have limited IT…
0
On my entrepreneur module I have a form (Pos input form) which shows the following to the users while capturing data:
(1)      Total cash payable
(2)      Cash received
(3)      Change to be given to the client
(4)      Stock current balance by product selected by user
(5)      Average cost by product selected by user
(6)      Average profit by product selected by user
Now I want also to show the total cash received by each logged user on the same form so that they are able to check their cash collection at any time rather printing a report. I have tried to use Dsum but it keeps on giving me either error or #Name? Below is my Dsum:
Dsum(“ TotalCash”,” tblPosdetaillines”,” [Userloged]= “ &[Userloged])

Please note Userloged is a string, this control is updated in table through the forms before insert event, example:
Me.Userloged = Getuser()

I want also to extend the Dsum to look like below:
Dsum(“ TotalCash”,” tblPosdetaillines”,” [Userloged]= “ &[Userloged]& “ AND [PosDate] =#” &[PosDate]& “ #” )
What I’m trying to do here is to get the current cash balance by user and by date. How best can I do this??

I have also a report for cash collection by userloged ,I have tried to total by user , but it keeps totalling by all users , any idea here . For example I want to see something like below:

22/01/2018   Chris  $20
22/01/2018   Chris  $30
Total                 2       $50

22/01/2018    Peter $15
22/01/2018    Peter $20
Total                 2        $35

Final Total       4     …
0
Hi All,

Is there a way to Maximize and move sub forms in the main form thru code in ms access 2010?
Please let me know if possible
Thanks
0
I have a large database where each person has a unique medical record number and an admission no (primary key)for each hospital admission they have.  I have a problem with one patient when I enter his medical record number the admission numbers are not linking and only  showing one admission number.  

Any suggestions please to rectify this?
Thanks
0
I create a log file, LogEvent.txt in my application.  How can I display the file from a command button in the application.

Thanks in advance

CRB
0
Get expert help—faster!
LVL 11
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

I'm trying to email several reports but it takes too long probably because each report appears on the screen and I really don't need it to do that.  Here is my code presently:  (Is there any way to speed up the process?)  Note, I've tried acViewReport but it still appears first which takes too long.

'Email Reports...
fileNameIncomeStatement = Application.CurrentProject.Path & "\Income Statement_" & todayDateIncomeStatement & ".pdf"
DoCmd.OpenReport "Income Statement", acViewPreview, , "[Div]='" & strDivNumber & "'", acHidden
DoCmd.OutputTo acOutputReport, "Income Statement", acFormatPDF, fileNameIncomeStatement
DoCmd.Close acReport, "Income Statement", acSaveNo
        
fileNameSalesAnalysis = Application.CurrentProject.Path & "\Sales Analysis_" & todayDateSalesAnalysis & ".pdf"
DoCmd.OpenReport "Sales Analysis", acViewPreview, , "[Div]='" & strDivNumber & "'", acHidden
DoCmd.OutputTo acOutputReport, "Sales Analysis", acFormatPDF, fileNameSalesAnalysis
DoCmd.Close acReport, "Sales Analysis", acSaveNo

Open in new window

0
How to connect Access form/table with SQL server database?
0
I have a main form, single form view.  And then in that form, a sub-form whish is a datasheet view.  In the footer section of the subform I am trying to sub the value of a field named txtGross (Gross is the control source).

What I have for the field's control source in the footer is =Sum([txtGross])  

But I'm getting #Error.

????
0
Am trying to convert this Excel formula:

=IFERROR(((1-J11)*I11),"")

On the Access form...

J11 is txtOtherDamage
I11 is txtNetCWT

What would the control be for the field txtFinalCWT where the converted formula would be entered?
0
Please refer to my posting "Posted on 2017-12-20" with the question header "MS Access 2016 -  Dynamic update of linked table paths after location change of a database file".  Gustav Brock was the expert who helped me to resolve this issue on my original posting.

Summary of the problem:

Copying the same database file between the local and network locations with linked tables can be very tedious work.  To make my life easier to work with that structure I have been using functions that was automatically changing the links back and forth depending on the location.  If I was on my local drive changing from the network path to the local path.  Both locations have the identical file and folder structure.


Here is the current status of the process that has been working up until this morning.

File and Process Setup:

  • I have identical Access files on my local drive and on the network location.
  • Not very infrequently I copy these files back and forth and relinking tables between local and network drives. This have been done via VBA function.  As part of this process I created tables using a query based on mysysobjects to display the current link table paths.  I have two tables like this one for the local drive and one for the network drive.
  • Also, I came up with two sets of VBA code again to change the links using the available tables with the applicable paths in either location .  This process have been working like a charm since January 1st.
0
I am getting "System Resource Exceeded" when exporting using VB 2015 application and Access MDB file. On my development PC in VB Studio I get no such error. Dev PC is 64 bit Windows 7 PC with 8 Gig RAM. The Live PC that is incurring the error is Windows 10 64 bit with 8 Gig RAM.

Is there an option for OLDDB similar to this one below that I can increase this setting? Any other ideas? I found a hotfix for this on Microsoft web site but it has been expired and the registry entries they suggest to change do not exist on the Live PC.

SUGGESTED in other articles: "dbEngine.SetOption dbMaxLocksPerFile, 100000"
0

Microsoft Access

218K

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.