access & vba

  1. Manuel Cavero

    Query to two Access databases

    Hi everyone I'm working in a code that need retrieves data from two Access databases, but unfortunately it doesn't works What the matter? Dim cn as adodb.connection Dim Qr as string Dim rs as adodb.recordset 'Opens the conection set cn = new adodb.connection with cn...
  2. S

    Parse numeric substring, generate accounting key

    Hello, I'm a full time software developer, helping out a charity with their Access (.accdb) database in my spare time. There I need to work with an existing database that connects basic entities from one table with actions to perform on them in another table; everything is properly linked (1...
  3. S

    SQL in VBA help

    HI, I am having issues with my current SQL Statement. my immediate window is throwing the current error: "-2147217900:Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'." I am trying to determine the number of records in the Dates table based on two fields...
  4. N

    Dymanic Form Creation

    I am a novice with MS Access, and I am working on an important assignment. I am stuck on the next steps. I currently have a form, four update queries (all updating one table), and one table (called products and services; the data type of each field is a Yes/No). Using macros, I’ve successful...
  5. T

    Correct dates inserted into incorrect fields in table.

    I have a table that has serial numbers with dates that show when a job was entered, the date it was closed and the date it was installed. The Date of the installation is on the incorrect line and I would like to have it put onto the correct line. How can I do this in Access without going thru...
  6. S

    MS Access Top N Values Show in Table

    Hi, I have a table in Access which contains sales total. I need only Top 2 Values in each column. My input data is below: <colgroup><col span="8"></colgroup><tbody> Prd_Code Prd_Desc Cat_Code Cat_Desc Q1_Sales Q2_Sales Q3_Sales Q4_Sales 111 ABC 11 A 12 12 87 34 222 DEF 22 B 43 56 56 56...
  7. A

    Append Query doesn't Run but doesn't Error

    Hello, i have a VBA code that sends data from excel to one of three access databases. Table1 and Table2 work great. but the data for Table3 is sent to a TempTable. From here, the all the data in TempTable is linked with 5 columns in Table1 in an Append Query. Up until here everything works...
  8. L

    Updating or Adding a Record in Access with Excel VBA

    Hello All, I'm currently using a macro that pushes data from a sheet in Excel to the appropriate column in an Access table (referred to throughout as 'AssignedVol_tbl') at the click of a button. However, the code I'm using will currently only add a new record to the database whereas I want...
  9. M

    VBA to Export Data from Excel to Access

    Dear Experts VBA code required to Export Data from Excel to Access I have a table in excel with 5 columns that I want to export to an access database. The data in excel is per below and all the 5 columns exist with the same heading names in the access database. The table name in the database...
  10. T

    Search an Access database from Excel

    I have a very heavy MS Access database of 2 columns across 4m rows. Importing it to a workbook is not possible considering the number of rows. I am looking to search for values in column 1 from an xls spreadsheet, and retrieve the corresponding value in column 2. A sort of VLOOKUP from excel...
  11. T

    Lock range based on cell value

    Hi, I am trying to come up with process for an online Excel sheet which will lock access to a range if a cell is set to "Yes" Please see below link...
  12. I

    SQL result to msgbox

    Hello everyone, I am trying to display the result of a SQL select query in a message box in access 2016. My initial thinking was that I need to use Openrecordset, but for some reason I can't manage to get it to work. This is my code: sqlstring = "Select [Current Period] from tblCHinfo"...
  13. A

    VB Access DAO converting to SQLDB

    Hello guys, My company has a huge VB, wich contains of 15000 lines. Now this is connected to a access db, but because it needs to connect to a Maximo environment, it need to change to a sql database. What are the easy ways or hard ways for me to change this. Or is there even an option to...
  14. J

    Updating Access Database through Excel Userform - Primary/Foreign Keys

    Hi, I'm relatively new to using Excel to add records to an access database. I have the following code that when the user presses the submit button it adds the record within access database. Set db = OpenDatabase(DatabasePath) 'Open the 'Lab_Submissions' table within the database Set rst =...
  15. S

    Support required to declare ldapserch object in access VBA coding

    Hello Friend, Thank you for your willingness to help! I am new to VBA coding and I've been assigned a task to maintain a pre-developed access vba tool. while running the code is getting stuck at a particular line where the LDAP serch object is declared. (error msg: Runtime error 429, Active...
  16. R

    alternatives to excel for large volume of data

    I have 500,000 rows of data on a sheet that is taking long time to process since there are some formulas and conditional formatting etc. also this data is forecasted to cross over 1 million rows in near future. I know excel does not support anything in excess of million so what are my options...
  17. T

    Export from multiple Access Queries to Multiple Workbooks and Worksheets

    I have a DB that culminates into 4 export queries. I would like to create a macro to export the 4 queries into one workbook per Business Unit. There are 10+ Business Units. A single Business Unit workbook would contain the output of from each of the export queries for the respective Business...
  18. E

    Opening and Modifying Excel with Access VBA

    Hello I am trying to open and modify an Excel 2016 file using VBA in Access 2016. I pieced together the code below, but I keep getting "Compile Error: Sub or Function not defined" at the bolded section of the code below. Does anyone know how to correct this error or if there is a better code to...
  19. C

    Pulling Data from an Excel Spreadsheet to Generate Worksheets?

    Greetings, I'm not sure exactly where to start, but I have an idea of what I'm trying to do. I tried to think of a way to import all these excel files into an Access Database.. but I am not very skilled, I can use the out of the box stuff for Access, but can't seem to get it to do what I'm...
  20. B

    recordset.addnew problem; recordset is read-only

    I have two tables in an Access database, a parent and child. I have a connection, and two ADO recordsets, one each for the parent and child tables. They're both opened with a string of SQL, adOpenDynamic, adLockOptimistic, adCmdText. The logic is to see if something specified in the spreadsheet...

Some videos you may like

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top