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

learn more… | top users | synonyms

1
vote
1answer
51 views

Access Data stored in SQL Server

I'm currently working on a project to convert our Access Apps to store the table data in SQL Server. Using SSMA to migrate the data results in the foreign keys and Look-ups work as expected in ...
0
votes
1answer
72 views

How to prevent #Error values derived from Nulls in an outer join

Suppose I have two tables to outer join Tab1: ID Nam 1 Joe 2 Moe 3 Flo Tab2: ID Val y w 1 stuff 66 33 2 duff 67 44 and one of the resulting columns is derived -- i.e., ...
1
vote
0answers
164 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 ...
0
votes
1answer
170 views

Joining two database tables into one

I have a database with two tables. Each table has different structure but they do share few common fields. Simplified example: Table1 field structure: Field1, Field2, Field3, Field4 Table2 field ...
0
votes
0answers
23 views

Accessing Unidata DB over Access 2003 through ODBC driver

My apologies if it sounds like gibberish, I work extensively with MS SQL and not much else. I have an Access SQL query that selects data in a linked table in Access 2003. That table is linked using ...
0
votes
1answer
97 views

Rounding mixed data types

I have a column with a mixture of letters and numbers, and I need to round only the cells that contain numbers using SQL. I currently have my data saved as strings only, and when I try to use a round ...
1
vote
1answer
195 views

PostgreSQL ODBC and Microsoft Access date/time in aggregate functions

I am trying to replace our MS Access 2003 databases with PostgreSQL 9.3. The first step is to use our MS Access front end to the upsized databases in PostgreSQL. Everything seems to work very well ...
1
vote
1answer
2k views

How to set user-level security in 2007+ MS Access?

I've read this MS info which should cover my problem. There are quite clear steps: Open the .mdb or .mde file that you want to help protect. On the Database Tools tab, in the Administer ...
0
votes
1answer
330 views

ORA-01461 in a msaccess linked table

In an Oracle 11 database I have a table that all fields are varchar2. This table is linked in an access database, and when I try to do an insert from a data aggregate query, it throws the error ‘can ...
1
vote
0answers
20 views

What is a .cof file that the database is linked to?

I am on working on creating a script that de-link and re-links all the linked tables from the front-end. I'm working on an exisiting database that has been created and pre-configured. When I click ...
1
vote
0answers
231 views

Most effective way to manage a growing list of time series data in an MS Access database

I am building a database to store time series data along with some other summary statistics. Since this is a local application I do not have access to more robust database solutions and am confined to ...
1
vote
1answer
730 views

How to get the equivalent of array or string GROUP BY aggregate functions in Access?

From SQL Aggregate Functions (Office 2007) there are only these GROUP BY aggregate functions in Access SQL: Count(), Sum(), Avg(), First(), Last(), Min(), Max(), StDev(), StDevP(), Var(), VarP() ...
1
vote
1answer
294 views

MS Access: adding column if not exist (or try/catch it)

this is an easy question, but I can't find out the solution... I have to check if a column exists and add it if the test is negative. How can I do with only sql code? This is the syntax for SQL ...
1
vote
1answer
437 views

Wrong data type while using linked tables in MS Access

I have a SQL server database which needs to be linked to MS Access forms. I have a table with one column as both Identity and is a also a primary key and is big int But after linking the table , if ...
0
votes
0answers
395 views

Access 2010 delete and append queries run partially then give “Read Only” error

I have a number of append and delete queries written in Access 2010 that link to and modify tables on our SharePoint 2010 site. These queries have run without problem for months, but suddenly they've ...
0
votes
1answer
231 views

How to define destination for an append query Microsoft Access

I'm trying to append two tables in MS Access at the moment. This is my SQL View of my Query at the moment: INSERT INTO MainTable SELECT FROM Table1 INNER JOIN Table2 ON Table1.University = ...
0
votes
1answer
145 views

Linking two fields from different tables (are relationships required?)

For example, I have two tables. Employees and Products. Inside Employees I have the fields: emp_ID, emp_fname, emp_lname, emp_products Inside Products I have the fields: pro_ID, pro_name, ...
0
votes
1answer
1k views

Allow access to enter data through forms, but not full database access

Using Access 2013 or Sharepoint 2013 is it possible to give someone access to data entry forms that work with a back end database whether Access database or Sharepoint SQL. But from everything I've ...
1
vote
1answer
90 views

Displaying only the most recent activity of an item

I have made a database that tracks the shipping and receiving of the tools that my company has. I am now wanting to make a query that shows only the most recent activity of all the tools. I have a ...
1
vote
1answer
460 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 ...
1
vote
1answer
234 views

User can see Oracle tables via ODBC that her account does not have access to [closed]

We have a user whose Oracle account, according to ALL_TAB_PRIVS, has access to tables A, B, and C. However, when the user creates an ODBC and connects to Oracle via Microsoft Access (using her Oracle ...
1
vote
0answers
216 views

Replicating Multiple SQL Servers to Azure and Accessing From Web Apps

I am setting up quite a large system with multiple servers (MS SQL Server), and Office 365 Access DB, and a custom SharePoint web app for reporting purposes. The system should work as follows: Each ...
0
votes
0answers
54 views

Are documents made with MS Access 32/64 bit compatible with the other “bitness” version?

I don't have a copy of 64 bit MS Access installed so I can't try this. I'm a developer looking at what's involved with adding support for 64 bit. I'm just using the MS Access database as a back-end. ...
1
vote
1answer
95 views

MS Access - protecting backend file

I'm creating a split database in Access which has the backend on a drive accessible to all employees in the company at the moment. This isn't such a big deal at the moment because I'm still developing ...
1
vote
1answer
919 views

Updating one table from another table where non-primary-key field is similar string

I have a database in Access that I am trying to fix (actually, I'm working on a copy of the original). There are two tables, one with data input errors, and a second that is a copy of those errors, ...
0
votes
0answers
52 views

How can I track changes across 60 local databases with 1 million+ rows each?

The files are mdb and I have them locally on my computer. Just trying to track changes across the 60 databases. Realistically I want to compare two at a time and find the differences in the reports, ...
1
vote
0answers
45 views

Fetch Top 2000 Records

I have following query: strfinal = " TRANSFORM Format(Sum([Mandays].[Hours]),""#0.0"") AS [The Value] SELECT Mandays.WorkTypeCode AS WONo, ...
0
votes
0answers
135 views

Database design for varying prices from multiple vendors

I am designing a database where the products are sold in bulk with multiple pricing options, from 4 different vendors. What is the best table configuration to store this information? Id like to be ...
1
vote
0answers
61 views

MS Access Residual Query Parameters on Copied Report

I've been working on an ms access database which was put together prior to my employment, I wanted to create a new report in order to display Quality Control information, in order to do this I copied ...
0
votes
1answer
395 views

Exporting Access DB to SQL Server: unexpected EOF?

How do I correctly export a table from an old Access .mdb to SQL Server 2012? In Access I do: right click on table, export as text file and use comma or tab separator. My line endings are CRLF as ...
0
votes
1answer
366 views

SQL access query to count and delete rows > n

Can someone help me with a SQL (Access) query to delete all rows greater than n entries ordered by ID? I.e. say I have 1200 rows of data with ID incremented . I need delete the first amount leaving ...
1
vote
0answers
125 views

Importing mdb to AWS RDS SQL Server

I have a small and very old access DB, it is just one .mdb file and it was made before Access 2003. How can I import it to SQL server running on AWS RDS? I actually only need to import one table from ...
0
votes
2answers
105 views

MS Access Query to update

Trying to update Packet.MSGState to P where if not R or D and it is Y in table Packet.MSGState UPDATE Packet SET Packet.MSGState = 'P' Where (MSGState <> 'R' and MSGState <> 'D') and ...
2
votes
1answer
11k views

Linking Fields From 2 Different Tables (Microsoft Access)

I am trying to link 2 fields from 2 different tables on Access but I will give an example to try and make the issue clearer. I have 1 table called "Employee Details" which contains the field ...
2
votes
1answer
165 views

Duplicate table structure with different data [closed]

I am looking for a way to resolve a design issue with a Microsoft Access project that is being converted to use SQL Server 2014 as a backend. We have several Access backend files, two of them have ...
2
votes
0answers
314 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 ...
0
votes
1answer
29 views

Additional info on some rows in the table

I am trying to design an MS Access 2013 database to keep track of an inventory of items. All items have certain common properties, like name, date they were stored, location of the physical item, ...
2
votes
1answer
633 views

How to UPDATE/DELETE/INSERT with Microsoft.ACE.OLEDB.12.0 as Linked Server in SQL Server 2012?

I need to update an Access 2003 database from SQL Server 2012 with the Microsoft.ACE.OLEDB.12.0 provider. As an administrator, everything works great. I can do Select, Insert, Update and Delete ...
2
votes
1answer
142 views

I am Trying to Write an Access Query to Get a person having a First Name and Last Name but not all People having any of those names

I am using Microsoft Access. In my search form I have 3 text boxes that I enter text to search for: First Name; Last Name; Last 4 of SSN. With the current query when I enter text into multiple boxes ...
0
votes
2answers
214 views

Help with preventing double bookings with variable lengths

I'm designing a sample database for a venue where members can create bookings for venues. I'm looking for a method to prevent double bookings (possibly by creating a button on a form that checks ...
0
votes
2answers
96 views

Should I backup or copy

Access has the option of creating a backup of dbase, but is there any difference between doing this and 'saving database as'? Thanks for your time and patience for the ignorant.
1
vote
1answer
289 views

Find and Replace performance issue

Table 1: linked native access table Table 2: linked pgsql table Both tables have same records but searching for a record using built-in Find and Replace dialog box in selected field is a lot lot ...
0
votes
1answer
5k views

Access 2010 After Insert data macro to update another table

I have an Access DB with 3 tables. Table 1 Appointments Fields with Data that is not used anywhere else, but some of the information is inserted into 3 with a after insert Script. Table 2 Usage Data ...
1
vote
1answer
3k views

MS-Access vba sql update where not working

I am having trouble with an MS-Access VBA sql update command. I have a form which has a combobox of GroupMembershipIDs, and a button. I am trying to write code which will iterate through the ...
1
vote
1answer
88 views

Query performance issues

I need help in improving the performance of the following query. It has the purpose to select one record out of a group of records with an identical ID (doublettes). Rules are that Date2 must be the ...
0
votes
1answer
1k views

Applying normalization to a relational Database (Access & SQL Server)

This question is pretty specific but I hope it can get answered on here. There is a wide range of information about normalization but I feel what I have read so far isn't as effective as just stating ...
0
votes
0answers
301 views

Update Duplicate record in select query of access

I have a select query (test) in access with below mentioned column ID 2561 2562 2563 2343 2561 2563 2563 Now in ID, i have duplicate records , for example here 2561 , 2562 , 2563 are ...
0
votes
0answers
68 views

How does indexes work on an Access query

I'm working on an Access 97 Db and I'm trying to speed up some low performance issues, as of right now I'm wondering how indexes works in Access. Using this table with 300k rows i get awful results ...
1
vote
2answers
8k views

convert nvarchar to int

I have a table in access linked to SQL.I am doing a query where I am using a field named comment from this table. The field is designed in SQL as nvarchar(255) . I am writing integers numbers in this ...
1
vote
1answer
1k views

Install Access Database Engine on Linux Centos [closed]

I want to read mdb file from linux centos. To read that file, we must already install access data engine. Is there any access data engine for linux? Now I'm using mdbtools to configure ODBC connect ...