Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [ms-access-2016]

42 questions
3
votes
2 answers

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 = mass.SelfID) and (Det.DDTime between mass.Start and…
mpag
  • 978
  • 7
  • 23
3
votes
1 answer

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 engine is submitting to ODBC. It goes on to give…
User1974
  • 1,517
  • 25
  • 54
3
votes
3 answers

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 by the ON syntax when removing that (the latter…
JMK
  • 51
  • 1
  • 5
3
votes
3 answers

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 errors for ANY basic VBA code, including: StrConv…
pblo
  • 31
  • 1
  • 1
  • 5
2
votes
1 answer

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
  • 218
  • 2
  • 3
  • 9
1
vote
1 answer

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 tables and the view. For the view, I created an…
Lennart - Slava Ukraini
  • 23,842
  • 3
  • 34
  • 72
1
vote
1 answer

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 is: "List the trip name, type, and maximum group size for…
rubengavidia0x
  • 135
  • 7
1
vote
1 answer

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

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 error 'Compile Error: Expected: Expression' with the…
Amber Cahill
  • 13
  • 4
1
vote
0 answers

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 to understand why. What I have discovered is a…
Pawel Jasinski
  • 123
  • 1
  • 5
1
vote
1 answer

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 has a KeywordFK field that connects to the…
Lou
  • 181
  • 1
  • 8
1
vote
0 answers

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 piece of code is generated by access 2016): SELECT…
MightyRavendark
  • 33
  • 4
1
vote
0 answers

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 the unique identifier that Access is using, some…
James
  • 11
  • 3
1
vote
1 answer

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 not being split between front end and back end.…
TheMortiestMorty
  • 153
  • 3
1
vote
1 answer

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 a solution available already or there is a better…
MightyRavendark
  • 33
  • 4
1
vote
1 answer

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 key relation to the make table's primary key.…
Riblet
  • 11
  • 1
1
2 3