Hi Experts there!
I’m thinking of dropping all bound subforms and replacing them with unbound forms to try and avoid the following:
(1)      Avoiding locking system and significant gain on performance, this will have done by using append queries with the help of VBA, for sure I have to match the data reference to the primary table.
What is your advice on this one? Any problem you anticipate in future?

Regards

Chris

Dim rstTBDSectionWithDashCount As Recordset
Set db = CurrentDb

strImportTableMR = "5-1"
rstTBDSectionWithDashCount.Open "Select * From tbl_First_Occurrences_Report where tbl_First_Occurrences_Report.txt_Section_With_Dash = '" & strImportTableMR & "'"
rstTBDSectionWithDashCount.MoveLast
intrstTBDSectionWithDashCount = rstTBDSectionWithDashCount.RecordCount

Hello All.
I am getting an error message because "OPEN" is not in the method list.
What I am trying to do is.

Look in Table:
tbl_First_Occurrences_Report

In Field:
txt_Section_With_Dash

With the value of"
5-1

For this particular case my final goal is to get the Number of Records for the variable.

Thanks
Nate

Access update query

I need to pull the first word from TaskDescrip  and update Activity field with that word

SELECT tbl_Tasking.Activity, tbl_Tasking.TaskDescrip
FROM tbl_Tasking;

I made a form to browse images held in an ImageList control (named Images).
The form also has 2 buttons to navigate forward (named Next) and backward (named Previous), as well as an Image control (named ImageDisplay) to display the current image.

So far, my attempts to display something failed.
My code is fairly simple:

Option Compare Database
Option Explicit

Private mIl As MSComctlLib.ImageList    '// reference to the ImageList control
Private mIndex As Long                  '// index of currently displayed image

Private Sub Form_Load()
    mIndex = 1
    Set mIl = Images.Object
    
    DisplayImage
End Sub

Private Sub Next_Click()
    If (mIndex < mIl.ListImages.Count) Then
        mIndex = mIndex + 1
    End If
    DisplayImage
End Sub

Private Sub Previous_Click()
    If (mIndex > 1) Then
        mIndex = mIndex - 1
    End If
    DisplayImage
End Sub

Private Sub DisplayImage()
    ImageDisplay.Picture = mIl.ListImages(mIndex).Picture    '// failure here, the Picture property return the image's handle
End Sub

Select all Open in new window

So, how can I display images held in an ImageList control, in an Image control ?

Hi Experts;

I need a clear advice on the Ms Access tables storage size:
Our client with a supermarket has many products on the shelves, now our current Point of sales works as follows:
(1)      For every product line, five line entries are generated as follows (Revenue line, Vat Line, Cost of Sales Line, Stock line and the Cash/Receipt line)
(2)      All the five lines are stored in POS Table details
My question or worry is can this table manage to handle let say 850, 000 product sold per year which is equal (850000 X 5 lines = 4,250,000 lines) if an access table can handle that, then can we continue to use the same POS for the next 5 years? Will it not burst???????
The above line represent data that will be required for accounting purpose, example:
(1)      Revenue Account
(2)      Cost of sales Account
(3)      Vat output Account
(4)      Stock Account
(5)      Cash/Receipts Account
The performance as at now is very good no issues at all.
Regards

Chris

Recurring Access MDB data corruption

I am upgrading a Access 2003 application to Access 2016 for a client.  The application has about 8 - 12 users.

For the last nine years each user had their local copy of the FE mdb.  The data MDB resided on the server and all user were linked to it.  Nine years, no corruption issues.

During that time all users had Access 2007 and Windows 7 installed on their local machines.

The client wants to move to Win 10/ Access 2016.

I upgrade the FE application to an accdb.  Most functionality in the application stayed the same but some of the functionality, mostly the export of reports in preview mode had ot be revised since the old Access 2003 'Export' threw an error in Access 2013 and 2016.

I wanted to a controlled rollout to find any problems in the Accdb prior to rolling it out to all users.  The client purchased two WIn 10/Access 2016 machines.  I installed the upgraded application on those machines.  The backend DB is still and MDB.    All users (Win 7/Access 2007) and WIn 10 Access 2016) connect to the same backend MDB

The first couple of days went smoothly with about 7 users on the old Win 7/Access 2007 version and 2 user on the new Win10/2016 versionbut now their seems ot be a data corruption issue almost daily.  The users get '3343 Unrecognized database format'.

Given this scenario, does anyone see a reason this issue is occurring?

Several imes in the past I have had clients with a mix operating systems and Access…

The data is stored in this way:  Year, Quarter (1st, 2nd, 3rd, 4th), DatabaseName, tablespace_name, Size(MB)


I have an access database that allows the user to choose the database name (from a drop down),
then (based on the database chosen) choose the "from" date, then the "to" date.  This gives the growth of the data during the selected time period.
It works OK but I'd like to revamp the database.

I've created a table for each database to hold the data for each database. See samples below for databases "FINA" and "PAYR".
 

TABLE:  tbl-FINA_Data

YEAR  QUARTER  APPLICATION  TABLESPACE  SIZE(KB)
2017  1ST      FINA         FINA_DATA   29383
2017  2ND      FINA         FINA_DATA   33228
2018  1ST      FINA         FINA_DATA   38222


TABLE:   tbl-PAYR_Data

YEAR  QUARTER  APPLICATION  TABLESPACE  SIZE(KB)
2017  1ST     PAYR          PAYR_DATA   3892983
2017  2ND     PAYR          PAYR_DATA   4493903
2018  1ST     PAYR          PAYR_DATA   4899393
2018  4TH     PAYR          PAYR_DATA   5300203


I also have a table of the database names:

TABLE: tbl-DB_List

DBCODE  DBDESC
FINA    FINANCIAL
PAYR    PAYROLL

I made a form that populates a drop down box of the Databases from tbl-DB_LIST

When the user selects the Database name form the list, I want to do the following:

1. Obtain the DBCODE (this I can do)
2. Plug the DBCODE into a variable that is used to construct the name of the query.  This query selects distinct Year & Quarter …

I have a table called "Parameters" in Access 2010 that contains a ranges of years by Category that I'd like to retrieve


I have a data table that I'd like to retrieve records from on "years" that fall within the minimum and maximum year range by category on the Parameters table. For example, based upon my parameters, the data highlighted in yellow below is the data that would be retrieved.


I appreciate any recommendations as to what would be the most efficient way to execute this. Ideally, I'd prefer to do this through SQL, but if this can't be achieved in a single query, I'm open to running a VBA function or whatever you think is best.

Thanks in advance for any suggestions!

Is it possible to freeze multiple column in a report?

I need to do some work on a client's DB.  Despite the clients best efforts and repeated emails some user don't close the Access 2013 application when leaving for the day.  I did some research on forcing user out of the DB after some period of inactivity and came up with this solution on the Microsoft website.

Microsoft Suggestion For Forcing User Out of an Application

I am hesitant to use this one due to its use of 'TimerInterval'.  Somewhere along the line in my many years of Access development I remember being warned against using the 'TImerInterval' because in some way it could become problematic.  

Do any of you have experience with the having to remotely shutdown users due to inactivity?  DId you use the 'TImerInterval' function or are there better methods?