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

Sql Server SSIS packages that interact with Microsoft Access 2016 files have been very inconsistent.  I have tried odbc connections as well as using the ACE driver.  Some work all the time other access files show locked all the time even when the lock file is not showing.  Is there a resource that documents how to work with these issues?  Of course it would be nice to know if you really can kill the lock file but I have looked quite a bit for solutions and found nothing that would work with the environment we are working with.  These access files are being connected to by other sources that an access front end files.  Sometimes even excel is used to pull data.  But again I have some that are being connected to in a variety of ways but works  in ssis all the time.  We are using a unc for the pointer to the access file itself in the package connection.  Then we could be pulling from a table in the access file and also pushing data from sql server to a specific table in access which is being worked through the access front end at the same time by multiple users.  This is a tough issue no one wants to do this but unfortunately right now there is no alternative.  The other factor is some files have passwords and the ones that don't seem to work so the encryption may be part of it.  Thanks you
0
i have a task that i cant seem to wrap my brain on. I have an excel sheet that tracks swab testing for areas in the plant for bad bacteria. Each swab vector has to passa 14 day swab test. the test must be negative for 14 days in a row. If any test results are positive in any time of the 14 days the test starts back over to day one. I have no idea how to design this  that is user friendly  and automatically starts over if the result is positive.
I have attached the excel form so you can see what i have been tasked to do. Any ideas would be helpful.
14-day-follow-up.xlsx
0
My customer has changed their portal login page and my download code no longer works. I think the only change was the actual home login page.

I'm trying to re-code this in order to have Access login and Navigate to some data that I download daily. The target webpage recently re-wrote their login page. Therefore, my login system no longer works. I think the back-end is still good. It's just the login that has me messed up.

FUNCTION THAT ENTERS LOGIN VARIABLES IN ACCESS
I removed most of it. I'm only listing the code where my variables are not transferring to the target web-page.

**I did NOT include everything because I didn't want to populate the page with code that is NOT needed here.**
Access Code
 For Each drp1 In QPR.Document.Forms
 'MsgBox drp1.Name
    If drp1.Name = "Login" Then
         With QPR.Document.Forms("Login")
        .User.Value = (myUserName)
        .Password.Value = (myPassWord)
        .submit
        End With
    End If
  Next drp1
 

Open in new window



CODE WHERE I SET MY VARIABLES IN ACCESS

Private Sub Command155_Click()
'Assign the user inputs to variables that will be used in the Web application.
'Tell VBA what to do when the user does not input a date format
On Error GoTo NotADate

    myStartDate = Format(CDate(txt_StartDate), "mm/dd/yyyy")
    myEndDate = Format(CDate(txt_EndDate), "mm/dd/yyyy")
    myNAMC = txt_NAMC
    myUserName = txt_UserName
    myPassWord = txt_Password

    'Inform the user that this program is operating 

Open in new window

0
Please see the attached picture. On the left side is a form of fields that the user must enter some technical information. On the right side is a window with a filter which allows the user to select an occurrence that they want to enter information for. Once the user enters this information the line item is removed. (Task completed)

I would like to be able to create a report for that line item that can be attached to an email and sent to other members.

The information is being entered into and stored in a table called QREVALUE. The primary key to that table is ScrapTag. It would be OK if there was a method that I could simply export a .pdf into a folder location. Triggered by a button or moving on to another record being selected or something.

What would be the best approach for this?
QREVALUE.png
0
I am trying to split a MS Access 2016 database on a sharepoint server. When i try re-linking the tables in he FE to the BE in the Share point it keeps saying file not found. Please Help!
0
Im trying to have a previous button and next button to scroll through attachments of a record on a form. I don't know the code to do that. Each record can have several picture attachments per bacteria swab sample.
 attachment-scroll-buttons.jpg
0
Hi everyone,

After doing ALOT of reading and research, I turn to the experts to help me out. We have an MS Access Application that is used by hundreds of customers. It was originally built on MS 2007 but through the years we have upgraded all the way to development in Ms Access 2013.

We have quite a few customers that have installed Office 365 in different languages (ex: French and Spanish). For our MS Access application the underlined MS Access program has to be in English.

We installed MS Access runtime 2013, but very often we get a weird on open Err:0 "can't find the object xxx". The only temporary fix was to transfer the application to a PC with Office 365 in English and open it there. It would open normally, then we would return the application back to the original PC until it happens again. That's not a viable solutions.

We tried to install MS Access Runtime 2016 but it does not allow it to be installed with Click to Run Office.

Finally we installed MS Access Runtime 365 and it allows to open however it is really slow. We wanted to tweak the registry setting (ie MaxBufferSize) but can't seem to find the location in the registry.

Has anyone else had this problem? This whole CTR thing is becoming a real problem.

Any advice would be greatly appreciated.

Regards,
TSIsolutions
0
MS  Visio question. I have a org chart built off position number as the key field. This field is always assigned to the same division, Program and team. However it can change who the position reports to. Whenever a change to the data is made I receive the error message conflicting data. The chart is being refreshed my a ms access data table. Any assistance would e greatly appreciated.  Thanks in advance.
0
Does anyone know how to make an access form auto populate on the form the name of who is logged into office 365? My goal is to have access forms on my Sharepoint website for my staff to fill out but whenever they fill out a form, it needs to pull their login in name for verification on who filled out the form.

Thanks!

max
0
I want to be able to send an attachment, which is a report, as a PDF using Yahoo mail.  I need to include the sender's email address (hard coded), the To email address (hard coded), the BCC email address (hard coded), the Subject line (hard coded but with the addition of the date on the report AND the location which is on the report), and the body copy (as html copy), and don't know where to begin.
0
Access MenuHi,
 I have Windows 2008 Server running ACCESS database in 2003 version of ACCESS. It runs fine.
 Today I installed Windows server 2019 and installed ACCESS 2003 on it. When I open my database file, it closes the database file automatically as it tries to open "mainmenu"  form.
 When I open it while holding down the [Shift] key, I can see the all my objects - Tables, Queries, Forms, Reports ... etc. However the moment that I try to open any forms, it kicks me out of ACCESS. I can open tables without any problem. My security/Macro is set to low.
can you help?
0
I have an excel workbook I created with

DoCmd.OutputTo acOutputQuery, "qry_chart_sold_ppsf_year", acFormatXLSX, sfilename, Autostart:=False

I would like to add 3 additional worksheets to that workbook, names worksheet_1. worksheet_2, and worksheet_3.

The data for the worksheets is from qry_1, qry_2, and qry_3

What would be ms vba code?

Thanks,

WCGee
0
In an MS Access query, Field1 could contain an integer from 1 to 50000. I need help to create a function, or preferably a formula to convert this into a bracket. In the table below, the letter describes the bracket name (the output of the function or formula) and the integer range describes the range of Field1. For example, if Field1 = 9, then the formula should yield the letter F.

A_  0 - 1
B_  1 - 2
C_  2 - 4
D_  4 - 6
E_  6 - 8
F_  8 - 10
H_ 10 - 12
I_ 12 - 15
K_ 15 - 20
L_ 20 - 25
M_ 25 - 30
N_ 30 - 40
O_ 40 - 50
P_ 50 - 100
R_ 100 - 200
S_ 200 - 400
T_ 400 - 800
U_ 800 - 1,600
V_ 1,600 - 3,200
W_ 3,200 - 6,400
X_ 6,400 - 12,800
Y_ > 12,800
0
Hello, All.

OK, what used to work, no longer work.
A multiple row insert, will now only, insert all into a single field.

environment: Windows 10 Pro / IIS
inserting into a Microsoft Office Access Database ACCDB
(This is a site for the intranet, and not for the internet. Which is the reason for using the access database)

Code, that used to work.

The form has all TEXTAREA's that are feeding into the insert form.

arrMN = Split(Request.Form("MediaName"), vbCrLf)  
arrMF = Split(Request.Form("MediaFile"), vbCrLf)  
arrMB = Split(Request.Form("MediaBand"), vbCrLf)  
 
for i=0 to uBound(arrMN)
  Set objDoubleInsert=CreateObject("ADODB.Command")  
  objDoubleInsert.ActiveConnection=objConn  
  objDoubleInsert.CommandText = "INSERT INTO EVMediaServer (MediaName, MediaFile, MediaBand) VALUES (?,?,?)"  
  objDoubleInsert.Parameters.Append objDoubleInsert.CreateParameter("@MediaName", 200, 1, 255, arrMN(i)) 
  objDoubleInsert.Parameters.Append objDoubleInsert.CreateParameter("@MediaFile", 200, 1, 255, arrMF(i))  
  objDoubleInsert.Parameters.Append objDoubleInsert.CreateParameter("@MediaBand", 200, 1, 255, arrMB(i))  
  objDoubleInsert.Execute  
Next

Open in new window


before, this code used to create as many rows as I had in the fields.
But now, all of it is inserted into ONE ROW, and all the lines are in one column.

What is going on?
Thanks for any and all assistance on this one.

Wayne
0
I can no longer run compile on the debug function. This came to my attention last Friday
because a number of problems started to occur. I picked up on this problem yesterday
when trying fix problems.

I am unsure if this problem is related but I do know that it's not right?

Please help
0
I have a Access Database 2016 that connects to an SQL Server 2012

all forms use the following code to get data:

Public Function GetFormData() As ADODB.recordset

    Dim cnn As New ADODB.Connection
    cnn.Open sqlServerConnection
    cnn.CursorLocation = adUseClient
    
    Dim cmd As ADODB.Command
    Set cmd = New ADODB.Command
  
    cmd.ActiveConnection = cnn
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "Form_Data_Stored_Procedure"
                      
    Set GetFormData= cmd.Execute
End Function

Open in new window


in the Form_Open even I have the code

Set Me.recordset = GetFormData()

Open in new window


this works fine but I cannot right click on a column and sort the data.

this stopped working when we upgraded to Access 2013

how can I sort by right-clicking?

thanks

jack
0
i would like to programmatically change the taskbar height using VBA. Thanks
0
Good morning....

 We have an old accounting System (Sales, Purchases, ledgers, receivables, inventory, etc) called RTS-ADVANTAGE.  
To get specific and customized reports from that system, we have to Export the information via TXT files.   After we have the TXT files we LINK those files to MS-ACCESS, where we can build queries and reports.
The Export process is done using Macros in RTS-Advantage, which runs every 2, 5, 6 hours (let's say).
The process of exporting the information is sometimes slow and is not live.   The users needs real live information (the most possible) and running the macros to export is tedious.

Our need:
We are looking a solution to CONNECT (at least READ) the information DIRECTLY from the RTS-ADVANTAGE tables or files, so no exportation is needed and we can build queries and reports in MS-ACCESS (Preferably).  It might be ODBC for instance.  
NOTE:  RTS-ADVANTAGE was built using C++ and it uses ISAM files.
Please find attached Data Files and Pictures (Power Point) showing the program and Explorer structure.

Looking forward...
Dat-files.zip
ADVANTAGE-PICS.pptx
0
I have a date field and I'd like to for it to add "Todays" date as a default. I need it to be editable so that I can change it if needed. Is there a simple way to do this?
0
Since my client has updated to Windows 10, embedded logo images are lo longer showing on reports.  Any ideas how I can resolve this?
0
Move the current leave balance to next year in access database?
Below is a screenshot of the database. I want to move the current balance of 2018 to 2019 of all employees.
How this can be done? Or how can I automate this on 1st Jan of every year.
CaptureEE.PNG
0
Below is my code:
Public Function getFormName(frmName)
Dim frmDispatch As Form
Set frmDispatch = Forms!dispatchBoard!Board1.Form              '' This line works fine"    "Please see the problem below"
getFormName = frmDispatch
End Function
The Problem:
In the line "set frmDispatch". I don't know the subform (Board1 is a subform example only). The subForm name  i need to process is passed to the function (frmName is the variable passed to the function)
0
I need the proper syntax to delete an excel file [PROBLEM_LOG.xlsfrom a folder prior to the running the following code.
The following code gets a syntax error. When i manually delete the file gets saved with NO problems.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "ProblemLog", CurrentProject.Path & "\" & "PROBLEM_LOG.xls", True

Open in new window

0
Is it possible that the current version of windows, office 365 (access) and server 2019 are not compatible with virtualized machines???

MS Access (o365) is deleting indexes and tables during compact and repair operations with Win10 Pro.  The front end machine is an i5 win10 pro machine.  BE virtual machine with one cpu and 4 cores.

I can consistently reproduce the problem just by running the compact operation with multiple databases.  No error messages are produced during the process, however, a system table is created when a failure occurs.. I don't remember the name buts its obviously a system table with very cryptic info.. one or two rows and no significant data...

When I use infinity to disable all but one core, the process compacts without errors consistently.

Please tell me there is a better solution than programmatically using infinity!

Thanks
0
I have just updated an old 2002 mdb to Access 16 accdb. When I click on any report opening button the report starts to load but the underlying code windows flash on the screen in a semi hidden way until the report is ready to display. There must be some property that stops this. In the old 2002 mdb you could write Echo False and the screen would freeze , although sometimes not freeze, and then render the report. Is there a similar command for access 16. I tried Echo command in 16 and find it has no effect
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.