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

I'm trying to add data to the dictionary in ms access 2016 but the data collected via a SELECT query is not being added , kindly help me to spot the mistake on the code below. To be sure I want to print the data using a message, only then can I take the data to its final destination:

Private Sub CmdSales_Click()
    Dim jsonitems As New Collection
    Dim jsonDictionery As New Dictionary
    Dim i As Long
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim item As Variant
    Set db = CurrentDb
    Set rs = db.OpenRecordset("Qry1")
    i = 2
    For Each item In jsonitems
        
        With rs
            
            ![INV] = item("INV")
            ![Customer] = item("Customer")
            ![TaxId] = item("TaxId")
            ![Address] = item("Address")
            ![InvoiceDate] = item("InvoiceDate")
            ![Product] = item("Product")
            ![Qty] = item("Qty")
            ![Price] = item("Price")
            ![VAT] = item("VAT")
            ![TotalPrice] = item("TotalPrice")
            End With
            jsonitems.Add jsonDictionery
            Set jsonDictionery = Nothing
            i = i + 1
    Next
    rs.Close
    Set rs = Nothing
    Set jsonitems = Nothing
    MsgBox JsonConverter.ConvertToJson(jsonitems, Whitespace:=3)
End Sub

Open in new window

0
I create an excel table with checkbox with no caption using vba. This works fine but if the same table is create a second time, the checkbox has alternative names which I cannot delete. This is the code I use:

Function AddCheckBox(objExcel As Object, lTab, lRow, lCol, lCaption As Variant)
Dim lColChar, lName As Variant

lColChar = AssignChar(lCol)
objExcel.Worksheets(lTab).CheckBoxes.Add(Left:=Range(lColChar & lRow).Left, Top:=Range(lColChar & lRow).Top, Width:=Range(lColChar & lRow).Width, Height:=Range(lColChar & lRow).Height).Select
With Selection
  .Caption = lCaption
End With
End Function

I have also tried the following but in this case I get an error on the second time I try to create the excel table (first time works fine).

Function AddCheckBox(objExcel As Object, lTab, lRow, lCol, lCaption As Variant)
Dim lColChar, lName As Variant
Dim s As Shape

'On Error Resume Next
lColChar = AssignChar(lCol)
lName = RandomString(10)
objExcel.Worksheets(lTab).CheckBoxes.Add(Left:=Range(lColChar & lRow).Left, Top:=Range(lColChar & lRow).Top, Width:=Range(lColChar & lRow).Width, Height:=Range(lColChar & lRow).Height).Select
With Selection
  .Caption = lCaption
  .Name = lName
  objExcel.Worksheets(lTab).Shapes(lName).AlternativeText = ""
End With
End Function

Thank you so much for any help. Regards Michael
0
I have a form called PowerlineF.  It's tied to a MS SQL backend table called PowerlineT.  On this form I have a button "btnDuplicateProject".  The goal is to duplicate the current record when a project needs to be split in half due to a decision to build it in different phases with different construction timelines.  The code I planned to use to duplicate is:

    

Private Sub Duplicate Project_Click()

On Error GoTo err_Error_handler
    
If Me.Dirty = True Then
    Me.Dirty = False
End If

With DoCmd
        .RunCommand acCmdSelectRecord
        .RunCommand acCmdCopy
        .RunCommand acCmdRecordsGoToNew
        .RunCommand acCmdSelectRecord
        .RunCommand acCmdPaste
    End With

exit_Error_handler:
 On Error Resume Next
 Set objOutlook = Nothing
 Set objMailItem = Nothing
 Exit Sub
 
err_Error_handler:
 Select Case Err.Number
  Case 287
   MsgBox "Canceled by user.", vbInformation
  Case Else
   MsgBox "Error " & Err.Number & " " & Err.Description
 End Select

Resume exit_Error_handler

End Sub

Open in new window


I have some unique indexes preventing the duplicated record from saving.  My unique column is "ProjectName".  How can we prompt the user to provide a new name for the ProjectName field when pasting the duplicate record?  

Also, if original project was named "Silvertip Powerline" and we wanted to split that project in half, we would need to change the original ProjectName to "Silvertip Powerline (Phase-1)" and the new duplicated project as "Silvertip Powerline (Phase-2)" at the time of duplication?  I'm thinking we'd deploy a message box to facilitate the name change by asking the what the ProjectName should be changed to on the original, then followed by another message box prompting user to provide a ProjectName for the duplicate/new.  Problem is, I don't know how to implement these things within the code.

Thanks!
0
what kind of formulas determine how long you should retain your SQL Server database backups for? Our admins seem to be using a variety of different retention schedules for backups of different critical databases, some databases there are 1 weeks worth of SQL backups, others there are 6 weeks worth. I am trying to determine how exactly you would determine how long to keep database backups for, and for example have you ever restored data from say a 6 week old backup, which would surely incur huge data loss? Or under what circumstances would you need to keep backups for longer than say 7 days? I guess the same principle applies regardless of database platform hence adding it to other DB topic areas.
0
Hi Experts!


Thank you so much for helping me here for too long , I have now done 97%  of REST API and all appear to be working accurately.

Now instead of inserting the ID =  1 or 8 manually in the code, I want to use a  text control otherwise how are the people going to accessing the VBA code in Ms Access runtime?

http.Open "GET", "http://jsonplaceholder.typicode.com/users/?id= 1", False

Open in new window



The above works very well in the VBA code , its just that  it will be difficult to be changing the ID in the code , I want something like below:

http.Open "GET", "http://jsonplaceholder.typicode.com/users/?id = &Me.txtUserID", False

Open in new window


How can it be done to work also like the one below?

http.Open "GET", "http://jsonplaceholder.typicode.com/users/?id= 1", False

Open in new window


I thought this was the easiest, but has proved me wrong again , any idea will be very much appreciated.

Regards

Chris
0
I have a Export button which I wrote code behind and receiving this error after the Export was successful.....Any suggestions?


Private Sub Command562_Click()
On Error GoTo SubError
       
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "BeneficiaryRMD", "C:\Distro\Distro Processing\Exports\Exp_Beneficiary.xlsx"
   
   
    MsgBox "File exported successfully", vbInformation + vbOKOnly, "Export Success"
 
SubExit:
    Exit Sub
   
SubError:
    MsgBox "Error Number:" & Err.Number & ": " & Err.Description, vbCritical + vbOKOnly, "An error occured"
    GoTo SubExit
   
End Sub
0
Hi Experts,
I have a query question about convert from hours to day
attached is my DB, I have two queries, one is "qrySub" this is list all the days and hrs of the time had been taking.  "qrySub1" is group by all the time had been taking.  I'm having trouble to convert  them into days and hrs.
for ex: in qrySub1 that list
            Doe, Jane   25.5 hr(s) CT
                                9 hrs SL
                                11HRS VL
                               9 SL   -- this is days
                               7 VL  -- this is days
what I want to do is covert to : (8 hrs work in a day)
            Doe, Jane   3 days 1.5 hrs CT  
                               10 days 1 hr SL
                                8 days 3 hrs VL
Is possible to convert to like this? or any better suggestion

Thanks,
Time.mdb
0
I need to capture all text to the right of the last space and everything (including spaces) to the left of the last space.  I have been all over the Mid, Right and Left functions. All messed up here!!!
0
I can no longer import Excel files into Access 2003 nor can I export tables out of Access 2003 even though I've been doing the exact same thing for years.

Ideas anyone?
0
Dear Experts
Thank you for the valuable help you have rendered to me from inception to now, I’m saying thank you to you all.

Now I still have one more issue which is now 80% done but still require your help. I have done a Json code integration with the help of this site , all I need for now is to include a parameter on the API so that only the specific data can be pulled using “GET”. The VBA code which is working is below and the Json data it is pulling.

Private Sub CmdEmp_Click()
Dim http As Object
    Dim JSON As Object
    Dim i As Integer
    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    Set http = CreateObject("MSXML2.XMLHTTP")
    Set db = CurrentDb
    Set rs = db.OpenRecordset("Contact")
    http.Open "GET", "http://jsonplaceholder.typicode.com/users", False
    http.send
    Set JSON = ParseJson(http.responseText)
    i = 2
    For Each Item In JSON
        With rs
            .AddNew
            ![ID] = Item("id")
            ![FirstName] = Item("name")
            ![UserName] = Item("username")
            ![Email] = Item("email")
            ![City] = Item("address")("city")
            ![Phone] = Item("phone")
            ![WebSite] = Item("website")
            ![Company] = Item("company")("name")
            .Update
        End With
        i = i + 1
    Next
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Set JSON = Nothing
    Set http = Nothing

    MsgBox ("complete")
End Sub 

Open in new window


Below is the data I’m populating in Ms Access table, for example instead of getting all the users, I want to populate only the selected user ID (txtUserID) from a form. Please note the above code is on onclick event behind the form called frmEmployees which also have the control called (txtUserID).
Here people are required select the user Id and click the command button to populate the exactly required data. Below is the Json data which is required to be populated.


Open in new window

0
I need to update a date field in ms access with a date\time data however when I try to update the field, it changes the field to a short text field with the data
0
After working so hard trying the Json stuff I have managed to make it work in Excel without problems, now I want help to make the same to work Ms Access.
I have created a table called CONTACT with the following controls:
•      ID
•      FirstName
•      Username
•      Email
•      Address
•      City
•      Phone
•      Website
•      Company Name
Below is the code that is working very well in excel:
Public Sub exceljson()
Dim http As Object, JSON As Object, i As Integer
Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "GET", "http://jsonplaceholder.typicode.com/users", False
http.Send
Set JSON = ParseJson(http.responseText)
i = 2
For Each Item In JSON
Sheets(1).Cells(i, 1).Value = Item("id")
Sheets(1).Cells(i, 2).Value = Item("name")
Sheets(1).Cells(i, 3).Value = Item("username")
Sheets(1).Cells(i, 4).Value = Item("email")
Sheets(1).Cells(i, 5).Value = Item("address")("city")
Sheets(1).Cells(i, 6).Value = Item("phone")
Sheets(1).Cells(i, 7).Value = Item("website")
Sheets(1).Cells(i, 8).Value = Item("company")("name")
i = i + 1
Next
MsgBox ("complete")
End Sub

Open in new window


The help I want is to create a query inside the above code so that when I click the button on the form the code can now send the data to the table called CONTACT.
0
I have an extract of data in an access database which essentially represents an access control system, and one of the fields if formatted in date/time, i.e. 03/06/2019 08:27:02 - these represent the times an employee swiped into and out of one of our buildings. What I need is a way of getting a unique list of days where there are no entries whatsoever in the data at all in between a date range of 01/06/2019-31/07/2019 only. So for example if there were entries in the date for 04/06/2019 then that wont be returned in the results as there are entries on that day, but if there are no entries at all for 05/06/2019 then I need that to be returned in my results along with any other unique dates that have no records whatsoever in the table. Can you think of a way of achieving this with a query? I don't want anymore though than the 'missing days' between the range specified if at all possible.

n/b at present its in an access table but we could just as easy export to excel and use that if it will be easier to get the desired result.
0
A subform field that should not accept alpha characters.

I could do this the simple way and change the datatype of a field to numeric 19,0, but i cant right now.

So i have a field on a subform that i'm trying in every way possible so that anytime an alpha character is entered it will not accept it.
For the most part these work until:
A COPY AND PASTE SITUATION OCCURS.

this below is the code i have thus far,


Private Sub APPROVED_GP_AfterUpdate()
Dim var As Variant
   On Error GoTo APPROVED_GP_AfterUpdate_Error

If Len(Nz(Me.APPROVED_GP, "")) = 0 Then
   Exit Sub
End If


' checking for decimals
On Error Resume Next
var = Split([APPROVED_GP], ".", , vbTextCompare)

If Err.Number <> 0 Then
  '  Debug.Print "check"
Else
    counthypens = UBound(var)
    
    If counthypens > 1 Then
      MsgBox "You have entered too many decimals. Please Re-Enter", vbCritical, "Decimal Check"
      Me.APPROVED_GP.SetFocus
      Exit Sub
    End If
End If



Me!APPROVED_GP = Replace([APPROVED_GP], "%", "")
Me!APPROVED_GP = Replace([APPROVED_GP], "..", ".")


'Me!APPROVED_GP = Me.APPROVED_GP.Value / 100 * 100 & "%"

If IsNumeric(Me.APPROVED_GP) = True Then
    Me.APPROVED_GP.Value = Format(Me.APPROVED_GP.Value / 100, "0.00%")
    Else
    MsgBox "You have entered a Alpha character. Please Re-Enter", vbCritical, "Alpha Check"
    Exit Sub
End If

If Me!APPROVED_GP = "%" Then
Me!APPROVED_GP = ""
End If

   On Error GoTo 0
   Exit Sub

APPROVED_GP_AfterUpdate_Error:

    MsgBox 

Open in new window

0
I'm trying to create a shortcut automatically using vbs script but it refuses to run all the way through  because it throws a char: 2 error - object doesn't support this property or method which happens on this particular line item:

WshShell = CreateObject("Wscript.shell")

Please note attachment that shows error.

I took this code from this website but it hasn't worked out for me.

https://support.microsoft.com/en-us/help/244677/how-to-create-a-desktop-shortcut-with-the-windows-script-host

Public Sub CreateIconToDesktop()

 Dim WshShell
 Dim strDesktop
 Dim oMyShortCut
 WshShell = CreateObject("Wscript.shell")
 strDesktop = WshShell.SpecialFolders("Desktop")
 oMyShortcut = WshShell.CreateShortcut(strDesktop + "\Sample.lnk")
 'oMyShortcut.WindowStyle = 3  &&Maximized 7=Minimized  4=Normal 
 oMyShortcut.IconLocation = "Q:\Dialer\_dbadmin\icons\logocampaign.ico"
 oMyShortcut.TargetPath = "Q:\Dialer\dialercampaigns.accdb"
 'oMyShortCut.Hotkey = "ALT+CTRL+F"
 oMyShortCut.Save

End Sub

Open in new window

0
Is there a single command that will change blank fields to zero in a spread sheet?

50 columns 50 rows but 20 number columns some with random blank fields. I am importing to MS access. It doesn't like blank fields
0
Need correct syntax or code to format a number using vbs.

Please note the following code which grabs your default MS Access location by formatting the numeric version using the command Function:

The line that contains the error:  Type mismatch: 'Format'

strLnKey = "HKEY_CURRENT_USER\Software\Microsoft\Office\" & Format(sAppVersion & "", "##,##0.0") & "\Access\Security\Trusted Locations\Location"

Please note attached file that shows the listed error.

Basically just want to know how I can either created my own Format function in vbs to do the above or what should be the correct command?
I tried FormatNumber instead of Format but that didn't work.  I tried convert string to integer and then FormatNumber(  … but that didn't work.


    Set oRegistry = GetObject("winmgmts:{impersonationLevel=impersonate}//./root/default:StdRegProv")
    sKey = "Access.Application\CurVer"
    oRegistry.GetStringValue HKEY_CLASSES_ROOT, sKey, "", sValue
    sAppVersion = Right(sValue, Len(sValue) - InStrRev(sValue, "."))
    Set oRegistry = Nothing


    'Specify the registry trusted locations path for the version of Access used
    strLnKey = "HKEY_CURRENT_USER\Software\Microsoft\Office\" & Format(sAppVersion & "", "##,##0.0") & "\Access\Security\Trusted Locations\Location"

Open in new window

ee-vbs-error.PNG
0
Kindly help me to have the listed code below work in Ms Access VBA , I have already imported the JsonBas converter from GTuB site.

Private Sub CmdJson_Click()
Set MyRequest = CreateObject("winhttp.winhttprequest.5.1")
MyRequest.Open "GET", "https://jira.atlassian.com/rest/api/latest/issue/JRA-9"
MyRequest.Send
Dim Json As Object
Set Json = JsonConverter.ParseJson(MyRequest.ResponseText)
MsgBox Json("Key")
End Sub

Open in new window



On My request it say user not defined

I want to have JRA-9 showing after clicking the above button, this part of my training if this works , then the next stage is prepare the Json Spec , I may also ask how to go about it, I'm not worried about opening the record set that used to feed the Json.


JsonCorrection.png

Regards

Chris
0
Hi Experts,

I have the following function that processes files (downloaded from an FTP server thru a PS Script every 15 minutes), and then keeps a log of those processed files.

Public Sub CallImportDataToCaspio()
   Dim StrFile As String, strTable As String, sFileStr As String
   Dim sDir As String
   Dim l As Long, s As String, i As Long
   Dim db As Database
   Set db = CurrentDb
   sDir = "E:\AppDev\FTP\Caspio\"
   'sDir = "E:\AppDev\FTP\Caspio\"
    'StrFile = Dir(sDir & "*PatChanges*")
    StrFile = Dir(sDir & "*" & sFileStr & "*")
    Do While Len(StrFile) > 0
    
        If Not IsFileOpen(sDir & StrFile) Then
            ''        Do While IsFileOpen(sDir & StrFile)
            ''            ' do nothing
            ''        Loop
            If InStr(1, StrFile, "Full") = 0 And InStr(1, StrFile, "Part") = 0 Then
                i = CountOfRecords(sDir, StrFile)
                If i > 1 Then
                    'Debug.Print StrFile & " - " & CountOfRecords(sDir, StrFile)
                    If InStr(1, StrFile, "PatChanges") > 0 Then
                        strTable = "Patients"
                        l = ImportDataToCaspio(strTable, sDir & StrFile, i)
                    ElseIf InStr(1, StrFile, "SchChanges") > 0 Then
                        strTable = "Schedule"
                        l = ImportDataToCaspio(strTable, sDir & 

Open in new window

0
Objective:      Open the Informix Demo (dbaccessdemo) database using Microsoft Access
Problem:      I have done the basic install & configuration, but I am not able to “connect”
Setup:            My Informix Server is a Windows 10 Pro, Intel i5-4570, 16 Gb RAM, 128 Gb SSD
            My MS Access Workstation is a Windows 10 Pro, Intel i7-4770, 16 Gb RAM, 256Gb SSD
            They are connected on the same Workgroup (I do not have a domain)
Questions:
1).      Do I need to create a Rule for Windows Firewall?
Is Port 9088 the default for Informix?
Inbound, Outbound, or both?
On the Server, Workstation, or both?
2).      Which Client SDK should I use?
ClientSDK4.10TCDE.Win (32-bit) or Client SDK4.50FC1.Win (64-bit)?
The IBM Knowledge Center says …”To configure a DSN on the Windows 64-bit platform, you must use the 32-bit ODBC Data Source Administrator” (https://www.ibm.com/support/knowledgecenter/SSGU8G_14.1.0/com.ibm.odbc.doc/ids_odbc_069.htm)
3).      Depending on the Client SDK, which Informix can I install?
    IBM.ids.1210TC13de (32-bit) or IBM.ids.14.10.FC1.Win (64-bit)?
    (if I use the 32-bit SDK, can I still use the 64-bit Database?)
4).      Do I need to create an Informix User Group in Windows, and what permissions do I need to give them?
5).      What ODBC Connections do I need to make?
On the (Informix) Server, (MS Access) Workstation, from MS Access, or all?
(e.g. Control Panel -> System and Security -> Administrative Tools -> ODBC Data Sources (?-bit)
0
Hello the experts :)

I'm just starting with Access where I simply want to file and sort ALL my administrative stuff, from the classic contacts to my files organization, my various matrix categories, everything.

Simple question : should I put EVERYTHING in the same database, or build like 3-4 (contacts, files management, todo/projects management, purchasing, for example)

Thank you very much & kind regards,

Magpie
0
I have an Access table with multiple fields.  There is a StateField and there is an AmountField.   I need a query that will sum the AmountField for each state.   Similar to using subtotal in Excel.
0
I have an Access database with a MyTable that has 10 fields.    In Field1, there can be duplicate values.

I need a query that will return all records where a value in Field1 occurs more than once. In those instances, I don't want just Field1 returned; I want the entire record (all 10 fields returned).

Thank you
0
I have several large calculated fields in an Access query - in design view.  Each time I want to review the formula, I have to double click to resize the column width.  Is there a way to set the column width to be the width of the formula and/or table name, whichever is longer, so I don't have to double click every field to review the formula?  

It's Access 2013.
0
I have an Access database.  Mytable has multiple fields.   The values in Field1 may or may not be duplicated or triplicated.

I need query that will extract all records from MyTable where the value in Field1 occurs 2 times and another one where the value in Field1 occurs 3 times.
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.