Microsoft Access

217K

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

Hi Experts,

I have an event that loads data from an Excel File into a Table.  The process works perfectly with the data from the first column of the Excel File written into the first column of the Table.

I would like to change this to load the first column of the Excel File into Column 10 of the Table.

Is there a way to do this?  i.e. Specify the Starting Column for the data to be inserted in the Table?

I am attaching the section of VBA code that currently does this.

Thanks,
Bob C.
Import-Excel-Data-To-Access-Table.txt
0
Veeam Task Manager for Hyper-V
LVL 1
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

I'm working on a Delphi program which I'd like to link to a online database which should be accessible on any computer with the Delphi program running.

is it possible to link, and where can the database be hosted ,also how should the code look ?
0
I've created a query with Start Date and End Date parameter so that when someone enters the start date and end date it will extract data in between those dates. Now the extracted data has  2 columns one for reason and the second column for downtime.  I would like to add downtime for same reasons instead of having many rows. I tried using Group By  SUM in the query but is showing an error as a complicated expression; simplify it. Can someone help me with this?
0
We have a database that gets daily use by ~100 employees and has worked flawlessly for years.  6 months ago I deployed 13 identical new computers with Windows 10 Pro and Office 365 (effectively Office 2016).  Most of the rest of the office runs Windows 7, with a mix of Office 2010 and Office 2016.  Everything worked great.

In the past 2 weeks, 2 of the 13 new builds have encountered the following error:

"The expression On Click you entered as the event property setting produced the following error: The OpenForm action was cancelled.
*The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure].
*There may have been an error evaluation the function, event, or macro."

The .mde program opens normally, and prompts me to login.  Upon trying to login, the error message is generated.  When it occurred on the first machine, I'm not sure how I eventually resolved it.  I tried many things repeatedly, and suddenly it worked.

The second machine failed today, and I could not resolve it.  I even formatted and reinstalled a clean copy of Windows 10, installed Office 365, and tried again.  No change.

There is currently no antivirus running.  This machine is on a domain, and the mdb file is on a network drive.  Any thoughts on what is going on here?
0
Hi -

I am using a form that is based on a query and not in a Table. It is a relatively complex query as I need  to pick up some of the Table columns and calculate values from them, and the new values are shown on the form. But I do also need to correct, using the form, new values.
However the query is yielding the above error.
My query  is constructed from 3 tables as below:

Screen-Shot-2017-08-18-at-22.39.21.png
can anyone help get me a solution?

joao
0
I have an access 2010 application distributed via the packaging wizard. It works well except that shortcut menus are nos available in forms.
For example, there is no way to copy or paste with shortcut menus.
In the source file they are available.
What should I do to make them available in the distributed package?
Thanks in advance.
0
I am getting an intermittent error when trying to open Excel using MS Access 2010 vba.  The relevant part of my code is shown below.
The error I am getting is:  Automation Error  The object invoked has disconnected from it's clients
As you can see from the code below I have an error routine which will allow the program to either use an instance of Excel that is already open or open a new one.  But, I get the error on the line:  Set xlObj = GetObject(, "excel.application").  It doesn't make it to the error handling routine.
I've used this method of opening excel for a long time but this error just started showing up recently and, as I mentioned, it's intermittent.  Sometime is works, sometimes it doesn't, on the same computer.
Any ideas on what is causing this and what I can do to avoid it?


Private Sub FormatTheSpreadsheet(spreadsheetname)
On Error GoTo Err_FormatTheSpreadsheet

Dim xlObj As Object

Set xlObj = GetObject(, "excel.application")

With xlObj
     ' Code for formatting spreadsheet
End With

Exit_FormatTheSpreadsheet:
    Exit Sub
Err_FormatTheSpreadsheet:
    If Err.Number = 429 Then
        Set xlObj = CreateObject("excel.application")
        Resume Next
    Else
        MsgBox Err.Description
        Resume Exit_FormatTheSpreadsheet
    End If
End Sub
0
I do not know how to use SQL or VBA.  (I used to write scripts in Borland dBase a long time ago, but I've atrophied since then.)   I can build somewhat complex databases, queries, and reports using tables in Access.  I frequently use Excel data tables as Access data sources.  And I can build basic Excel spreadsheets using many of the built-in functions.  I currently have a large database I need to analyze.  Each line in my Access table has one set of parameters (3 to 5 numbers) that needs analysis (return one number).  The analysis for that one set of parameters is too difficult to perform in Access using a long series of queries, and I doubt I could construct it.  Although it will take some time to build the spreadsheet in Excel, it should be straight forward to do the calculations and make the final decision in Excel on a single set of parameters.  However, there is no way I could construct that on a single Excel line, and then repeat that line down hundreds of thousands of lines in Excel.

How can I pass one set of parameters, say 5 numbers, to 5 specific cells in an Excel spreadsheet, and read  the result cell back into Access?  Running the Access query will need to complete the same analysis on every line in the Access data table, and fill in the results column in the same Access parameters table or a different Access table linked to the original parameters table.  Just to be clear, I'm not asking how to use a single Excel internal function, such as Present Value, in …
0
I have an access 2017 database that is linked to SharePoint List.  I have a form that list individual records within the database.  Normal you can either page down, click in the scroll bar or click on the block in the scroll bar and drag it down.  However, now when you click on the block and drag it down it does nothing.   No records move.  If you click on the scroll bar itself it resets back to the previous point.  I have attached a pdf file for those who need more of a visual like me.  Your help is appreciated.  This function has previously worked.
Doc9.pdf
0
I am writing a ms/access report and, on the detail line, as soon as I am changing for the next record, I need to prepare data such as making a few calculations and load several external images. For this I though to use the "On Current" event, but it does not fire... Which event fires when I am moving to the next record in the detail section?

can anyone help?
joao
0
Free Tool: SSL Checker
LVL 9
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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 am not sure which version this started with, or if it is even related to the office version, but...  The scrollwheel moves the scrollbar, but the user cannot see the form scroll.  The record count in the popup changes, but no movement.  The same is the case when dragging the scrollbar.  However, the arrows at the top and bottom of the scrollbar still work.  This is an app that has been in use for many years.  There have been no dev changes.  I have only seen one unanswered posted in google about this.

Thanks
0
Periodically I need to import a file into the main database from other co-workers. Due to network not being available for them (it's a long story) our work around is for them to export their data base as excel file and then they email it to me. I, in turn, import their excel sheet into the main database that runs all the numbers. The data structure of the tables is the same as is in the main, so on their desktop copy. The problem has come up, that I accidentally imported excel file from another location into the wrong table on the main database and as a result, that data was erased and new not imported because columns didn't match.

What I need to do is insert into the excel spreadsheet some kind of wording or code, that when I import their excel file, it checks first to make sure the wording/code matches the destination table wording/code, then import process begins otherwise it gives me an error message.

Below is my importing code.

Private Sub cmdBrowse_Click()
'call funtion to open dialog and look for the file
Selectfile

End Sub

Public Function Selectfile() As String
       ' Prompts user for back-end database file name.
       On Error GoTo Err_Browse
       Dim Fd As FileDialog
       Set Fd = Application.FileDialog(msoFileDialogOpen)
       
       With Fd   ' Ask for new file location.
         .AllowMultiSelect = False
         .Title = "Please Select Excel File"
         ' If user responded, put selection into text box on form.
         If .Show = 

Open in new window

0
I am typing a series of tables with 122 questions. The numbers on each table are in sequence from 1 to 122. My table from 30 to 41 is the only one that is out of sequence. How do I restart the sequence of numbers there?
0
I have the following statement that I am testing which is working with no issues.

Dim Category As Integer
Dim Service As Integer

If Me.Category = "Executive" Then
 Me.TestPTO = 200
ElseIf Me.Category = "Manager" Then
 If Me.Service < 10 Then Me.TestPTO = 160 Else Me.TestPTO = 200
ElseIf Me.Category = "General-Professional" Then
 If Me.Service < 3 Then
 Me.TestPTO = 120
 ElseIf Me.Service < 5 Then
 Me.TestPTO = 144
 ElseIf Me.Service < 10 Then
 Me.TestPTO = 160
 ElseIf Me.Service >= 10 Then
 Me.TestPTO = 200
 End If
End If

Here is my issue that I need to account for. Its really two scenarios. We have a field called InactiveDate. This field is filled in with a date when an employee leaves the company (obviously the field is null for active employees). The first thing I need to happen is should that employee leave the company and once that date has been mark in the inactivedate field, what ever time was accrued in the TestPTO field up to that point stays frozen or in other words no more pto time gets calculated. The last issue is we have temp employees and they do not factor into PTO so we have a field (for all of our employees) called EmployeeNumber. All of our employeenumbers for temps begin with 9999 so I'm guessing I require something like if EmployeeNumber begins with 9999 then TestPTO = 0.
0
I have just started to work with WPF using VB.net and have had trouble getting the DataGrid to work with the Access Database (Jet) that holds my data.  I learn best through examples and I would be grateful if someone can give me sample code (both XAML and VB) to solve the following situation.

I have a DataGrid as follows:
            <DataGrid Name="dgPayment" Margin="10,15,10,10" Grid.Column="0" AutoGenerateColumns="False"
                      ItemsSource="{Binding}"
                      RowEditEnding="dgPayment_RowEditEnding"
                      CellEditEnding="dgPayment_CellEditEnding"
                      SelectionChanged="dgPayment_SelectionChanged"
                      CommandManager.PreviewExecuted="dgPayment_PreviewCommand"
                      IsReadOnly="False" CanUserAddRows="True" CanUserDeleteRows="True" >
                <DataGrid.Columns>
                    <DataGridTextColumn Header="Seq" Binding="{Binding Path=CL_Seq}" />
                    <DataGridTextColumn Header="Name" Binding="{Binding Path=CL_Name}" Width="200" />
                    <DataGridTextColumn Header="Hdr1" Binding="{Binding Path=CL_Hdr1}" />
                    <DataGridTextColumn Header="Hdr2" Binding="{Binding Path=CL_Hdr2}" />
                    <DataGridTextColumn Header="Hdr3" Binding="{Binding Path=CL_Hdr3}" />
                </DataGrid.Columns>
            </DataGrid>

I have code behind as follows:
            Using conn = CashConnection
            …
0
I have a main form with three subforms only two are visible

[QryProgramClassSubTotals subform1] This has two textboxes
text15: =Sum([Total Classes])
text26: =Sum([Total Classes])-[Forms]![StudentProgramForm]![QryStudentTotalTrialClasses subform1].[Form]![Total Trial Classes]

In this form:
TblPayments subform

I would like a textbox that gets the actual value of textbox 26 above

in the example attached you can see the value is 37 in that case.

I tried this in text 39 in tblpayments subform but it gives -4 and not 37

=[Forms]![StudentProgramForm]![QryProgramClassSubTotals subform1].[Form]![text26]+[Forms]![StudentProgramForm]![QryProgramClassSubTotals subform1].[Form]![Text15]

Open in new window


example
0
My Tables has the following fields:
ERRC, Stock Nbr, Count of Stock, Qty Requested and Qty of Stock.

My query needs to group by ERRC, count records for each ERRC, Count Unique Stock for Errc, multiple unique stock * Qty request
0
Dear Experts,

Wondering if there is a way to change the font color on a continuous form in MS Access 2010 forms. I can change the alternate background color in properties no problem but would like to change the font color as well on the labels 'cause the font color looks great on one row color but doesn't work on the alternate row color. I would like to keep the BG color the same so wondering if I can change the font color of the labels to better contrast on the alternate color.

Thanks!
0
Hello Experts

I have a collection of SubReports inside a MasterReport. I'm having a small problem where SubReport number 9 (for instance) doesn't show up if there are no records. Instead there is just a blank section in the MasterReport, and then it shows the next SubReport. Its worth noting that SubReport number 9 does show up in Report View and Layout View, but does NOT show up in Print Preview or during an actual Print.

Ideally, we'd like it to display the headers for this SubReport and no records underneath them - instead of just all white space because this MasterReport is structured with numbers in the Titles, etc. (Check Number 1 - Expenses; Check Number 2 - Overdrafts, where 1 is SubReport 1 and 2 is SubReport 2). The numbers are important, and the order is also important.

For what its worth, I am using Office 365 Access.

Thanks for your ideas and thoughts!
-IT_Crowd
0
Ransomware: The New Cyber Threat & How to Stop It
LVL 4
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

I am getting error access denied in a workflow by the system ID
I am getting a SP 2010 workflow to cancel with error access denied by the system ID. Users have correct permission, I am a site collection admin and it does not happen to me but to the users who submit a request by a custom InfoPath form to a list with 2 custom workflows attached. The first workflow is the one that is not being triggered but instead is cancelled. When I manually run the item, the 2 workflows complete.
0
Hello all:

I am opening a recordset and connecting to an excel spreadsheet using Access 2010.  What I am trying to do is read from it  as well as write to it.  I can query the recordsource (a sheet in the workbook via  SELECT * FROM [Sheets1$] ), but can't write to it.  I was trying to write to it by using the following method:

rs.Fields("ColumninSpreadsheet") = "someValue"
and then performing a rs.Update.  This does not error out, however it does not update the column in the excel file.  Here is some of the code I currently have:
    con.Provider = "Microsoft.ACE.OLEDB.12.0" ' "Microsoft.Jet.OLEDB.4.0"
    con.ConnectionString = "Data Source = " & strFileName & ";" & _
    "Extended Properties=Excel 12.0;" ' "Extended Properties = Excel 8.0;"
    con.Open
    
    strSQL = "SELECT * FROM [" & lstSheetNames.value & "$]"
    rs.Open strSQL, con, adOpenStatic, adLockBatchOptimistic
    
    
    If Not (rs.EOF And rs.BOF) Then
        Do Until rs.EOF
            rs.Fields("UserName") = "userName"
            rs.Update
            rs.MoveNext
        Loop
        rs.Close
        Set rs = Nothing
        con.Close
        Set con = Nothing

Open in new window


Can anyone tell me:
1) is it possible to update the spreadsheet this way and if so,
2) how?

Many thanks!

Juan
0
Hello Experts!

I know a long time question has been how to lock or hide or password protect an Access table.  Since I use a table to hold permissions (username and password), I've been searching for a way to prevent someone from creating a blank access database, then importing tables so they can then see users' passwords.  

It looks like this dude found the answer:
https://www.experts-exchange.com/questions/25193938/Locking-down-an-Access-2007-ACCDE-Database-objects-Tables-Queries-Data.html#a26822729

One of Data-Man's responses: "none of my tables show up in the ‘Import Objects’ window even if the user set the option to view system and/or hidden objects"  is exactly what I'm looking for.

Anyone know how he did it?  Encryption/decryption vba code perhaps?  If so, can anyone send me a sample database?

Thanks All!
0
Hi,
I have an EventsName field , and start date , end date and items lists fields.
I have make a crosstab which display for me the EventsName in a columns and give me as a value a sum of the items  in each EventsName.
as i know there is NO way to edit these value in crosstab.
i make a subform and put the crosstab as a source object . (because the columns in the crosstab is changing dynamically)
is there a way i can connect the main form with the subform of the crosstab that can help me give information from the subform and send to the main form which help editing ?
0
Private Sub Save_Request_Click()

'add data to table
Dim sqlStr1  As String
Dim sqlStr2  As String
Dim sqlStr   As String

sqlStr1 = "INSERT INTO report_request_data([Ticket Number]"

sqlStr1 = "INSERT INTO report_request_data([Ticket Number],[Data Source Requested],[Ticket Date],[Due Date]"

'##### the sqlStr2 always complain syntax error. Why. I can't figure out where is wrong. #####

sqlStr2 = "VALUES('&Me.TNumber&'), '"&Me.Source_Req&"', #"&Me.TCreateDate&"#, #"Me.DueDate"#"

' ##### And this statement doesn't pass either. #####
CurrentDb.Execute sqlStr, dbFailOnError  

Debug.Print sqlStr

Clear_Click

End Sub
0
We have 2 Citrix servers and both have Access on them.  The users use accde to open the frontend.  For some reason today on one of the servers we are getting the message "The database is in an unrecognized format"  
I tested the users frontend directly from the database server it is stored on and it opens fine.
I also tested it from the Citrix server and it gets the error.
The accdb open fine, just not the accde

I did a Compile and created a new accde and placed it in the users folder.  I did a repair of Office on the server.

The server is 2016 and the database is 2013 Professional

What could be the cause
0

Microsoft Access

217K

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.