Questions tagged [ms-access]

Microsoft Access, also known as Microsoft Office Access, is a database management system from Microsoft that combines the relational Microsoft Jet Database Engine with a graphical user interface and software-development tools.

Filter by
Sorted by
Tagged with
1
vote
0answers
49 views

Running an UPDATE SQL QUERY within VBA using DAO

I have a macro in Excel that updates a recordset using WHERE a primary key in MS Access matches a primary key in a UserForm label value. The macro is called from a ...
1
vote
1answer
101 views

A function to validate that an unbound form in Access has all required fields filled out

My goal in this code review is to see if i can figure out how to optimize the Validate function takes up fewer lines of code. i know there must be a way to cut it down so that it doesnt span 300 lines ...
2
votes
3answers
120 views

A function that replicates DLookup, and can use parameterized queries

This function was built to replace DLookup so that i could pass in parameterized queries for a speed increase. I also wanted a simple way to get more than one value if needed. For example, if i want 4 ...
2
votes
0answers
292 views

MS Office Suite VBA: ShellWait() and PowerShell()

I stumbled across this post on SO, and it had a link to some good code; however the code itself was a bit dated. Since the code has a "do not modify" clause I went back to the basics as noted by the ...
6
votes
2answers
119 views

Automated document downloading, with error handling

I've automated downloading of CSVs from our vendors' websites. As expected with anything web-based, things can go... wrong, so I'm implementing some better error handling. The code has been working ...
2
votes
1answer
87 views

Creating Access tables based on a comparison of tables from ArcMaps

This script creates a an Access table that my boss uses for making a report in Access. The data comes from a comparison of data in more than a few tables in Arcmaps. Right now this script works in an ...
4
votes
0answers
595 views

Object-relational mapper for VBA / MS-Access

This is my attempt to create simple object-relational mappers (ORM) in VBA for DAO and ADODB recordsets to reduce boilerplate code when converting data between recordsets and objects. As far as I've ...
1
vote
2answers
68 views

Select Records Constituting Percentage of Total

The Setup I have a table (call it myTable) in MS Access with two fields: Item (Text) and ...
2
votes
0answers
54 views

Programmably Typing the Tables, Views, and Columns Properties of a Database

Programmably Typing a Database gives you the advantages of Intellisense and Compiler Type Checking. Notice that single public DatabaseAlias variable of public Type DatabaseAlias is used. The ...
0
votes
1answer
74 views

Looping in SQL Server to insert row into table at top

Here I have a stored procedure that seems to be taking a little longer to run than I had hoped for. What made this algorithm so lengthy was the fact when adding an element to a new table in SQL it ...
5
votes
1answer
284 views

Creating dialog with Access form instances

With Access forms, creating a true dialog form -- in the sense of actually pausing execution of the calling VBA code -- with only instances (rather than using global default instances) is a ...
2
votes
1answer
325 views

Duplicate Payments using SQL

Working on identifying duplicate vendor payments using SQL in MS Access. The business logic uses four column; Vendor #, Invoice #, Invoice Date and Invoice Amount. There are 5 core reviews, if all are ...
3
votes
1answer
411 views

Update pass-through query from a form (an alternative to stored procedures)

I've adapted a script from the book Microsoft Access Developer's Guide to SQL Server that I use to update a pass-through query via a form. One way to pass parameters to a pass-through query is to ...
0
votes
2answers
2k views

VBA- Retrieving data from an access database by referencing a column in excel

I am not able to run the for loop when number of rows exceed 8-9k. Can you suggest an alternate method for this? can you suggest the syntax to referencing the data using a tablename(or column name) ...
3
votes
4answers
277 views

General function to test for empty/no-value controls

I'm working on a very CRUD-heavy MS Access application, which has over 50 forms for entering and working with data. This, of course, involves lot of testing to see if a textbox, recordset, etc. has a ...
3
votes
1answer
2k views

Generic database select method for within my application

I want to implement a generic Get method for my ASP MVC application, which gets data from an MS Access database. This is what I have so far, where I am testing it ...
6
votes
2answers
148 views

Retrieving database values through properties

I have a Settings table which I make frequent DlookUp calls to retrieve values. So I thought instead of having to write ...
2
votes
1answer
65 views

SQL to list function - turning a table column into a delimited list string

I wrote this very "general use" function, because I needed that in some reporting. I was wondering if there is any suggetsion for improvement. ...
2
votes
1answer
45 views

Convert ISO 8601 week to American-formatted date

Requirements: Implement a function that, given a standard date and an indicator whether or not the output should be in american standard (weeks start on sundays, the first week of the year is ...
3
votes
1answer
75 views

Automatically generate a thunderbird importable contacts file for all employees

This is implemented in Ms Access 2003. The Table "People" is a ODBC linked database table on a MSSQL 2008 server. The file generated can be imported into Mozilla Thunderbird contacts to have a contact ...
2
votes
0answers
462 views

Speed issue when controlling Excel from Access VBA

I have a very hard time figuring out how to proceed. I built an Access DataBase which I use to process data in order to build several weekly and monthly reports. I wrote VBA codes in Excel and in ...
4
votes
1answer
4k views

Resizing the controls on an Access form dynamically

Below is a sub I wrote to dynamically resize all the controls on a form. It utilizes the Tag property of each control. In that ...
3
votes
2answers
1k views

C# Datagridview to Access database

I feel like there would be a better way to this. Essentially, I am transporting the data from an excel file to a DGV, and then from a DGV to an Access database. Should I cut the middle man or is this ...
1
vote
1answer
64 views

Database Dynamic Table Creation

The code below is meant to build an array with certain properties neccesairy to create column for a table in a database. Would there be any more effcient way of doing this? ...
7
votes
3answers
443 views

Update 12 records in a database table in less than 13 queries

I've got a database table in my application called "periods". The structure of the table is: Year (Number) Month (Number) Period (Text) The Year column stores years (Say, 2016), Month stores months (...
1
vote
2answers
54 views

MS Access SQL query for active assigned cases

I've inherited an Access database and am looking to start using it to do reporting. I've written a query that gets me the information I'm looking for, I'm just hoping someone can have a look over it ...
3
votes
2answers
120 views

Updating access records

I spent an hour or so this morning compiling this, and since I have started reading Clean Code and The Pragmatic Programmer I figured I would let you help me get slightly better at this. Due to some ...
2
votes
0answers
974 views

Connecting to an MS Access database (OleDB) with multiple users

I'm writing C# applications that store their data in MS Access databases, (I'd rather use a different type of database but currently have no other option). The applications are in use by multiple ...
9
votes
0answers
1k views

Function to conect to Firebase Rest API via Access VBA

This is the application for small school, which will store students' info in Firebase and also retrieve data from Firebase. I am using JSON Parser to parse the data and save the same in Access tables. ...
1
vote
2answers
1k views

Excel VBA used to fetch Access data

I am using the following VBA to fetch data from an Access database. The query works but there is a lot of information in the database and the response time is very slow, about 30 seconds. Is there ...
5
votes
1answer
367 views

VBA to MS-Access SQL query set(s) and outputs

I am hoping I can get some guidance on some better "best practice" suggestions on how to handle this kind of code set. I understand that parameterized queries are a thing, but I am not quite there yet,...
7
votes
1answer
152 views

Split cells vertically on comma

This takes a field delimited by commas, and splits on the comma. The newly divided information is put in another field (created at the start of the program) called ...
2
votes
1answer
501 views

Update and sum query C# Access DB

I have a simple query using query builder of VS2012 C# which updates the database in my winforms application. The database has 216 entries so far and it's taking a lifetime to update the database. How ...
3
votes
4answers
109 views

A function that implements a query

I have some parameterized queries in Access 2010. They are often used in VBA functions having the same parameters. In such cases, I give the query and function the same name. The following is such a ...
6
votes
1answer
2k views

Retrieving ListBox.column values and store into objects

I know there must be a better way to perform the above sub than using multiple If statements. I was thinking of storing the ...
4
votes
1answer
261 views

Parameterized Query in Access SQL

I am a beginner in Access SQL. I am in need of writing a parameter query for Access SQL and found a few tutorials and links for that but that wouldn't satisfy my requirement. DB: Access DB This ...
3
votes
3answers
315 views

DAO recordset effiency

I have a form that consists of about 60 fields that all update when choosing a different customer from a drop down. I feel like my current way is not the best way as it takes about 30 seconds to ...
4
votes
1answer
341 views

Improving performance of VBA data validation script in Access with large data size

I work in IT compliance and one of my main duties is ensuring the integrity of employee network accounts. Partial VBA script as currently used is shown below: ...
5
votes
1answer
2k views

Speeding up Access query with a calculated field calling a VBA function

At work I have a Microsoft Access database of employees with fields such as employee's manager, employees email, and employee ID. My duties include updating this database and validating entries ...
7
votes
1answer
501 views

MS Access to PostgreSQL converter

I was hoping to get some feedback on the implementation of this class for programmatically converting an access file into a PostgreSQL schema (works by obtaining schema data from the cursor object). <...
7
votes
1answer
2k views

Run arbitrary INSERT INTO SQL Query using parameters

While DoCmd.RunSQL is all well and good for simple code, the number of times I've run into problems with unescaped apostrophes and the like was starting to nark a ...
8
votes
2answers
2k views

Overriding all Access form control AfterUpdate methods

I have a set of custom event handlers for all my textboxes and combo boxes. The reason for this is my users want the bound form (to SQL Server) to immediately push changes to the recordset, rather ...
7
votes
3answers
6k views

Progress Bar Form

Forms in MS Access are really just special glorified classes, so you can create new instances of them from code like this. ...
12
votes
3answers
1k views

Security issues in a SQL query and possible function?

By adding more and more features to my websites, I am now up to 7 different hard coded queries. I have read a lot about SQL injections and possible flaws and security issues, and wanted to make sure ...
12
votes
1answer
5k views

Creating ShortCut (RightClick) Menus in Access

I'm writing a tool to create shortcut menus for an application that I maintain built on the Access runtime. I compile this app into an *.accde file, so much to my chagrin, all right click menus are ...
4
votes
1answer
5k views

MS Access VBA code to compare records in a table and combine data - revised

I've previously posted a version of this code here: MS Access VBA code to compare records in a table and combine data where necessary To repeat what I'm looking for from my previous post: Note I ...
5
votes
2answers
4k views

MS Access VBA code to compare records in a table and combine data where necessary

I need to process some data which is the output from a CAD system, namely a Bill of Materials. I've constructed database and written some VBA code to achieve this. An explanation of what this code ...
1
vote
2answers
1k views

C# to Access SQL query based on UI controls

I have a WPF application (using MVVM pattern) that allows users to perform a search for a record/records based on their ...
8
votes
2answers
843 views

Generating a collection of controls

This question I asked previously mentions a function named BuildControlCollection, which I didn't go into the details of since it wasn't relevant. However, because ...
10
votes
1answer
28k views

Form controls - Single event handler for multiple controls, without sacrifices

Bit of background info first - I've been struggling with a problem for a while now. How could you have one single event handler to handle every control (or all of a particular type of control) on a ...