Questions tagged [ms-access]
Microsoft 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 and MySQL, can also be used as a database server for Access applications.
674
questions
21
votes
3
answers
739
views
Access (Jet) SQL: DateTime stamps in TableB flanking each DateTime stamp in TableA
First Words
You can safely ignore the sections below (and including) JOINs: Starting Off if you just want to take a crack of the code. The background and results just serve as context. Please look at ...
11
votes
5
answers
41k
views
Full Outer Join in MS Access
I have two staff lists:
List A:
StaffID Supervisor
====================
0001234 NULL
0001235 0001234
0001237 0001234
0001239 0001237
and
List B:
StaffID Supervisor
=====...
10
votes
3
answers
4k
views
Query best possible matches and order them
I'm trying to write a query along these lines:
select *
from tbl
where
col1 = 1
and col2 = 2
and col3 = 3
order by
...
;
I want first all the results where all 3 WHERE ...
10
votes
1
answer
2k
views
Why would suser_name() not reflect an AD account name change?
One of our users' names was legally changed, so we changed their Active Directory username to match - from domain\oldname to domain\newname. However, when suser_sname() is called by this user in a ...
9
votes
7
answers
6k
views
Main Considerations When Moving From MS Access Programming to SQL Server
I am a 100% self taught MS Access programmer (main part of my job is programming), I am now building larger databases; still using MS Access as the UI but SQL Server to store all data and do more of ...
9
votes
5
answers
16k
views
Can I be automatically notified of prolonged blocking in SQL server?
About once a week I have to resolve a blocking chain on a SQL Server 2005 database, caused by a long-lived read lock from an Access 2003 front-end. The lock is taken out whenever a user opens a ...
9
votes
6
answers
37k
views
What is the easiest way to get started using databases with real data?
I have a project that could benefit from using a database, but I have no experience with databases, don't have access to a server, and have relatively little experience working with things living ...
8
votes
5
answers
26k
views
SQL queries to only show most recent purchase records for individual food items
I'm working with a food purchasing / invoice system in MS Access 2013 and am trying to create an SQL query that will return the most recent purchase price for each individual food item.
Here is a ...
8
votes
3
answers
6k
views
How to log data changes in Access at the table level?
I support an Access database for a non-profit organization. We have some issues with data mysteriously changing, and there been some, um, friendly debate over whether this is caused by users who are ...
8
votes
1
answer
1k
views
Normalizing a database without access to source data?
I've started a new role which deals with large amount of related data. Our source of all this data is various Excel dumps pulled from databases we do not have access to. The previous person filling ...
7
votes
3
answers
32k
views
Easiest way to import access db (.accdb) into SQL Server 2016?
I have a .accfb file from an access 2016 database and am trying to import this database into a SQL Server 2016 developer edition instance.
While I found posts online discussing using import/export ...
7
votes
3
answers
3k
views
Online resource for writing Access SQL statements [closed]
I am looking for an online resource for writing Access SQL statements. I write queries using SQL because the UI puts limitations on what can be queried. I have been using w3schools for reference, ...
7
votes
1
answer
12k
views
MS Access database in a network
I would like to ask if MS Access database supports working in networks & multi-user access?
7
votes
2
answers
28k
views
Import from Access .accdb file to SQL Server [duplicate]
How to import data from Access .accdb file to SQL Server? What should I choose in SQL Server Import and Export Wizard?
Suppose we choose OLE DB Provider for SQL Server Integration Services, then if ...
7
votes
1
answer
13k
views
Is it possible to synchronize two ms access db?
We have a flower store where we use MS Access and a client program to work with the database. But this program can work only with one database.
We are going to open a new store, so now we have to ...
6
votes
2
answers
49k
views
Backup Access Database Daily/Weekly
We have a Microsoft Access database split into backend/frontend. What I am looking for is to be able to automatically backup these files on a daily or weekly basis - what is the best way to go about ...
6
votes
3
answers
423
views
What would I look for in SQL Server to determine if a user connected with Microsoft Access?
Is there a way I can check SQL Server to see who is accessing it using Microsoft Access (as opposed to a website)? I'm using SQL Server 2008R2 with the default logging settings.
6
votes
1
answer
2k
views
Any reason not to add 'rowversion' column?
The TechNet article, "Optimizing Microsoft Office Access Applications Linked to SQL Server", recommends adding a rowversion column to SQL Server tables linked from MS Access. From the Supporting ...
6
votes
2
answers
6k
views
Column definition for a SQL Server equivalent to Access BOOLEAN type
The BOOLEAN datatype in Access (ie, Jet/ACE) returns a -1 for True and 0 for False; the field is always required (i.e., it cannot be set to NULL). The SQL Server BIT type returns 1 for True and 0 for ...
6
votes
2
answers
3k
views
SSMA for Access - Migration Assistant Wizard fails at link tables
When using SQL Server Migration Assistant for Access with the Migration Wizard, all goes well converting an Access (2016) database to SQL Server Express (2017) until I get to the Link Tables step. ...
5
votes
3
answers
2k
views
SQL Database vs Multiple Microsoft Access Databases
The company I work for produces a lot of test data in raw text files from lots of different devices. Each device belongs to a 'process' of which there are only a few dozen. Currently we have a program ...
5
votes
4
answers
8k
views
MS Access / MySQL: unlock locked Records
I have a Microsoft Access database that is connected to a MySQL server via ODBC and is accessed by multiple users.
Over time many records are locked and editing is not possible anymore.
Is there a ...
5
votes
1
answer
2k
views
What is the meaning of this operator: *=
I have a very old Access application that uses SQL Pass-Through Queries. This application has been nursed along through multiple server upgrades, and we recently moved up to SQL 2012.
I now get a ...
5
votes
2
answers
983
views
Loop through tables or copy paste
I have to do a migration where I should retrieve data from 40 Access tables, which should contain several of the same columns (but not all, the schemas are different). To make it simpler, I used SSMA ...
5
votes
1
answer
3k
views
SQL Server ODBC Mirror/Failover Partner
Background/Context
We are in the process of moving our SQL Server 2000 databases to a mirrored SQL Server 2008 R2 server with a witness and automatic failover. We have both .Net and Access ...
5
votes
1
answer
1k
views
How to perform the dreaded double hop with linked servers?
The situation:
I have SERVER1, SERVER2, ACCESSDB on SERVER3.
I have successfully created a linked server connection from SERVER1 to SERVER3's ACCESSDB.
I want to be able to access the linked server ...
5
votes
1
answer
232
views
Design a relational database (Access) that identifies breaches in records based on user defined limits
I am trying to create a structured database in Microsoft Access that monitors trading risk. Let's say an organisation wants to analyse all of the trades that it has in its books at any given point in ...
5
votes
3
answers
953
views
Microsoft Access Export to Excel
I have an Excel spreadsheet that is filled with a ton of data which is organized by column (a column for names, a column for occupation etc.). I have recently moved all of this data into Microsoft ...
5
votes
2
answers
10k
views
Access 2010 linked table - solve the "decimal field's precision" error
I have an Access 2003 database (running on Win 7 64-bit, Office is also 64-bit) that has many linked tables pointing to an Oracle 11g instance through 64-bit Oracle ODBC driver. Most of the tables ...
5
votes
1
answer
2k
views
Removing duplicates from a query with conditions in Access 2007
I have a table of transactions with 880,095 records that I am querying.
There are duplicate Transaction ID's in the table that I would like hidden/removed when querying the table.
Eg 1.
...
5
votes
1
answer
7k
views
Using a Parameter Query to generate Charts in MS Access Reports
I am using Microsoft Access 2007 and I have a query which looks something like this;
PARAMETERS Identifier TEXT(255);
SELECT
DateDiff('h', (SELECT Min(SampleDateTime) FROM Specimen WHERE UserID=[...
5
votes
3
answers
2k
views
Querying special characters
I work with language with special characters (example: e -> é). I have a query with a LIKE but when I search a word with special char and my query doesn't have the special char, I don't get the ...
5
votes
1
answer
771
views
Need ODBC connection to postgresql to include materialized views
I have an Access front end user interface that links to tables in a postgreSQL database using the most recent ODBC driver. I can link to the postgreSQL tables and views but can't get to the ...
5
votes
1
answer
4k
views
Display a one-to-many relationship in one row
I have a "one to many" relationship in two tables:
Table "products" contains: product_id, product_descr
Table "packing" contains: product_id, package_id, package_desc
One product has often more than ...
5
votes
2
answers
7k
views
ODBC Connections locking tables in SQL Server
We have an ERP system that is a little less than modern, the backend is currently sitting on a SQL Server 2008 R2 installation. We have through mandate of management (isn't that always the case) the ...
4
votes
2
answers
2k
views
What principles should I follow when entering data into a complex relational database?
I'm new to Databases, so forgive me if this is a basic question.
I'm attempting to parse a complex open source dictionary, and output the data into an Access database, which is equally complex:
I ...
4
votes
2
answers
39k
views
Compare Two Rows and Find the Difference Between Them Which is In The Second Column?
I have a table that has the following data:
ID Name LastName
1 William Johns
2 John Adam
4 William Smith
3 Mark Stephan
I want to Find all those ...
4
votes
3
answers
2k
views
Database Performance Tuning
I have a MySQL backend, MS Access front-end system that is nice and responsive over a local network but becomes sluggish over a broadband connection. I haven't done any performance tuning as of yet. ...
4
votes
5
answers
3k
views
How can I allow part of a key to be null and enforce uniqueness of records with nulls?
I have an Access table called Engagement with four fields:
Emp_id, Year, Week, Act_id
It records when an employee is/was engaged in an activity.
Each field is Number and each makes up part of the ...
4
votes
1
answer
100k
views
IF IS NULL then SINGLE else keep the value of the field
I am working with a DB in Access and I'd like to be able to fill the field in this row with a result of SINGLE if it is not already filled with another value.
I wrote Iif(MaritalStatus) is null, "...
4
votes
1
answer
29k
views
Joining 3 tables using SQL Query?
I'm having problems joining three tables. It works well when I use the same query for joining two tables (after removing LEFT OUTER JOIN C ON B.No = C.No). However, it doesn't work when I use three ...
4
votes
2
answers
235
views
Concurrency question?
I have a MySql (5.5) database with a MS Access frontend. If two users are viewing the same record at the same time how can I make each user aware of each other? For example how would I go about ...
4
votes
1
answer
3k
views
MS Access displays alle columns in SQL Server tables as "deleted"
I have created an ODBC connection to a SQL Server 2005.
When I link to the tables from MS Access, some tables have only "deleted" data.
This means the rows and columns are displayed, but all data ...
4
votes
2
answers
175
views
Access not properly translating TOP predicate to ODBC/Oracle SQL
I have a MS Access query that is based on a linked ODBC table (Oracle).
The DDL for the underlying Oracle table is:
create table road_insp
(
insp_id integer,
road_id integer,
insp_date ...
4
votes
1
answer
93
views
Fill percentage of total field conditional on some other field with MS Access SQL
This question is an extension of Fill percentage of total field with MS Access SQL.
I have the following 2013 MS Access SQL query:
UPDATE MyTable
SET F = f / DSUM("f", "MyTable") ;
This query ...
4
votes
6
answers
6k
views
What flavor of SQL is used inside Microsoft Access?
I don't have much Access experience, and I took on a new Access project. I can use SQL to query the Access databases.
What SQL specification/flavor is used inside Access? Is it the same as SQL Server?...
4
votes
1
answer
2k
views
Designing a database which can track stock movements
I work for a manufacturing company which sells machines to customers which have a control unit installed in them. We need to keep track of the locations of all the Machines after they are sold to the ...
4
votes
1
answer
2k
views
Ms-access ODBC calls for linked table behaviour
I'm curious if anyone knows how ms-access goes about generating ODBC calls for linked tables.
The reason I ask is that I'm trying to make the front-end of a system super efficient and too minimise ...
4
votes
3
answers
153
views
representation in ms-access
I have a database in microsoft access. I want to know how to look up a singular datum from a reference table giving a dynamic set of values. Here is a representation of what I mean:
I have the ...
4
votes
1
answer
758
views
I need help to migrate denormalized table content to normalized form- MS Access
I have a staff database where we are storing information in Normalized form. There are 3 entity tables and 2 mapping tables connecting these with m-n relationships
However, for ease of collecting ...