Microsoft Access

221K

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

MS Access unable to see files moved from 2008 SBS to Server 2016.  The MS Access app is 16bit, running on Windows 7 32bit with Office 2016, accessing data files on Server 2016.

The files are present, can manually open them in Notepad as Domain Admin or one of the Domain Users without any issue.
The File Path is correct in the MS Access App
       * I can create a new record entry, this new record is created in the correct folder on the new 2016 server
       * I can pull up “History” in MS Access, it will only show new files created, not any of the others that came from Server 2008, yet are in the same folder.
       * MS Access does not error with a security warning, just states there are no files to be read if no new records are available.

I have manually reviewed Security settings comparing to the 2008 Server – It all matches
       * Compared the Security against what the MS Access application creates, they match.
       * I have gone through using iCacls.exe to set permissions again including Synch
       * I reviewed SMB on the old 2008 server to confirm SMB1 was not enabled and being used by MS Access
       * I wiped out the files and attempted a normal copy – no change
       * I have confirmed Access Based Enumeration is not enabled on this folder

If I use the exact same syntax with RoboCopy from the 2008 SBS Server to a 32bit Windows 7 system, change the data path being referenced by MS Access, MS Access will see the old files correctly.

Thank …
0
I'm developing in MS Access (VBA) and trying to capture Smart Card (CAC/PKI) certificate information using VBA.  I simply need Friendly Name or Subject for each certificate, as I can see in ActivClient user console - the program I use to manage certificates.  I know MS Access uses the same certificate to add Digital Signature to a database, and in the certificate selection utility, the fields that have the information I need are Subject and Friendly Name (the same as in ActivClient user console).

I've looked around and keep finding X509 store information, but it's based on .NET and C# code, which I do not know how to use in MS Access.  I cannot make a dll from that code, as I am not running Visual Studio or any other programming applications.  Also, the X509 Store is not in any library reference, so I cannot create an object and use it in VBA.

Does anyone know how to get or read certificate information - using VBA and any readily available references?  BTW, the references I've tried loading were as follows, but I couldn't make any headway with them.

ac.activclient.olaaddin 1.0 Type Library
CertCli
CertEnc
CertEnroll
GrooveCryptoServices

Thanks for any help/guidance!
0
Pretty sure I've dealt with this before but alas, no memory!

Using Access 2003

I have a table with 2 fields, a date field and a memo field I need to insert into a 2nd table with the same field name and type. A date exists in every record but in several of those records the memo field is blank.

I only want records with both date and memo to transfer but the memo field is particular when it comes to criteria. Conventional syntax like Not Null or <>" " doesn't work.


What's the trick?
0
Anyone have some existing VBA code I could use in Microsoft Access 2016 to add a new calendar event to Google calendar?
I have looked all over the web already, and I have not found any.
Thanks for your help.
Kevin Rea
Lancaster, California
0
How do I let a user select an printer for an access report?
0
I had this question after viewing Microsoft Access Form With A SearchForRecord Macro In A Combo Box Appears To Be Spontaneously Deleting Records.

I get an error message:  Run-time Error '3622':
You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an IDENTITY column.

Here's my code:  

Private Sub cboFacilityID_AfterUpdate()

'Old way of not displaying warning messages: 
'DoCmd.SetWarnings False
'DoCmd.OpenQuery "PowerlineF_PowerlineParticipationAppend"
'DoCmd.SetWarnings True

"""""""""""""""""""""""
'New way:
CurrentDb.QueryDefs("PowerlineF_PowerlineParticipationAppend").Execute dbFailOnError

FacilityPowerSF.Requery

End Sub

Open in new window



Can anyone provide details on that?
0
Hi all,

I know how to find a duplicate in any table in access that has a key column and date column but how can I delete the duplicate? Please find attached an example of that.
Database8.accdb
0
ScreenshotI'm trying to create what I expect would be a very simple query in Microsoft Access. I've added a screenshot that shows the query for reference. I have one criterion specified the references a date range on a form, which causes the query to select all matches records, which works fine. There are multiple other columns which are true\false that I would like to design the query to select if any one of those has a "true" value. I thought this would be very simple... basically if ANY of the records have a true value for ANY of the columns, then then that record would appear in the results, however all records where there wasn't AT LEAST one true value across all of the columns would be excluded.
0
I am posting form data to a URL using VBA from MS Access.  However, I need to end up on the URL that I'm posting the form data to and I can't seem to do this since the url ends in .php.  How do i land on the same URL that i just posted to?
       
URL = "https://websiteX.com/tastein/enterxx.php"
        Set IE = Nothing
        Set IE = CreateObject("InternetExplorer.Application")
        IE.Navigate "about:blank"
        IE.Visible = True
       
        Set xmlhttp = CreateObject("MSXML2.XMLHTTP")
       
        xmlhttp.Open "POST", URL, False
        xmlhttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
        xmlhttp.send (sPayLoad)
        Response = xmlhttp.responseText
        While IE.busy
        DoEvents
        Wend
        IE.Document.Write Response
0
I have a simple Update query and I can not understand why I'm getting this parameter request.

When I call DoCmd.OpenQuery "qCreateOeReportDueDate" I am getting the "enter parameter" request as seen in the picture attached.

What is triggering this request? What prerequisite is not being completed to trigger this?

query qCreateOeReportDueDate
UPDATE ProblemLog SET ProblemLog.[OE Report Due Date] = [QREVALUE_DueDate];

Open in new window


FUNCTION THAT IS BEING RUN TO TRIGGER THE REQUESTParameter Request
Public Function CreateProblemLog()
DoCmd.SetWarnings False
Module1.DeleteXLLog
DoCmd.OpenQuery "qMakeProblemLogTable"
DoCmd.OpenQuery "qCreateOeReportDueDate"
DoCmd.OpenQuery "qUpdateProblemLogMonthDayYear"
CurrentDb().TableDefs("ProblemLog").Fields("ScrapRecordTag").Name = "DIAMTracking"
CurrentDb().TableDefs("ProblemLog").Fields("SkpiProblemLog_Date").Name = "Occurance Date at Customer"
CurrentDb().TableDefs("ProblemLog").Fields("MyMonth").Name = "Month"
CurrentDb().TableDefs("ProblemLog").Fields("MyDay").Name = "Day"
CurrentDb().TableDefs("ProblemLog").Fields("MyYear").Name = "Year"
CurrentDb().TableDefs("ProblemLog").Fields("TagNumber").Name = "Customer Claim #"
CurrentDb().TableDefs("ProblemLog").Fields("QPRQPINumber").Name = "Other Tracking #'s (RMA#, SLIPS#, e-CIMS, etc)"
CurrentDb().TableDefs("ProblemLog").Fields("Customer").Name = "Customer"
CurrentDb().TableDefs("ProblemLog").Fields("Quantity").Name = "Final Reject Qty"

Open in new window

0
I have an Access front end and SQL back end.  I have a form that has a web browser on it.  The web browser points to a particular control named txtFunctionalLocation to complete the Control Source's URL .  This web browser is kept hidden on the form by an On Open event.  However, once the cboFacilitySearchcombo box is utilized to search for a particular project, it activates the macro below to make the Web Browser control visible.

macro.jpg
The problem I have is that sometimes there's an invalid (placeholder) in the txtFunctionalLocation control box that causes errors in the web browser control.  The error comes from an invalid URL.  The place holder in this position is a numeric integer.  The real Functional Location value will always start with "X" (or possibly some other letter in the distant future).  I need to figure out how to convert the macro pasted above into a VBA code to carry the same functions but only make the web browser control visible if the txtFunctionalLocation control has a value starting with "X" (or any letter).  The "X" is usually followed by a series of numbers.

I found this article:
https://support.office.com/en-us/article/use-like-criterion-to-locate-data-65b07c8a-b314-435a-8b48-2b911856d4f9 and was thinking of maybe [!0-9]* but I'm not sure if it would be correct and how to structure the VBA with everything.
0
Hi Experts,

I am receiving complaints from users from time to time that they are getting an error on a linked SQL view that could not be updated (see attached).

However the strange thing is that when I try doing that same change on my pc it works, moreover after a while (sometimes next day) the error doesn't occur on theirs either...

Any idea how to troubleshoot this?

Below is the SQL of the view in question.

SELECT     dbo.Skilled_Nursing_Visit_Note.ID, dbo.SNV_Printed_History.VendorsID, dbo.SNV_Printed_History.SNV_ID, dbo.Skilled_Nursing_Visit_Note.Client_Last_Name, 
                      dbo.Skilled_Nursing_Visit_Note.Nurse_Signature_Last_Name, dbo.Skilled_Nursing_Visit_Note.Visit_Date, dbo.Skilled_Nursing_Visit_Note.Shift_From_Hour, 
                      dbo.Skilled_Nursing_Visit_Note.Shift_To_Hour, dbo.Skilled_Nursing_Visit_Note.Date_Signed, dbo.Skilled_Nursing_Visit_Note.Nurse_Name_Stamp_SNV, 
                      dbo.Skilled_Nursing_Visit_Note.Nurse_User_ID_num_SNV, dbo.SNV_Printed_History.ReviewedBy, dbo.SNV_Printed_History.ReviewedDate, dbo.SNV_Printed_History.PrintedDate, 
                      dbo.SNV_Printed_History.PrintedBy, dbo.Skilled_Nursing_Visit_Note.Client_First_Name, dbo.Skilled_Nursing_Visit_Note.Nurse_Signature_First_Name, 
                      dbo.Skilled_Nursing_Visit_Note.Date_Of_Birth, dbo.Skilled_Nursing_Visit_Note.Shift_From_Minute, dbo.Skilled_Nursing_Visit_Note.Shift_To_Minute, 
                      

Open in new window

0
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
How to convert time (23:05:00) to number? I tried hour but it gave me an error if the time is (24:00:00)
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

Microsoft Access

221K

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.