Skip to main content
OverflowAI is here! AI power for your Stack Overflow for Teams knowledge community. Learn more
12 votes

SQL Database vs Multiple Microsoft Access Databases

In my opinion, Microsoft Access is an obsolete way to manage data, especially within an organization. There's a lot of limitations and lack of features compared to a modern database system, besides ...
J.D.'s user avatar
  • 37.8k
8 votes
Accepted

Compare Two Rows and Find the Difference Between Them Which is In The Second Column?

You could rephrase it like this: Return every row for which another row exists with the same Name and a different LastName which is very easy to translate to SQL if you are familiar with the ...
Andriy M's user avatar
  • 23k
7 votes

Non unique one to many relationship

What you describe is not a one-to-many relationship but a many-to-many relationship because the column Field1 is not unique in any of the two tables (and please pick better names to describe the ...
ypercubeᵀᴹ's user avatar
7 votes

Am I doing a security breach if I am connecting a MS-Access front end (multiple computers) to a SQL Server back-end?

Yes you can use MS Access as a front end to SQL Server. Its quite common. I have done this myself in the past. No, Access itself won't corrupt data. However your Access application needs to be ...
Sir Swears-a-lot's user avatar
7 votes
Accepted

Easiest way to import access db (.accdb) into SQL Server 2016?

1.Open MS SQL Server Import and Export Wizard. 2.For “Data Source” choose “Microsoft Office 12.0 Access Database Engine OLE DB Provider”. 3.Click “Properties” enter the location of the .accdb file ...
bwilliamson's user avatar
7 votes

LEFT JOIN with WHERE (Microsoft Access SQL)

In your query left join is performed only using only the condition a.ID=b.ID and then results are filtered based on condition b.lang='de'. You need to add both conditions on the left join: SELECT a....
dbilid's user avatar
  • 318
7 votes
Accepted

What principles should I follow when entering data into a complex relational database?

A foreign key relates to the primary key of another table. When the record with the foreign is entered, the corresponding record with that primary key must be present. Therefore you must always start ...
Olivier Jacot-Descombes's user avatar
6 votes

How can I allow part of a key to be null and enforce uniqueness of records with nulls?

The strongest advice I can give is to keep the primary key immutable unless it is absolutely unfeasible for your use-case. You appear to be describing 2 different datasets. User-Actions taken during ...
Peter Vandivier's user avatar
6 votes
Accepted

Bypass MS Access 2GB file size limitation

You can't bypass the built-in limitation of an Access database. You can however circumvent the built-in limitation by using built-in features like splitting the database objects between multiple *....
John K. N.'s user avatar
  • 17.9k
5 votes
Accepted

Am I doing a security breach if I am connecting a MS-Access front end (multiple computers) to a SQL Server back-end?

If they have access to the tables directly with insert, update and/or delete rights then they can corrupt your data. If you prevent this by limiting the access-rights or by an MS-Access application ...
Marco's user avatar
  • 3,710
5 votes
Accepted

Fill percentage of total field with MS Access SQL

You need UPDATE, not INSERT: UPDATE MyTable SET F = f / (SELECT SUM(f) AS fSum FROM MyTable) ; or: UPDATE MyTable AS my, (SELECT SUM(f) AS fSum FROM MyTable) AS total SET my.F = my.f / ...
ypercubeᵀᴹ's user avatar
5 votes

why the last function in msaccess allow me to bypass the group by?

What is the behaviour of the LAST function? From the documentation: These functions return the value of a specified field in the first or last record, respectively, of the result set returned ...
Paul White's user avatar
  • 85.4k
5 votes

What principles should I follow when entering data into a complex relational database?

Some general advice. In the image, I guess Keyword_Priority is dependent on Keyword which is dependent on Entry. You, therefore, need to start with Entry, then Keyword, etc. From a modelling ...
Lennart - Slava Ukraini's user avatar
5 votes
Accepted

Is it better to have multiple tables with the same primary key or one big data table?

As per mustaccio's comment, when you have the same PrimaryKey defined in each of your Tables, that then guarantees a one-to-one relationship is enforced between each Table. In other words, based on ...
J.D.'s user avatar
  • 37.8k
5 votes

SQL Database vs Multiple Microsoft Access Databases

Education: full-featured databases (which Access is not) have a thing called Indexes*. When configured, these allow you to pull data by indexed values without visiting the rest of the table. You can ...
Josh Berkus's user avatar
  • 1,206
4 votes

Non unique one to many relationship

What you appear to have is two tables that refer to a common field They have an inferred relationship In the case of a common field then create a master table for Field1 that is nothing but the ...
paparazzo's user avatar
  • 5,043
4 votes

EXEC xp_dirtree

The undocumented Stored Procedure xp_dirtree requires the sysadmin fixed server role in order to see any files when the procedure is called. Adding this role to your user test should enable you to ...
Uberzen1's user avatar
  • 678
4 votes

Fill percentage of total field conditional on some other field with MS Access SQL

Your last UPDATE statement should work. Access is often a bit crap though so although it should work, it probably won't. Thanks Microsoft Access dev team (probably more specifically the updates to JET)...
blobbles's user avatar
  • 1,621
4 votes

Easiest way to import access db (.accdb) into SQL Server 2016?

For me it didn't work to try to import directly from the ACCDB file. I had to save in Access as an mdb file and do the import that way.
Luis's user avatar
  • 41
4 votes

Compare Two Rows and Find the Difference Between Them Which is In The Second Column?

An alternative way, under the assumption that there are no repeated (Name, LastName) pairs, is the following. First select all the names that appear more than once, and create a Query out of them. ...
joanolo's user avatar
  • 13.5k
4 votes
Accepted

How to compact the current MS Access database from VBA function

This is simply not possible. Compacting and repairing a database requires the database to be closed. As such, you can't compact and repair a database between steps in a sub or procedure, since the ...
Erik A's user avatar
  • 248
4 votes
Accepted

Database software solution

SQL Server express is a good choice. You can also create a very low cost (a few bucks a month) database only on Azure. In that scenario there would be no maintenance. I would argue SQL is what you ...
Doug B's user avatar
  • 104
3 votes

VBA Maximize Access Application Main Window

Me propose this new solution, work perfectly in my application: Dim StrDbAccessPath As String, appAccess As New Access.Application StrDbAccessPath = "C:\access.mdb" '--> path of access appAccess....
Agustí Panadès's user avatar
3 votes
Accepted

Does modifying an access file copy affect original file?

No, modifying a backup file will not affect the original. Access databases are self-contained in the MDB file. Your statement "Since both files will be pointing to the same database" is incorrect. ...
Randolph West's user avatar
3 votes

Design a relational database (Access) that identifies breaches in records based on user defined limits

Initial observations are that Index Type and Index Composition are attributes of Index, not of Acceptable_indices. There will be relationships between a security and an index; whether this is ...
Michael Green's user avatar
3 votes

Easiest way to import access db (.accdb) into SQL Server 2016?

I just had a similar issue with SQL Server 2017. My setup is 64 bit SQL Server 2017 and Access 2016. It seems that SSMS is 32 bit and wants to show you the 32 bit version of the wizard. If the ...
Mike Cheel's user avatar
3 votes

INSERT INTO with UNION Alias ERROR

The short answer is: Access is weird ;) Long answer is that Access has certain restrictions in the SQL syntax it supports. It requires parentheses around some constructions, for example when more ...
ypercubeᵀᴹ's user avatar
3 votes
Accepted

MS Access replace entire table contents

As Access does not support UPSERT semantics you're stuck with something along the lines: insert ... into tbl select ... from tmp_tbl where not exists (select 1 from tbl where tbl.id = tmp_tbl.id)` ...
Grimaldi's user avatar
  • 420
3 votes

How to minimize full ms-access window in vba?

Almost, but not quite. The one you're looking for is DoCmd.RunCommand acCmdAppMinimize (minimize the Access window) Also: DoCmd.RunCommand acCmdAppMaximize DoCmd.RunCommand acCmdAppRestore
Nelson's user avatar
  • 1,689
3 votes
Accepted

What is the equivalent of MS Access InStr function in SQL Server 2008?

InStr is pretty similar to CHARINDEX. So, your query can be written as: SELECT PartID, [Description], [Service], CHARINDEX(' ',[Description]) Expr1, LEFT([...
Lamak's user avatar
  • 2,576

Only top scored, non community-wiki answers of a minimum length are eligible