Questions tagged [ms-access]
Microsoft Access, also known as Microsoft Office Access, is an application development and database development tool from Microsoft. It combines the Microsoft Jet/ACE Database Engine with a graphical user interface and software-development tools. Other database engines, such as SQL Server, can also be used as a database server for Access applications.
ms-access
57,806
questions
189
votes
9
answers
727k
views
How to do INSERT into a table records extracted from another table
I'm trying to write a query that extracts and transforms data from a table and then insert those data into another table. Yes, this is a data warehousing query and I'm doing it in MS Access. So ...
- 21.3k
176
votes
22
answers
78k
views
How do you use version control with Access development?
I'm involved with updating an Access solution. It has a good amount of VBA, a number of queries, a small amount of tables, and a few forms for data entry & report generation. It's an ideal ...
- 6,561
151
votes
9
answers
1.2m
views
How to SUM two fields within an SQL query
I need to get the total of two fields which are within the same row and input that number in a field at the end of that same row.
This is my code.
Sum(tbl1.fld1 + tbl1.fld2) AS [Total]
Is this ...
- 1,935
126
votes
1
answer
190k
views
Manipulating an Access database from Java without ODBC
I want to manipulate a Microsoft Access database (.accdb or .mdb file) from my Java project. I don't want to use the JDBC-ODBC Bridge and the Access ODBC driver from Microsoft because:
the JDBC-ODBC ...
- 122k
122
votes
6
answers
446k
views
delete all from table
what's faster?
DELETE FROM table_name;
or
DELETE FROM table_name where 1=1;
why?
does truncate table work in access?
- 59.3k
106
votes
3
answers
188k
views
VBA check if object is set
I have a global variable that is an instance of my custom class.
How do I check if the object is set or if I need to initialize it?
- 8,602
105
votes
28
answers
154k
views
Good Free Alternative To MS Access [closed]
Consider the need to develop a lightweight desktop DB application on the Microsoft platforms.
It could be done fairly easily with MS Access but I'd like to be able to distribute it to others and I ...
89
votes
15
answers
171k
views
How to find longest string in the table column data
I've a table contains the columns like
Prefix | CR
----------------------------------------
g | ;#WR_1;#WR_2;#WR_3;#WR_4;#
v | ;#WR_3;#WR_4;#
j | WR_2
m ...
- 1,666
89
votes
4
answers
186k
views
How can I convert an MDB (Access) file to MySQL (or plain SQL file)?
Is it possible to create a Dump of SQL commands from a Microsoft Access database? I hope to convert this MDB file into a MySQL database for importing so I don't have to go through the CSV step.
I ...
- 86.9k
87
votes
16
answers
192k
views
SQL Query - Using Order By in UNION
How can one programmatically sort a union query when pulling data from two tables? For example,
SELECT table1.field1 FROM table1 ORDER BY table1.field1
UNION
SELECT table2.field1 FROM table2 ORDER BY ...
- 4,970
87
votes
7
answers
461k
views
Get length of array?
I'm trying to get the length of an array, yet I keep getting this error:
Object required
Am I doing something wrong?
Dim columns As Variant
columns = Array( _
"A", "ID", _
"D", "Name")
Debug....
- 33.6k
86
votes
3
answers
102k
views
SQL multiple join statement
When I had only one inner join in my SQL statement, it worked perfectly. I tried joining a second table, and now I am getting an error that says there is a syntax error (missing operator). What is ...
- 1,593
85
votes
10
answers
33k
views
Getting Error 3340 Query ' ' is corrupt while executing queries DoCmd.RunSQL
Since installing the windows update for Office 2010 resolving KB 4484127 I get an error while executing queries which contain a WHERE clause.
For example executing this query:
DoCmd.RunSQL "update ...
- 1,758
84
votes
11
answers
152k
views
How to see the real SQL query in Python cursor.execute using pyodbc and MS-Access
I use the following code in Python (with pyodbc for a MS-Access base).
cursor.execute("select a from tbl where b=? and c=?", (x, y))
It's Ok but, for maintenance purposes, I need to know the ...
- 3,362
77
votes
16
answers
160k
views
Determining whether an object is a member of a collection in VBA
How do I determine whether an object is a member of a collection in VBA?
Specifically, I need to find out whether a table definition is a member of the TableDefs collection.
- 3,347
73
votes
2
answers
117k
views
Persist Security Info Property=true and Persist Security Info Property=false
For the properties:
Persist Security Info=true
and
Persist Security Info=false
Can you tell me what is the difference between them, and if I don't put it in my connection what will happen?
...
- 761
68
votes
9
answers
268k
views
Microsoft.ACE.OLEDB.12.0 provider is not registered
I have a Visual Studio 2008 solution with two projects (a Word-Template project and a VB.Net console application for testing). Both projects reference a database project which opens a connection to ...
- 8,290
62
votes
2
answers
199k
views
VBA shorthand for x=x+1?
Sub btn1_Click()
Static value As Integer
value = value + 1
MsgBox value
End Sub
I swear when I was taking a VB.net course in college there was a shorter way to tell a variable to add '' to itself. ...
- 1,104
60
votes
7
answers
126k
views
how to deal with .mdb access files with python
Can someone point me in the right direction on how to open a .mdb file in python? I normally like including some code to start off a discussion, but I don't know where to start. I work with mysql a ...
- 15.7k
58
votes
2
answers
14k
views
How can I get this 8 year old VBA 64-bit compiler bug fixed?
So here's the bug: In a 64-Bit VBA host (for example Access 365 64-bit or Excel 2016 64-bit) create a class module SomeClass:
' this needs to be here to trigger the bug:
Private Sub Class_Terminate()
...
- 28.6k
57
votes
12
answers
260k
views
How to open a folder in Windows Explorer from VBA?
I want to click a button on my access form that opens a folder in Windows Explorer.
Is there any way to do this in VBA?
- 1,634
57
votes
3
answers
207k
views
What is the equivalent of Select Case in Access SQL?
I have a query which includes fields named openingbalance and commissions. I would like to compute values for commissions based on openingbalance, similar to this Select Case block in Access VBA:
...
- 587
57
votes
2
answers
83k
views
Difference between Microsoft.Jet.OleDb and Microsoft.Ace.OleDb
It has been a good number of years since I did some programming with Classic ASP and Microsoft Access where we used "Microsoft.Jet.Oledb" driver to access and display the data.
I have been asked to ...
- 669
57
votes
8
answers
86k
views
How to connect R with Access database in 64-bit Window?
When I tried to connect R with Access database I get an error
odbcConnectAccess is only usable with 32-bit Windows
Does anyone has an idea how to solve this?
library(RODBC)
mdbConnect<-...
- 1,278
56
votes
2
answers
123k
views
Multiple INNER JOIN SQL ACCESS
Syntax Error (missing Operator) in query expression 'tbl_employee.emp_id = tbl_netpay.emp_id INNER JOIN tbl_gross ON tbl_employee.emp_id = tbl_gross.emp_ID INNER JOIN tbl_tax ON tbl_employee.emp_id - ...
- 595
55
votes
8
answers
56k
views
Writing large number of records (bulk insert) to Access in .NET/C#
What is the best way to perform bulk inserts into an MS Access database from .NET? Using ADO.NET, it is taking way over an hour to write out a large dataset.
Note that my original post, before I "...
- 2,643
53
votes
15
answers
337k
views
How to add default signature in Outlook
I am writing a VBA script in Access that creates and auto-populates a few dozen emails. It's been smooth coding so far, but I'm new to Outlook. After creating the mailitem object, how do I add the ...
- 11.7k
52
votes
12
answers
58k
views
SQL: Using NULL values vs. default values
What are the pros and cons of using NULL values in SQL as opposed to default values?
PS. Many similar questions has been asked on here but none answer my question.
- 3,050
50
votes
6
answers
114k
views
Date Difference between consecutive rows
I have a table with following structure
ID Account Number Date
1 1001 10/9/2011 (dd/mm/yyyy)
2 2001 1/9/2011 (dd/mm/yyyy)
3 2001 3/9/...
- 669
49
votes
2
answers
201k
views
How to pass an array to a function in VBA?
I am trying to write a function that accepts an array as an argument. The array can have any number of elements.
Function processArr(Arr() As Variant) As String
Dim N As Variant
dim ...
- 870
48
votes
2
answers
75k
views
Why is "Yes" a value of -1 in MS Access database?
I'm looking at linked data in MS Access.
The "Yes/No" fields contain the value -1 for YES and 0 for NO. Can someone explain why such a counter-intuitive value is used for "Yes"? (Obviously, it should ...
- 2,629
47
votes
3
answers
385k
views
Code to loop through all records in MS Access
I need a code to loop through all the records in a table so I can extract some data. In addition to this, is it also possible to loop through filtered records and, again, extract data? Thanks!
- 613
47
votes
10
answers
103k
views
Is there an equivalent to Thread.Sleep() in VBA
Is there an equivalent to Thread.Sleep() in Access VBA?
- 29.5k
47
votes
12
answers
21k
views
Best way to test a MS Access application?
With the code, forms and data inside the same database I am wondering what are the best practices to design a suite of tests for a Microsoft Access application (say for Access 2007).
One of the main ...
- 16.7k
47
votes
3
answers
47k
views
Why is 1899-12-30 the zero date in Access / SQL Server instead of 12/31?
More out of curiosity than any real problem; the question came up today and I know I've seen 1899-12-30 used as a "default" date and a zero date in Access and older SQL Server apps. Just wondered why ...
- 2,991
46
votes
7
answers
164k
views
Using left join and inner join in the same query
Below is my query using a left join that works as expected. What I want to do is add another table filter this query ever further but having trouble doing so. I will call this new table table_3 and ...
- 1,144
46
votes
4
answers
291k
views
How to create a new column in a select query
In MS Access, I want to insert a new column into the returned result of a select query. The new column has the same value for every row. For example, my select returns columns A, B and I want C to be ...
- 21.3k
46
votes
17
answers
70k
views
Editing Record issues in Access / SQL (Write Conflict)
a problem has come up after a SQL DB I used was migrated to a new server. Now when trying to edit a record in Access (form or table), it says: WRITE CONFLICT: This record has been changed by another ...
- 3,044
45
votes
5
answers
141k
views
Is it possible to pass parameters programmatically in a Microsoft Access update query?
I have a query that's rather large, joining over a dozen tables, and I want to pull back records based on an id field (e.g.: between nStartID and nEndID).
I created two parameters and tested them as ...
- 563
45
votes
10
answers
181k
views
How can I modify a saved Microsoft Access 2007 or 2010 Import Specification?
Does anyone know how to modify an existing import specification in Microsoft Access 2007 or 2010? In older versions there used to be an Advanced button presented during the import wizard that allowed ...
L G
44
votes
8
answers
30k
views
What are the rules governing usage of parenthesis in VBA function calls?
I've just had an irritating 30 minutes on a "compiler error" in VBA (Access 2003) caused by my use of parenthesis around the arguments I'm passing to a Sub I defined.
I've been searching to ...
- 8,595
43
votes
9
answers
136k
views
Find the directory part (minus the filename) of a full path in access 97
For various reasons, I'm stuck in Access 97 and need to get only the path part of a full pathname.
For example, the name
c:\whatever dir\another dir\stuff.mdb
should become
c:\whatever dir\another ...
- 11k
43
votes
4
answers
225k
views
Access VBA | How to replace parts of a string with another string
I am trying to create a piece of code that replaces one word with another.
Example: Replace Avenue with Ave and North with N.
I am using MS Access, I could use SQL REPLACE Function but I want to do ...
- 3,967
42
votes
8
answers
202k
views
Error: "Could Not Find Installable ISAM"
I've written some VBA code in an Excel workbook to retrieve data from an Access database in the same directory on a desktop. It works fine on my machine and several other machines running Windows XP, ...
- 1,488
42
votes
7
answers
186k
views
How to copy to clipboard using Access/VBA?
Using VBA inside Access2003/2007.
How to copy the contents of a string variable to the clipboard?
This site recommends a creating a zero length TextBox, copying the string to the TextBox, then ...
- 15.8k
41
votes
5
answers
121k
views
Autonumber value of last inserted row - MS Access / VBA
I have a JET table with an auto-number as the primary key, and I would like to know how I can retrieve this number after inserting a row. I have thought of using MAX() to retrieve the row with the ...
- 12.1k
40
votes
6
answers
94k
views
How does one decompile and recompile a database application?
I have an Access database application and I would like to know the proper way of decompiling and recompiling it.
- 59.3k
40
votes
3
answers
190k
views
Does VBA contain a comment block syntax? [duplicate]
In VBA is there a short way to comment out a block of code the same way java uses /*...*/?
- 821
40
votes
12
answers
58k
views
Get Name of Current VBA Function
For error handling code, I would like to get the name of the current VBA function (or sub) that the error occurred in. Does anyone know how this could be done?
[EDIT] Thanks all, I had hoped that an ...
- 1,940
39
votes
13
answers
240k
views
"Operation must use an updateable query" error in MS Access
I am getting an error message: "Operation must use an updateable query" when I try to run my SQL. From my understanding, this happens when joins are used in update/delete queries in MS Access. However,...
- 5,679