access & vba

So we use a number of access databases to store transaction data. Recently I got asked to run a query against 36 databases, the query was not in the databases so it had to be written and I just assume that I would do what I always do which is to write the query in access, test it to make sure...

I have access file that was currently using linked table, but I didn't want it to always check connection to linked table at start because some users don't have access to the server, but they need to use. That's why i wanted to create vba script that will connect to sql server, and fetch data to...

Hello, I am trying to create a function that will let me input a number, and the function will do the following: Add the digits in that number togeather Check if the resulting sum is a single digit (1-9), 11, or 22; Return the number if the #2 is true Start Back at #1 with the new number...

Every day I create an Excel spreadsheet for work. I use an Access program which has external links to a couple Excel reports, and a couple *.csv* reports. After I run the Access macro, I opened up an Excel file, Refresh All, and then run a macro in there which is linked to the Access program...

Hi All, I am a novice in MS Access and VBA. I have written this code below to get a cumulative sum when a certain condition is satisfied. Calling this function in a Access query design. But it throws an error saying it contains a syntax error. Could anyone help rectify this? Public Function...

I am exporting data from access to excel. I have a record set that is larger than the space (rows), I'd like to place the first 22 records and then place the remaining records a few columns over. I'm not sure how to limit the first set of data ? array i = 16 With xlWks Do While Not...

Hello. I am trying to automate the following. I need to update Query 1, 2, 3, and 4 with a dealership ID #. So instead of opening up each query and entering the dealership ID #, I was wondering if there is an easier way of automating this. I used to use [Forms]![Form1]![dealerno] but this is not...

For an example if column value is "ABC 123 981" need to extract only 123... like so if its "456_wert" need to extract only 456 using access VBA code. Can somebody please help on this.

I am trying to send an SQL query string from Excel VBA to an access database. I have a standard module for this and it works fine except when I am trying to get this query to run. sSQL = SELECT SV_CgRefset.Name FROM SV_CgRefset WHERE ((SV_CgRefset.Name) = "CKD_15"); In short this gets to the...

Dear Experts, I am creating an excel tool that connects with an MS Access database stored in a remote location. Generally, it takes around 10 seconds ( sometimes faster depending on the internet speed) to run the query and get results back to the excel sheet. Currently, the form and the excel...

Hi, I have a Add New Record form that enters metadata for specific sites. When the data has been entered and the 'open data' button is clicked this will open to a data entry form. This data entry form has the metadata for that site along with 4 pages and subforms (each linked to a different...

Hello All, Quite new to VBA in MS Access, and I've got the following code which checks for open file before executing the remaining code. The issue is, when I run the code without the IsFileOpen portion, everything works as expected. However when I add in the IsFileOpen portion, I get a...

Hello all, I have a .dat file that opens up in an access 97 application. I would like to copy one table from the file and certain columns to a new access database. I was able to copy the whole table in excel however I am stuck on how to do this in access so that way I can skip excel all...

Hi Members, I want to create about 100 invoices each month using a single button. So my sales are being recorded at a day level in excel. At the end of the month, I want to create the invoice for the sales without much effort. Currently, my team copies the data for each client one by one. Is...

Hello All, I'm currently using Office 16 on Windows platform, and am looking to export data from an Excel sheet in one of my workbooks to MS Access. I've got the basic outline down, and am able to send data from Excel to Access, however, I am looking to only send rows based on a cell value...

For some reason my Dim db as DataBase says Compile error User-defined type not defined. Does anybody know why it`s driving me mad Private Sub Combobox_List() Dim rsQry As Recordset Dim Qry As String Dim i As Long Dim db As DataBase Dim dbPath As String Qry =...

This code I have written in Access database I need to filter this table with a Combo box then add the result data to another Comb box in VBA excel How is this possible? Sub ExportData() On Error GoTo ErrorHandler Dim dbTable As String Dim xlWorksheetPath As String xlWorksheetPath =...

I am fumbling through some steps to automate an export from access to a csv file. I have created a macro to run a query when access opens; '------------------------------------------------------------ ' AutoExec ' '------------------------------------------------------------ Function AutoExec()...

Hello everyone. It's nice to be back here. Obviously, I need a big help from this forum, again. I am to develop a project that would allow may co-team mates to request for Vacations Leaves via Excel (using a Data Entry form). These request will go directly to a database in Access. Why Access? I...

hello. i have the following code that is working. the only issue i have is that the template file grows in size and gets too large...as my process loops. i simply want to add code to open, save and close the template file. here is the code...