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.
68
questions
2
votes
1
answer
102
views
One way Synchronize 2 Access Tables with VBA ADODB
I am synchronizing two MS-Access databases.
One of them on a network drive, the other one on the local machine.
(For testing purposes both in the same directory)
The Databases both contain a table ...
- 21
1
vote
0
answers
50
views
Inserting Rows into Access Table Using Excel Macro
I have a macro that inserts data from Excel into an Access table. Each time it runs there may be 10k -250k rows to insert. I have experimented with using an empty database/table but it still takes 60s+...
- 123
2
votes
2
answers
183
views
Sub that calls itself when its parent Workbook is closed
and no, I'm not talking about Workbook_BeforeClose. Note that the motivation of this question is Excel specific but the technique I'd like feedback on is not.
If ...
- 165
0
votes
2
answers
69
views
VBA code which exports data from Access to Excel and then loops through the Excel file
I have a couple of VBA loops that work in the blink of eye when I execute them through Excel, but doing this as part of an Access VBA application takes like 15 minutes. The loops run through each row ...
4
votes
2
answers
513
views
Recursive function designed to ensure that full folder path exists before copying a file to the location
My recursive function works, but I feel it could be improved... suggestions? The code ensures that a given folder path exists by checking its validity or creating them if they don't already exist, so ...
- 181
2
votes
0
answers
336
views
Command line framework for VBA compiler app, written in twinBASIC :o
Ok compiler is a bit of a stretch...
I'm working on a package manager for VBA code, written primarily in python. One fiddly step of the pipeline involves "compiling" packages; this is the ...
- 2,345
2
votes
1
answer
31
views
MS Access query complexity on finding duplicates
I have a list of names and ID's, with many multiplication (it's a payroll list). I'd like to see those ID's and names where the name changed somewhen.
Input:
id1 name
1 Josephine Baker
2 Norma ...
- 243
2
votes
1
answer
133
views
How to speed up a treeview population in Access?
I have the following function that causes a significant visual jittering and lag in my access database - This is a treeview selection based on data in a table (tblDATA) with the fields:
Object | ...
- 161
7
votes
1
answer
318
views
Add Default Rubberduck VBA Folder Annotation to VBProject.VBComponents
Currently Rubberduck VBA files all the VBProject VBComponents in that do not have a Folder Annotation in a Folder named after the VBProject. It can be time consuming to manually organize the ...
- 4,133
2
votes
2
answers
275
views
Auto formatting and auto validating TextBox VBA
So, I created a class to avoid all the work behind the validation of input in a textbox.
The idea is to pass an existing textbox and a type of the desired content and the class will take care of block ...
- 354
4
votes
0
answers
70
views
Edit Excel through MS Access
I have an Access macro that runs AfterUpdate of a form. It opens an Excel workbook and edits a few cells. I have It working the way I want, but the problem is that ...
4
votes
1
answer
125
views
Slow performing subquery
This query executes very slowly and I wonder if it can be improved?
We have an Access database split to front end / back end with around 50 users. BE is in a network folder and FE is on users' hard ...
- 186
1
vote
0
answers
684
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 ...
- 41
1
vote
1
answer
342
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 ...
- 163
2
votes
3
answers
185
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 ...
- 163
3
votes
0
answers
1k
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 ...
- 141
6
votes
2
answers
152
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 ...
- 1,290
2
votes
1
answer
116
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 ...
user106363
5
votes
0
answers
2k
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 ...
- 51
1
vote
2
answers
90
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 ...
- 113
2
votes
0
answers
99
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 ...
user109261
0
votes
1
answer
90
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 ...
- 1
7
votes
1
answer
538
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,019
2
votes
1
answer
802
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 ...
- 21
3
votes
1
answer
2k
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 ...
- 198
0
votes
2
answers
3k
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) ...
4
votes
4
answers
2k
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 ...
- 141
3
votes
1
answer
4k
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 ...
- 559
6
votes
2
answers
184
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 ...
- 215
2
votes
1
answer
81
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.
...
- 340
2
votes
1
answer
74
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 the ...
- 330
3
votes
1
answer
105
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 ...
- 330
2
votes
0
answers
723
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 ...
5
votes
1
answer
8k
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 ...
- 171
3
votes
2
answers
2k
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 ...
- 291
1
vote
1
answer
98
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?
...
- 13
7
votes
3
answers
458
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 (...
- 173
1
vote
2
answers
67
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 ...
- 113
3
votes
2
answers
130
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 ...
- 315
2
votes
0
answers
1k
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 ...
- 21
11
votes
0
answers
2k
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.
...
- 249
1
vote
2
answers
2k
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 ...
- 27
5
votes
1
answer
499
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,...
- 315
7
votes
1
answer
168
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 ...
- 173
3
votes
1
answer
646
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 ...
- 31
3
votes
4
answers
132
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 ...
- 149
6
votes
1
answer
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 ...
- 63
4
votes
1
answer
324
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
3
answers
360
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 ...
- 183
4
votes
1
answer
364
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:
...
- 41