Questions tagged [ms-access]

Microsoft Access is a relational database management system that is part of the Microsoft Office suite.

172 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
3
votes
0answers
34 views

Migrating Access Saved Import Specifications to SSIS Package

We currently receive a multi-hundred column flat file from a vendor, and the tech prior to me had created an import specification in Microsoft Access to automatically split the file into the right ...
2
votes
0answers
165 views

Troubles inserting new records in linked SQL-server table in Access

Years ago, I upsized the back-end database from Access to SQL-server using the build in tool in Access. It worked fine but in the recent two weeks an error began to appear. This problem occurs for ...
2
votes
0answers
240 views

Export table from MS Access to Excel while retaining Field Name and disregarding Caption

I have a MS Access Database, with each table containing the data of a particular questionnaire. I am building data entry forms for each of these tables, but as the field names may prove to be ...
2
votes
1answer
226 views

How to Prevent Timeslot Overlap

SQL newbie here. I currently have a 2-table database-"Customers" and "Bookings". This question concerns the "Booking" table, which has 3 attributes this question is about: BookDate BookStart BookEnd ...
2
votes
1answer
197 views

Relationship between two multi-valued child tables

Scenario In my system, there are a number of different TradeBaskets. Each TradeBasket contains a number of different Securities. The Securities have a Type, Value, Sector etc... and new Securities ...
2
votes
0answers
583 views

#deleted in all fields using ODBC driver between PostgreSQL and Access 2013

I planning to use MS Access (2013) as a front end to a PostgreSQL database server (version 9.5.2). However, when updating a table in Access the new row appears with “#deleted” in every column until I ...
2
votes
0answers
149 views

Distributing MS Access Database

I develop an application that saves records in MS Access DB. Each user has it own DB copy on his local machine, and there is a central DB that reflects all the copies from everyone. The central DB is ...
2
votes
0answers
865 views

Design Question - Stock and OnSale Table

Background : I'm designing a solution for a repair toy association. Since they have office - I'll use Access. Plot : Stock will store items - I choose this primary key (Item_Id,Record_date) ...
2
votes
0answers
153 views

Moving from MS Access to SQL Server 2012, need help with column history

I'm moving an access database from all the data being in MS Access to MS Access being just a frontend, but I'm having trouble figuring out how to do a sort of column history. I have a tasks....
2
votes
0answers
826 views

Problem forming a query with subqueries in design view? (MS Access 2010)?

I just started a new job as a dbase admin intern, and due to the database being very large, it is better to do queries in design view, rather than hardcoding the sql. That being said, I cant seem to ...
2
votes
0answers
816 views

Moving data from MS Access to MS SQL Server

I'm trying to migrate data into SQL Server from an existing legacy set of MS Access databases. Currently everything is working except a few core tables that are wide and contain a number of ...
2
votes
2answers
1k views

Splitting 1 record on one table to become two records on another table

I am REALLY very new to this so please excuse my ignorance, but I could really use some help! I've got two tables: BillsFiled and Authors On BillsFiled, there are the columns: ID, Symbol, Filedate, ...
2
votes
0answers
772 views

SET IDENTITY_INSERT equivalent in Access

In sql server i can explicitly insert value to a identity column of a table by using SET IDENTITY_INSERT ON . For an Access database i am using Autonumber column datatype. For this column i am not ...
2
votes
0answers
1k views

Parameters in queries using vba

I am trying to write a process to run several prewritten Queries in a row without any human interaction. I tried to write a macro, but the after saving it it still prompted for the date range in the ...
2
votes
0answers
2k views

Connection to MS Access from Oracle over a DB Link

I need to establish a connection between a MS Access database, which is placed on a network share on a windows box, and an Oracle database on a Unix Box. I need to query the Access tables from the ...
2
votes
0answers
810 views

How do I auto-fill data in Microsoft Access 2003?

I am creating a Purchase Order Form at work using Microsoft Access 2003 so that it can be saved and archived in a database and reduce paper costs. These are my current dilemmas: I am unable to ...
2
votes
1answer
820 views

ORDER BY clause with Alias

I am trying to get the sum of the market values for each given CPTY. Each CPTY can have several market values. In my output list, i want to see the top 5 CPTYs, ordered by the sum of their market ...
1
vote
1answer
27 views

How to move geographical data from MS Access to SQL Server

For 20 years I've been working with MS Access, with much VBA involved. I have a lot of geographical data in Access and I'm preparing to move to SQL Server because it supports geographical data types, ...
1
vote
0answers
17 views

Rolling multiple rows into single row

I need help getting multiple rows of data to all pull into one row based on a primary key. I have four teachers rating a student's academic and behavior performance through the year. At the end of the ...
1
vote
1answer
11 views

MS Access linked table displays short text instead of Decimal data type

I have linked tables in MS Access databases (various versions, 2007-2019) which are connected to SQL server 2017 via Sql server 2017 drivers (eg, msodbcsql_17.2.0.1_x64). Fields that are of data type ...
1
vote
0answers
53 views

SSIS to import tables from Access DB to SQL

im trying to design an SSIS package to import 80 tables from Access DB into SQL DB, I have done it through the export and import wizard then saved the SSIS package but i didnt like the way the package ...
1
vote
0answers
18 views

Modeling a system with optional components

I am working on an MS Access database that is intended to help keep track of a large scale implementation of many component parts connected together. I have created a working implementation based on ...
1
vote
0answers
43 views

ODBC connects but authentication fails on users table

We have sql 2008 running in mixed mode. The site where sql is running the ODBC connection works and ms access authenticates against the user table. We have an IPSec vpn to a satellite office using ...
1
vote
0answers
21 views

Selecting Duplicates on All Fields

I have an MS Access (no laughing at the back) database I've used to import a bunch of IIS logs into. Having looked at the Excel files I pulled these in from, I'm worried going by the dates that some ...
1
vote
0answers
76 views

After reboot SSIS cannot connect to MS Access dbs

We rebooted our sql server 2016 machine. SSIS jobs that have been running fine are now unable to connect to access dbs. it throws the error: "XXXXX:Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE ...
1
vote
0answers
45 views

Unique Records property in Delete Query

In Microsoft Access, if you create a delete query based on two or more related tables, you must open the Property Sheet and set the Unique Records property to "Yes". If you leave it set to "No", you ...
1
vote
0answers
19 views

How to use validation rule within database with drop down menus.

Building a scheduling database using access 2013 for my company. I currently have it set up so that only employees that have a rating of 3 or more for the particular job show up when you use the drop ...
1
vote
0answers
25 views

Postresql & Access synchronising

I would like to ask for hints / tips / pointers on the possible approaches that I might use for the following... I maintain a database locally using postgresql 9.5, hosted on a Linux server. However, ...
1
vote
0answers
38 views

SQL Server linked to AccessDB Update statement doesn't work in some scenarios

I have a SQL Server 2014 Express server linked to an Access DB. I am able to SELECT and view data in the access db without issue. However, when I run update statements they don't always work. The ...
1
vote
0answers
33 views

Jet SQL switch — Fill in 'n/a' for blank cells

My query isn't working as I expect it too. I am trying to create best_id and where blanks/null cells are set to 'n/a' . Also I use Microsoft.ACE.OLEDB.12.0 connection via excel to do an internal ...
1
vote
0answers
88 views

Access Field Data Type Changed

I have several MS Access databases with tables containing fields that were set to the Data Type "Double" with decimal places set to "Auto" Access somehow changed all these fields to the Data Type "...
1
vote
0answers
110 views

MS Access 2016 M:N relationships return multiple columns instead of multiple rows from a JOIN

Let's assume a data model like this: Books could have multiple authors as well as authors could have written multiple books. If a book and its authors are selected by the following statement (this ...
1
vote
0answers
455 views

How do I get MS Access 2016 to work with SQL Server 2016 where DATETIME columns are involved?

I'm working in an environment that has a lot of apps that are a MS Access 2016 front-end with a SQL Server 2016 back-end. Symptoms: When viewing data from a table where a DATETIME column is part of ...
1
vote
0answers
966 views

How to Insert a static value into a column in MS Access during a query

I am currently switching data sources for one of my databases, now the old data used to include a column called 'Counter' every row had a 1 in it, this 1 was then used in excel formulas and summary ...
1
vote
0answers
31 views

Save Access SQL code to text file or Python variable?

This may not have a good answer but maybe? So I have a number of Access databases that query a SQL Server through ODBC to generate reports. The SQL Server backend is being changed out and I need to ...
1
vote
0answers
55 views

MS Access: automatic creation of row in a table based on field in another table

I have a Table A with an ID field and a Question field. The question has a yes/no answer. I want MS Access to automatically create a new observation (row) in another table, Table B, if I answer yes. ...
1
vote
0answers
464 views

Using VBA to open Access in full screen and in foreground

I have 2 issues. I am using VBA code to create a macro in PPT that opens an Access report. The macro works but does not open it in full screen. I have tried to add the DoCmd.maximize but not sure ...
1
vote
0answers
199 views

Access linked to SQL with SSMA slow performance

I'm working on migrating a legacy application's DB (in Access 2003 format) to a SQL Server 2008 back-end (This will be upgraded later on). We are close to hitting the 2GB size limit in Access and ...
1
vote
0answers
31 views

Switch Between Windows Authenticated Users On Single Windows Account

We have a CRM that has been developed in house in Microsoft Access 2003. We are also in the process of moving from an MS Access backend to a SQL Server backend. I have been researching different ...
1
vote
0answers
107 views

link multiple MS project to Access/Excel

sir I have a large project tracking database used to track PMs, status, help needed, action items, reports etc. I keep the project schedule in a large MS-Project file. I've exhausted all efforts to ...
1
vote
0answers
405 views

Creating a Relational Products / Package Database

To prefix this, I am stuck using Microsoft Access, or at least relating to it, so my capabilities are a bit limited. I also do not want to use an EAV model. I could put the new tables into SQL Server, ...
1
vote
0answers
49 views

SQL server schema to accomodate duplicate PK/FK values from autonomous Access data bases

I have several test stations with Access databases that have identical structures (12 related tables each). They are independent and all internal PK & FK values (Autonumber PK & Long Integer ...
1
vote
0answers
326 views

Access-PostgreSQL: Link tables as readonly, edit registers in form

I have a PostgreSQL database working as backend and I have to develop an Access frontend solution for regular users of this database. I decided it best to create some forms to manage the input, ...
1
vote
1answer
15 views

Is there a way to input field information by cvs file?

I am importing a new database. I am asked to input field information in MS Access manually. Is there a simpler way? For example, import a cvs version field information into the following
1
vote
1answer
114 views

Join two tables and create a new one in SQL

I am new to SQL and trying to apply a concept of Joins in MS-access. I have two tables Table 1 and Table 2: I want a query which will create a Table 3: Ideally i want all the data from both tables ...
1
vote
0answers
819 views

Using Microsoft Access front-end with PostgreSQL

We are using a MS Access database at work and I am exploring the idea of using the MS Access front-end and use a Postgres back-end to better manage concurrent uses and add spatial capabilities with ...
1
vote
0answers
82 views

Table Relationships: Table 1 (Field 7, Field 8, Field 9) to Table 2 (Field 1)

I'm sure that this is probably 'Databases 101', but I can't seem to figure it out. So, I'm setting up a database in Access - that I will want to move to MySQL, and I currently have 4 tables, set up ...
1
vote
0answers
66 views

Create multiple rankings and a table

Here is my problem: I am using SQL for Access 2013; I have a table tbl with some fields Category, Subcategory and Value: Category | Subcategory | Value | | C1 | ...
1
vote
0answers
33 views

How do I hide the “read …save as” notification for an accdr file

When I make the accdb file to an accdr file and open it with Access 2010, the database is created with Access 2010 and OpenGateSoftware’s UI builder. I get a notification bar at the top saying: Read ...
1
vote
1answer
51 views

Search a Table for a Lack of Records

Please understand this is not my field. I have inherited an access database from a previous employee. I have a database which logs training so I have 1 table with employees names trade etc. I then ...