Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x

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

in my office they were using a lease line of 100mbps previous it was 30mbps,while upgrading to 100mbps,in workstations im only able to get 45 mbps only, while troubleshooting my mux end im able to get 100 mbps. then the uplink from mux is going to a cisco 4321 router form that router to my access switches (sg 300).
my question is tht is cisco 4321 router is capable of handling 100mbps?if it can how i can solve this
0
Free Tool: IP Lookup
LVL 11
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

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
If Me.txtGross is 566.60 and txtMultiplier is 7.00% (datatype = number, double, percent, 2) what is the formula to come up with an answer of 526.94 ?

The human understandable (at least for me) is 566.60 x 93% but I can't figure out how to write the control source for the answer field.
0
I have a query that loops through a list of db schemas and outputs the results to individual pdfs. I would like to append the results to a single excel spreadsheet instead of individual files. How do I do this?

Function UsageReport()
Dim ssql As String, lookup As String
Dim qd As DAO.QueryDef, db As DAO.Database
Dim Cus As String



Open "D:\Access\UsageList.txt" For Input As #1
Do While Not EOF(1)
Input #1, Cus

Set db = CurrentDb()
Set qd = db.QueryDefs("UsageReport")

ssql = "Set nocount ON; select * from air_client_" & Cus & ".dbo.usagereport "
qd.SQL = ssql

On Error GoTo UsageReport_Err

    
    DoCmd.OutputTo acOutputReport, "UsageReport", "PDFFormat(*.pdf)", "D:\Reports\UsageReports\UsageReport_" & Cus & ".pdf", False, "", , acExportQualityPrint


UsageReport_Exit:
    Exit Function

UsageReport_Err:
    MsgBox Error$
    Resume UsageReport_Exit
Loop
Close 1
End Function

Open in new window

0
Hi
On my app, I have a subform that has a data source linked to a query. However, whenever I start the master form, it first loads the subform and, if there are no records in the query, it actually creates a record (!).
What I want is a readonly query that does not generate an (almost) empty record - its master key, linked to the main form is correct.

I have tried opening the query as "snapshot":
Set q02_exameSet = LifeLingerDB.OpenRecordset("tblExame", dbOpenSnapshot)
but it still creates a new record!
can anyone help?
0
I have an Excel formula that looks like:

=IFERROR((MAX((((G11-$M$5)*F11)*$M$6),0)),"")

The corresponding Access form fields are:

G11 = txtMST
M5 = txtMoistureMax
F11 = txtCleanCWT
M6 = txtDisSchd1

How do I convert this to Access?
0
I am getting an error with the DateSerial Function.  I'm using the tabular query builder in MS Access.  When I press RETURN in the field row, I get this:  The expression you entered has a function containing the wrong number of arguments.  Here is the text of my function:

CompMonth: DateSerial(YEAR(Date([Comp date])),MONTH(Date([Comp date])),1)

I marked this high priority because today is the last day my college programmer is with us.  I'm trying to validate a page in the database dashboard by aggregating data to confirm that what I'm seeing is correct.  If we have an error, we must fix today.

Thanks!
0
We have a List where data is fed through  a form i.e (+ New), I would like to restrict it in a way that majority of the Sharepoint users can only view the List and not be able to access the options in the red box. Only assigned users can have access to the red box.RFLForm.jpg
0
 
LVL 23

Expert Comment

by:Ferruccio Accalai
Is this meant as a question? If so open a question instead of a post
0
 

Author Comment

by:RFLafferty
Thank you
0
PROBLEM
Trying to pass on the value in the field "Volume in Liters" that is on the form "frmlSilicaSamplingSetup" or Sampling Information Setup to the report "rptChainofCustody" report or Chain of Custody .

WHAT I HAVE TRIED
I copied the field 'Volume in Liters" field from the form Sampling Information Setup that has this function in it: "=[Total Sampling Time in Minutes]*[Average Flow Rate (Start Flow Rate X End Flow Rate)]" to the Chain of custody form.

HOW TO RECREATE THE PROBLEM

1. OPEN APPLICATION
2. CLICK THE BUTTON THAT SAYS "SAMPLING SETUP" AND ENTER 3 WHEN THE PAREMETER COME UP.
3. LOOK AT THE VOLUME IN LITERS AT THE BOTTOM OF THE PAGE.
4. CLOSE FORM AND OPEN THE REPORT BY CLICKING "CHAIN OF CUSTODY" REPORT BUTTON AND YOU WILL SEE THE VALUE IS NOT IN THE "sAMPLE vOLUME lITERS".
fORM-rEPORT.jpg
Silica_App---V12.accdb
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.

I'm using the following code to get fields in a datasheet view form to appear in the right order.  But no matter what I do, txtGrossCWT won't appear in the 3rd column.  Am I doing something wrong?

    Const TWIPSTOINCHES = 1440
    Me!txtLoadID.ColumnWidth = TWIPSTOINCHES * 0.875
    Me!txtReceived.ColumnWidth = TWIPSTOINCHES * 1.25
    Me!txtGrossCWT.ColumnWidth = TWIPSTOINCHES * 1
    Me!txtTW.ColumnWidth = TWIPSTOINCHES * 0.75
    Me!txtFM.ColumnWidth = TWIPSTOINCHES * 0.75
    Me!txtCleanCWT.ColumnWidth = TWIPSTOINCHES * 1.125
    Me!txtMST.ColumnWidth = TWIPSTOINCHES * 0.75
    Me!txtShrink.ColumnWidth = TWIPSTOINCHES * 1.125
    Me!txtNetCWT.ColumnWidth = TWIPSTOINCHES * 1.125
    Me![txtSplits&Dmg].ColumnWidth = TWIPSTOINCHES * 1.125
    Me!txtPrice.ColumnWidth = TWIPSTOINCHES * 1.125
    Me!txtGrossCWT.ColumnWidth = TWIPSTOINCHES * 1.125
    Me!txtTotal.ColumnWidth = TWIPSTOINCHES * 1.125
    Me!txtLoadRecordID.ColumnWidth = TWIPSTOINCHES * 0

    If TWIPSTOINCHES * 1 > 0 Then
        Me!txtLoadID.ColumnHidden = False
        Me!txtReceived.ColumnHidden = False
        Me!txtGrossCWT.ColumnHidden = False
        Me!txtTW.ColumnHidden = False
        Me!txtFM.ColumnHidden = False
        Me!txtCleanCWT.ColumnHidden = False
        Me!txtMST.ColumnHidden = False
        Me!txtShrink.ColumnHidden = False
        Me!txtNetCWT.ColumnHidden = False
        Me![txtSplits&Dmg].ColumnHidden = False
        Me!txtPrice.ColumnHidden = False
        

Open in new window

0
I'm trying to translate this from an Excel worksheet:

=IF(C11<1,"",IF(E11>$M$7,(((1-$M$7)*C11)-(((E11-$M$7)*$M$8)*C11)),((1-E11)*C11)))

to an Access form field calculation.

Note that in the Access form these are the cooresponding field names:

C11 = txtGrossCWT
E11 = txtFM
M7 = Header txtFMMax
M8 = Header txtDisSchd2

I don't even know where to begin.  Any help would sure be appreciated.
0
I am trying to write a simple query to show me my "groups" of PC's I have in an Access database. I have a table called PC_EQUIP with 4 fields that I need: type, model, warranty and serial. This query works fine:

SELECT DISTINCT Type, Model, Warranty FROM PC_EQUIP

This shows me the information I mostly need since we order PC's in groups so the type, model and warranty date of each group will be the same and this query shows me the different groups that I want to see.

I really want to add two more pieces of information. Even though I am using DISTINCT, I would like to see one of the serial numbers for each DISTINCT group, dont care which. Obviously, if I add serial to the query above, I get a list of all records and the "DISTINCT" is useless. I would also like to add a COUNT to the query so it will show me how many rows are rolled up in that DISTINCT clause effectively showing me how many PC's I have on hand for that group,

The serial number part is optional but I cant seem to figure out how to make it return the count for each grouping.

Thanks
0
We have an access application using a SQL Server database back-end.  Connection to the database is via ODBC.  We are setting up a Dev environment - Dev Access front-end and a dev SQL Server database.  ODBC has been created pointing to the Dev database.  How do we now update the table link so that they are pointing to the Dev database and not the original production DB?  I clicked External Data--> ODBC Database and it prompts me to 2 options -
1) Import the source data into a new table in the current database
2) Link to the data source by creating a linked table.

It seems that I need to select option 2.  Then it will prompt me to the Data Source which will allow me to select the Dev database.  Then prompts me to select tables.....Am I doing the right thing???  I've never worked with Access before.
0
Hello-   I am using a IN SELECT statement  to add a date to my query in MSACCESS,  In (select AD from Admit_Date). Where AD is a field from the local table Admit_Date.

I want to call a date range from two local tables  the two statements I have is  In (select AD from Admit_Date)   and

I tried something like these, but it does not  seem to work:

Between (In (select AD from Admit_Date) and (In (DC from Discharge_Date))
or
Between In (select AD from Admit_Date) and In (DC from Discharge_Date)
 

Is there a better way?
0
I'm using the code below and it's in a never ending loop. Thoughts

Public Function Import_System_Access_Reports()

Dim strFolder As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strFile As String
Dim strTable As String
Dim strExtension As String
Dim lngFileType As Long
Dim strSQL As String
Dim strFullFileName As String
Dim varPieces As Variant

 With Application.FileDialog(4) ' msoFileDialogFolderPicker
     If .Show Then
         strFolder = .SelectedItems(1)
     Else
         MsgBox "No folder specified!", vbCritical
         Exit Function
     End If
 End With
 If Right(strFolder, 1) <> "\" Then
     strFolder = strFolder & "\"
 End If
 strFile = Dir(strFolder & "*.xls*")
 Do While strFile <> ""

     lngPos = InStrRev(strFile, ".")
    strTable = "RawData" '<- this could be a constant instead of a variable
Set db = CurrentDb()
' make the UPDATE a parameter query ...
strSQL = "UPDATE [" & strTable & "] SET FileName=[pFileName]" & vbCrLf & _
    "WHERE FileName Is Null OR FileName='';"
Set qdf = db.CreateQueryDef(vbNullString, strSQL)

strFile = Dir(strFolder & "*.xls*")
Do While Len(strFile) > 0
    varPieces = Split(strFile, ".")
    strExtension = varPieces(UBound(varPieces))
    Select Case strExtension
    Case "xls"
        lngFileType = acSpreadsheetTypeExcel9
    Case "xlsx", "xlsm"
        lngFileType = acSpreadsheetTypeExcel12Xml
    Case "xlsb"
        lngFileType = acSpreadsheetTypeExcel12
    End Select
    strFullFileName = strFolder & 

Open in new window

0
Hi There -
I have a code snippet that is not working when I try to use CurrentProject.Path from MsAccess to point to a pre made
Excel Workbook and place data in it using CopyFromRecordset. It has to be something simple I'm missing here.
Thanks in advance,
GeneBat
 (see code below) Cap if you're out there I need help. :)
 
Public Function ExcelfromAccess()
    
    On Error Resume Next

If MsgBox("Are You Sure You Want to Export This data to Excel?", 289, "Export to Excel") = vbCancel Then
        
        Exit Function
   
    Else
    'open excel
        Dim AppExcel As Excel.Application
        Set AppExcel = New Excel.Application
        'Dim strFileName As String
        
    'open new workbook
        Dim oWB As Excel.Workbook
        Dim sFullPath As String
        
        sFullPath = CurrentProject.Path & "\UcaasTemplate.xlsx"

'        Set oWB = AppExcel.Workbooks.Add("UcaasTemplate.xlsx") 'you can specify a template here
'        strFileName = Application.CurrentProject.Path & "\UcaasTemplate.xlsx"
'        Set oWB = AppExcel.Workbooks.Add(Template:="C:\Users\xtek109\Documents\Ucaas Project\UcaasTemplate.xlsx") 'template name goes here + this works
        AppExcel.Visible = True
        AppExcel.Workbooks.Open (sFullPath)
    'copy data to Excel
        oWB.Worksheets(1).Range("A5").CopyFromRecordset CurrentDb.OpenRecordset("LdapCutsheetFormat", dbOpenDynaset)
        
    'turn excel over to user control
        AppExcel.Visible = True
        

Open in new window

0
Hi I am getting the above error on a VNA statement, when trying to set the SQL definition of a query on ms/access.
Can anyone help?

Screen-Shot-2018-01-18-at-15.42.02.png
Screen-Shot-2018-01-18-at-15.42.11.png
0
I'm using the following function and getting a runtime error 3265 on line
 qdf.Parameters("pFileName").Value = strFile


Public Function Import_System_Access_Reports()

Dim strFolder As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strFile As String
Dim strTable As String
Dim strExtension As String
Dim lngFileType As Long
Dim strSQL As String
Dim strFullFileName As String
Dim varPieces As Variant

 With Application.FileDialog(4) ' msoFileDialogFolderPicker
     If .Show Then
         strFolder = .SelectedItems(1)
     Else
         MsgBox "No folder specified!", vbCritical
         Exit Function
     End If
 End With
 If Right(strFolder, 1) <> "\" Then
     strFolder = strFolder & "\"
 End If
 strFile = Dir(strFolder & "*.xls*")
 Do While strFile <> ""

     lngPos = InStrRev(strFile, ".")
    strTable = "tblConsolidated" '<- this could be a constant instead of a variable
Set db = CurrentDb()
' make the UPDATE a parameter query ...
strSQL = "UPDATE [" & strTable & "] SET FileName=[pFileName]" & vbCrLf & _
    "WHERE FileName Is Null OR FileName='';"
Set qdf = db.CreateQueryDef(vbNullString, strSQL)

strFile = Dir(strFolder & "*.xls*")
Do While Len(strFile) > 0
    varPieces = Split(strFile, ".")
    strExtension = varPieces(UBound(varPieces))
    Select Case strExtension
    Case "xls"
        lngFileType = acSpreadsheetTypeExcel9
    Case "xlsx", "xlsm"
        lngFileType = acSpreadsheetTypeExcel12Xml
    Case "xlsb"
        lngFileType = 

Open in new window

0
Receive 1:1 tech help
LVL 11
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

I have written the below code, which works fine until it has to pass the sql code to the query definition (CurrentDb.QueryDefs(qry2).sql = sql), as then I get a "System Resource Exceeded" error.   In the example I am testing, the number of OR clauses in qString will be 131.  Any ideas?

Thank you.

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Public Function ImportServicePOs()
On Error GoTo ImportServicePOs_Err

'This function imports the spreadsheet with te POs
Dim tbl As String, fd As FileDialog, fn As String, qry As String, tbl1 As String, tbl2 As String, db As DAO.Database, rst As DAO.Recordset, qString As String, qdExtData As QueryDef, qry2 As String, pos As String, sql As String
tbl = "tbl_TempPos"
tbl2 = "tbl_PoNumbers"
qry = "qry_ServicePosWithVendorDetails"
qry2 = "qry_ServicePosWithVendorDetailsPQ"
tbl1 = "tbl_ServicePosWithVendorDetails"
Set fd = Application.FileDialog(msoFileDialogFilePicker)

fd.AllowMultiSelect = False

MsgBox "Browse for the PO file to import...", vbOKOnly, "Attention!"

If fd.Show = True Then
    If fd.SelectedItems(1) <> vbNullString Then
        fn = fd.SelectedItems(1)
    End If
Else
    'Exit code if no file is selected
    End
End If

DoCmd.SetWarnings False
DoCmd.DeleteObject acTable, tbl
DoCmd.TransferSpreadsheet acImport, , tbl, fn, -1

Set rst = …
0
1-In the reports tab, under the monthly sales, how can I add a profit and loss for the month?
2-How to delete a completed related customer?
0
I have a query that shows employee attendance. I created a cross tab query for this. The cross tab query shows the names of employees on the rows and the dates as the column headings. In the columns it show Present if atx_time_amount >0 and X if it is < 0. Only thing I realized is the atx_time_amount field will never be a negative number. It will always have a positive number or be blank if they are absent. So I am trying to figure out how to make it so the report shows present for atx_time_amount > 0 and X for a blank field. I added an attachment so you can see what I am working with.

THANKS!
Query.PNG
0
Hi all.

I have an Access form with a combobox whose row source is set to "Table/View/StoredProc" and is populated in the On Enter event. There are 2 columns with widths (0";1") the first column is the ID and the second is the name, the end user sees the name but when they save the record the ID is what gets saved in the table.

The problem we're having is if the end user enters the combobox via the mouse or tab without clicking on the down arrow it temporarily changes the selected value/text of the combobox to the first entry of the combobox but then it reverts back to the original selected value/text when you leave the combobox.

Any idea how we can stop this from happening?

Thank you in advance.
0
How can i do random balance in ms access? as like attached file
Bank-Statement.xlsx
0
I have a field on a form named "Scheduled Date".  And then using my Outlook calendar, I wondering if I schedule a calendar event in Outlook calendar with a specific job number in the calendar Subject, and then launch the Access database, could the jobs in the tblJobs somehow bring those calendar events over to the field on the form.

Crazy I'm sure.  Anybody done this before?
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.