Skip to main content
Share Your Experience: Take the 2024 Developer Survey

Questions tagged [ms-access-2016]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0 votes
1 answer
34 views

odbc--call failed. [Microsoft][ODBC SQL Server Driver][SQL Server]A collation conflict ... in the UNION ALL operator ... cannot be resolved. (#451)

I union all the distinct columns of the INFORMATION_SCHEMA.COLUMNS views of many databases which I query with a direct query: select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME from xyz ...
questionto42's user avatar
0 votes
1 answer
546 views

Data Type mismatch error when using function in SQL Statement

I have a function which gives Levenshtein Distance used for fuzzy search of a field. Option Compare Database ' Levenshtein3 tweaked for UTLIMATE speed and CORRECT results ' Solution based on Longs ' ...
Knightowl3128's user avatar
0 votes
1 answer
35 views

Using “OR” in SQL [closed]

I am new to sql and today my manager asked me to retrieve some customres informations using their customer ID so i used the following query: SELECT * FROM Customers WHERE CustID = “11023” or CustID = ...
Omar's user avatar
  • 3
1 vote
1 answer
286 views

Unable to update SQL Server via Access, Reserved error (-7776)

I'm trying to use Access as a front-end for an SQL Server 2017 database. The objects I'm interested in are 6 temporal tables and a view joining them. I created an ODBC user data source and linked the ...
Lennart - Slava Ukraini's user avatar
1 vote
1 answer
323 views

3 Simple Queries SQL Student Question, Query Errors in Microsoft Access

The First Question is: "List the name of each trip that does not start in New Hampshire (NH)." SELECT TripName FROM Trip WHERE State!='NH'; /* works with State='NH' */ The Second Question ...
rubengavidia0x's user avatar
1 vote
1 answer
977 views

How to refer to Field Name with a ? in it in MS Access VBA code [closed]

Simple question, but I am new to databases. I have a field called 'ActorRetired?', and in some VBA code I need to set the value of it to -1. But When I try: Me.ActorRetired?.Value = -1 I get the ...
Amber Cahill's user avatar
1 vote
0 answers
56 views

how to stop ms access 2016 from connecting back to microsoft?

I have a small program which compacts access database using DAO COM api. The program works, except around 50% of the time hangs on exit for about 15 seconds. So I have decided to investigate and try ...
Pawel Jasinski's user avatar
0 votes
1 answer
147 views

Cannot login to a MS Access database

Below is some VBA code to authorize users to login into an MS Access database. I am trying to fix this code and I don’t understand what is the issue in this code snippet. Private Sub cmdLogin_Click() ...
KCR's user avatar
  • 7
0 votes
1 answer
3k views

How to convert columns into rows in MS Access [closed]

Please help me to understand, how to convert columns to rows in MS Access. I have the below table, I would like to convert as the below result table. NoOf_A NoOf_B NoOf_C NoOf_D 200 300 400 ...
KCR's user avatar
  • 7
0 votes
1 answer
109 views

How to create columns with Count and percentage using MS Access Query

I have a data table below, I would like to create a query to produce a result table like below, with count and percentage, using MS Access Query. I am unable to create this. Name Alex Alex Bob Bob ...
KCR's user avatar
  • 7
0 votes
0 answers
41 views

How do I link two Access Tables so if one Table gets updated both will?

I have an access database with a table that is linked to an excel sheet. I also have a duplicate table with the same data linked to a postgres database. My goal is to either link these two tables in ...
Paul Sender's user avatar
0 votes
0 answers
46 views

How can you effectively visualise a complex relational database?

I have an Access database which contains all the data from an open-source Japanese dictionary, JMdict. The database contains 24 linked tables, so reading it is complicated. The reason for this is that ...
Lou's user avatar
  • 181
0 votes
2 answers
418 views

What's an alternative solution to using a null value for a foreign key field?

I have a few tables in my database which contain a Foreign Key field linking to another table. The problem is, the value for that field isn't always going to be present, and so sometimes I might have ...
Lou's user avatar
  • 181
1 vote
1 answer
676 views

Is there any practical difference between using the Lookup Wizard to create a FK relationship, or using the Relationships window?

I'm very new to Access, so I'd appreciate patience if this is a basic topic. I'm creating a lot of tables which I need to link. I have two tables, Keyword and KeywordOrthography. KeywordOrthography ...
Lou's user avatar
  • 181
0 votes
1 answer
103 views

how to professionally normalize my database - screenshots included

Hello database administrators. I got some excel spreadsheet with data of "my customers" and I'd like to create professionally designed database out of it. Can anyone tell me how this data should be ...
michal roesler's user avatar
0 votes
2 answers
21 views

Table with two customer IDs that I want to relate to the entity

Let's say I have a transaction where Party1 and Party2 are associated, eg by a transaction - let's say Party1 sells something to Party2. I have a list of transaction IDs, eg transaction1 is Party1 ...
Tracey's user avatar
  • 1
0 votes
1 answer
672 views

Unable to use today's date as a criteria in a query

Ok, been a good decade since I've worked with Access and SQL, so a little forgiveness please. Short version - when I add the query criteria to only display rows which match today's date, the query ...
thewinchester's user avatar
0 votes
1 answer
123 views

Trigger Access table on Insert

I am using Access 2016 and building an in-house HR app for my team. I have a Position Table, an Employee Table, a Position_Employee Table and a Staffing Table Position Table ------------- ...
DrD4rk's user avatar
  • 33
0 votes
2 answers
2k views

Tables not showing up when linking SQL Server to Access using ODBC connection

I am learning to link the back end of a database in SQL Server express 2016 to the front end in Access 2016. The tables were originally in Access and then I exported them to SQL Server. Now I am ...
Shafin Islam's user avatar
0 votes
1 answer
16k views

Select ONLY Max(date) when there's repeating dates

Here is a sample of the data contained in my Tracking table: tracking_id date_action action_id staffing_id ------------- ----------- ----------- ----------- 1 2019/03/04 4 ...
DrD4rk's user avatar
  • 33
0 votes
1 answer
42 views

One primary key in two tables

I am first timer with ms access and also here I am working on a database of library. I have two different tables which stores details one for books and others for Periodicals. How can make ...
Taqi's user avatar
  • 3
-1 votes
1 answer
42 views

Duplicate primary key

I am new in database and ms access.I am developing a database of income tax return form submitted by several taxpayers. Where each tax payer has an unique ID called ETIN (electronic taxpayer ...
Learner's user avatar
  • 99
0 votes
1 answer
1k views

Linking tables in Access with unique ID

I am creating a database in MS Access that has 3 different tables with 3 separate types of information for one set of users, Personal, Business and Board. I have one table created with all the users ...
Dennis Villa's user avatar
0 votes
1 answer
639 views

How Do I Query this Data in Access Without Creating a Circular Reference

I have two tables called Events and EventSeasons. Events define the main type of events that there can be, and EventSeasons stores the number of times that the events have occurred yearly. I'm trying ...
David's user avatar
  • 13
0 votes
1 answer
5k views

Microsoft Access is giving me network interrupted errors

We have just upgraded to window 10 and are using Microsoft Access 2016 (16.0.8431.2110) 32 bit. As a result of this upgrade we now have the issue of intermittent network connectivity issues. My ...
paul davey's user avatar
1 vote
0 answers
313 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 ...
MightyRavendark's user avatar
3 votes
1 answer
3k views

multi-condition left join conditions in MS access

Is there another way to write the following poorly performing in Access (Jet) SQL? SELECT * FROM Det left join (select * from Inv inner join Dep on Inv.SN=Dep.SN) as mass ON (Det.RecSN = ...
mpag's user avatar
  • 968
1 vote
0 answers
762 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 ...
James's user avatar
  • 11
0 votes
1 answer
838 views

MS Access M:N Relationship query: exclude records with more than the requested values

I have a little problem when looking up records that have m:n relationships to other tables and therefore appear multiple times in link tables. I try to show it with a minimal example here: Let's ...
MightyRavendark's user avatar
1 vote
1 answer
37 views

Would db corruption be further minimized if I remove querying on a backend table from the user's front end?

I have an MS Access database that was being used by 30 people. They all use it at different times of the day, but within any 8 hour block of time there might be 10 people using it. This database was ...
TheMortiestMorty's user avatar
2 votes
1 answer
43 views

Store long number in MS access

How can I store 12 character long number in MS Access 2016, if the field cannot be empty?
Zsmaster's user avatar
  • 218
1 vote
1 answer
234 views

Compare all attributes in M:N relationship

I am new to database development and I'm sure my current problem is quite common. Unfortunately I couldn't find a solution yet - mainly because I'm not sure how to name it correctly ;) So if there is ...
MightyRavendark's user avatar
3 votes
1 answer
2k views

Trace ODBC commands: What is the registry path for TraceSQLMode and where is SQLOUT.TXT?

In the Optimizing Microsoft Office Access documentation, it mentions that we can: ...edit a Microsoft Windows registry setting that allows you to see the commands that the Office Access database ...
User1974's user avatar
  • 1,467
1 vote
1 answer
708 views

MSAccess: creating a new row in a related table and linking it back to the "master" table

In a hypothetical 'cars' database, assume there's a model table and a make table: models modelID int model varchar make int makes makeID int make varchar Model's make column has a foreign ...
Riblet's user avatar
  • 11
0 votes
1 answer
49 views

How to query for something like "last active" per column in Access

Let's Say i have two Tables, let's call them PC-Info | ID | Hostname | Location | |:---|----------:|:-----------------:| | 1 | Hostname1 | This Room | | 2 | Hostname2 | That Room ...
Laurenz ebs's user avatar
0 votes
1 answer
387 views

After running VBA, I lose exclusive access to the database

I wrote some VBA to import an excel spreadsheet easily into a table in my database. This way, the user just clicks a button on a form, and answers some questions about the file to be imported. The ...
MGaddict's user avatar
1 vote
1 answer
313 views

Relating two fields of one table to one field of another

I have two tables that look as such: Table1 Table2 ID | Appname ID | App | Dependency ------------ ----------------------- 1 | App1 1 | 2 | 3 2 | ...
Tim S.'s user avatar
  • 113
1 vote
0 answers
74 views

Create a column in a table based on the rest of the row and other table

I have two tables, and I'd like to select a range of positions from one table for each row in the other table. I've tried to relate the tables in order to get new information but it seems like there ...
Julio Maldonado's user avatar
3 votes
3 answers
6k views

JOIN ON syntax error in Microsoft Access 2016

I am querying a couple tables, and for some reason Microsoft Access 2016 continually informed me that my SQL query JOIN ON syntax is incorrect. Specifically, the JOIN syntax is highlighted, followed ...
JMK's user avatar
  • 51
3 votes
3 answers
10k views

Access 2010-2016 Compatibility Error

I was just updated to Access 2016, while my users are still at Access 2010. I opened our database in MS Access 2016 once, didn't make any changes, and closed it out, and now all the users are having ...
pblo's user avatar
  • 31
0 votes
1 answer
7k views

Query showing a number instead of the column value

I have a query (query_clients) based on a table (Person). One of the columns I am pulling is a StateAbbr column, which is a lookup to the StateAbbr table. The primary key in the StateAbbr table is the ...
kgrafton's user avatar
  • 101