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.

Filter by
Sorted by
Tagged with
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 ...
mpag's user avatar
  • 968
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 =====...
Ozzah's user avatar
  • 213
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 ...
forX's user avatar
  • 415
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 ...
Warrior Bob's user avatar
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 ...
Simon1979's user avatar
  • 213
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 ...
Warrior Bob's user avatar
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 ...
Charles W's user avatar
  • 193
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 ...
J. Taylor's user avatar
  • 379
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 ...
BenV's user avatar
  • 4,893
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 ...
pedram's user avatar
  • 133
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 ...
choloboy's user avatar
  • 225
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, ...
Move More Comments Link To Top's user avatar
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?
user avatar
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 ...
Przemyslaw Remin's user avatar
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 ...
Max's user avatar
  • 73
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 ...
user avatar
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.
Curtis's user avatar
  • 337
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 ...
mwolfe02's user avatar
  • 432
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 ...
mwolfe02's user avatar
  • 432
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. ...
Chris Purves's user avatar
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 ...
Chris McQueen's user avatar
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 ...
pila's user avatar
  • 191
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 ...
Michael Richardson's user avatar
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 ...
Loïc Lopes's user avatar
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 ...
Mike S.'s user avatar
  • 53
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 ...
SqlSandwiches's user avatar
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 ...
James Peter McConnell's user avatar
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 ...
CDZ's user avatar
  • 51
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 ...
Marko's user avatar
  • 61
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. ...
user16631's user avatar
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=[...
John Wordsworth's user avatar
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 ...
forX's user avatar
  • 415
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 ...
bpatterson's user avatar
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 ...
Christian Schmidt's user avatar
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 ...
Sabre's user avatar
  • 189
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 ...
Lou's user avatar
  • 181
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 ...
Sayad Xiarkakh's user avatar
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. ...
David's user avatar
  • 539
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 ...
Martin F's user avatar
  • 311
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, "...
Trish Main's user avatar
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 ...
deepz's user avatar
  • 267
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 ...
David's user avatar
  • 539
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 ...
mit's user avatar
  • 507
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 ...
User1974's user avatar
  • 1,457
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 ...
Daneel Olivaw's user avatar
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?...
Ryan's user avatar
  • 185
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 ...
Bruno Aloe's user avatar
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 ...
David's user avatar
  • 539
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 ...
Ritesh Seth's user avatar
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 ...
RG2's user avatar
  • 41

1
2 3 4 5
14