Access vs SQL Server Express (1 Viewer)

tmyers

Well-known member
Local time
Today, 15:22
Joined
Sep 8, 2020
Messages
1,090
I just got to thinking and realized I did not quite know the answer to this.

At what point does your data load start to outpace Access and should logically be moved to SQL Server? I know Access can handle millions of records and just has the limit of 2G per file, but at what point would you start losing performance due to the sheer amount of data? Or at what point should you start considering the transition due to size?

I have a database that is just used for warehousing. I use my companies report writer to do a data dump of all our sales on a daily basis and then upload that to this database and after just 3 months of use, I just surpassed 4 million records this morning which triggered this thought. This databases one and only purpose is to feed my Power BI reports. Should this be something I should consider moving to SQL Server to better manage per chance?
 

GPGeorge

Grover Park George
Local time
Today, 12:22
Joined
Nov 25, 2004
Messages
1,776
The trade-offs are not just in the amount of data. To address the specific question, when an accdb reaches the neighborhood of 1.00 to 1.2 GB, i.e. well below its theoretical max, you might start to experience problems related to that size, so it's wise to keep an eye on that. For one thing, Compact & Repair operations can begin to fail as the need to process the accdb at that size can blow it up over the 2GB limit. That's happened to me a few times in one environment where we were retrieving large, but temporary, datasets from an Oracle data warehouse.

The bigger issues, IMO, are around security and processing power. Data in SQL Server can be made more secure. In some situations that's a critical issue. Processing power, such as the ability to call on stored procs for complex calculations, is also a factor.

Using the data as you do, the latter two considerations might not make so much difference, although I think you might be able to write views on your data in SQL Server that better supply aggregated data to your Power BI reports.

Look not to the specific number of records but to the size of the accdb before and after doing a Compact and Repair operation on it.
 

tmyers

Well-known member
Local time
Today, 15:22
Joined
Sep 8, 2020
Messages
1,090
Currently the file is sitting at right around 1.4G after a compact and repair which is what really made me start to wonder about it. Currently the file is just only for warehousing the data and all of the other processes are done within my Power BI report via DAX and Power Query.

Also, while I did ask a question that ended up being specific to my case, I did have hopes of a general discussion taking place and reading all of the replies and learning, so fire away! :)
 

Gasman

Enthusiastic Amateur
Local time
Today, 19:22
Joined
Sep 21, 2011
Messages
14,046
Well I think I can safely assume you do not have much of a choice anyway? :) that is if you want to keep the data, and not prune old data each time?
 

tmyers

Well-known member
Local time
Today, 15:22
Joined
Sep 8, 2020
Messages
1,090
Well I think I can safely assume you do not have much of a choice anyway? :) that is if you want to keep the data, and not prune old data each time?
True, but I could also technically create another Access file and link them together within Power BI. The problem would be that each year I could potentially make 3 different Access files to handle this, which sounds like a royal pain the butt to manage. Would make my life so much easier if I had the ability to connect my Power BI directly to my companies SQL Server and pull the data directly, but understand why that would never happen :D
 

GPGeorge

Grover Park George
Local time
Today, 12:22
Joined
Nov 25, 2004
Messages
1,776
I think SQL Server Express is a fine alternative given the situation. 1.4B is right on the border of what I would risk for an accdb used this way. One C&R could bring it down. On the other hand, if there is nothing in it except the data, all you would need to do is create a new accdb to replace it, I suppose.
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 15:22
Joined
Apr 27, 2015
Messages
6,286
Would make my life so much easier if I had the ability to connect my Power BI directly to my companies SQL Server and pull the data directly, but understand why that would never happen
As long as the DBA gave you read only access, it should not matter. The juice is definitely worth the squeeze.
 

Sodslaw

Registered User.
Local time
Today, 12:22
Joined
Jun 7, 2017
Messages
81
Not having done it myself or perhaps not the best way of doing things but as a general question to you gurus :) , is it possible in access to use 2 or more back end files and separating the tables?
 

tmyers

Well-known member
Local time
Today, 15:22
Joined
Sep 8, 2020
Messages
1,090
Not having done it myself or perhaps not the best way of doing things but as a general question to you gurus :) , is it possible in access to use 2 or more back end files and separating the tables?
To my knowledge you can have any number of linked backends to a single front end, but I am by no means an expert so someone correct me if I am wrong.
 

GPGeorge

Grover Park George
Local time
Today, 12:22
Joined
Nov 25, 2004
Messages
1,776
Not having done it myself or perhaps not the best way of doing things but as a general question too you gurus, is it possible in access to use 2 or more back end files and separating the tables?
Possible, yes. Desirable, no.

You lose more than you gain, more often than not. For one thing it's no longer possible to enforce referential integrity between tables in two different accdbs.

I would invest a little time and effort into learning how to manage a SQL Server Express database rather than futz around trying to make a multiple accdb set up work satisfactorily.
 

Sodslaw

Registered User.
Local time
Today, 12:22
Joined
Jun 7, 2017
Messages
81
I would invest a little time and effort into learning how to manage a SQL Server Express database rather than futz around trying to make a multiple accdb set up work satisfactorily.
i feel ya! thanks for the reply!
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 06:22
Joined
Jan 20, 2009
Messages
12,849
Get into SQL Server. It might take a bit of work to optimise it for your application but you won't regret it. Don't go in thinking that it is an automatic performance panacea. Performance may even fall at first if you have very complex queries. If you are using custom VBA functions as the basis for select queries you will definitely need to address that and create server side alternatives.

Once you get started, spend a few minutes every week exploring the Management Studio to see what else is in there and Google the names. In many cases, what I discovered this way led to a treasure trove of capabilities I was able to employ immediately. You will be amazed at what it can do. The querying capabilities of TSQL are incredible.

I've been using SQL Server for nearly fifteen years and am yet to imagine anything I needed to be able to do that didn't turn out to be somehow supported. At worst, the capabilities I wanted were available in later versions than I had but workarounds were often described. I'm eagerly awaiting an upgrade from 2016. There were some really sweet additions from 2017 onwards.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 14:22
Joined
Feb 28, 2001
Messages
26,999
To my knowledge you can have any number of linked backends to a single front end, but I am by no means an expert so someone correct me if I am wrong.

Yes and no. You run into limitations on the maximum number of connections. I remember that earlier versions of Access (I think 2.0) limited you to 16 DB files at once (counting the front end as 1). But newer versions may have changed that a bit. There is an absolute limit of 255 connections per DB and there are other issues regarding whether the result of, say, a UNION query across multiple BE files would result in a recordset greater than 1.9 GB. If you do the BE files one at a time, you can see a lot of data. But simultaneously open files might lead to issues in MSACCESS.EXE itself running into problems, because the main image (including code, ALL libraries, the virtual memory map, the stack. and the heap) is limited to 2 GB as well.

However, note that if you have split things across multiple BE files, you also lose relational integrity because RI only works inside a single file at a time. The pointers in the system table that maintains RI don't have room for a file slot - i.e. all files in a RI relationship must be in the same file.

You probably can have as many BE files, used one at a time, as your computer's disks have capacity to hold. However, past a certain point, all the gyrations involved in splitting attention between/among multiple files can become cumbersome to the point that an SQL Server or any SQL engine that Access can use might become a better choice. I am also not completely confident that closing one BE file before you open another will have immediate effect. I have seen other things that don't actually clean up virtual memory until the process exits, which would mean that at some point, you would blow out the MAIN image memory area.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 19:22
Joined
Feb 19, 2013
Messages
16,553
Moving your BE to sql server (express or otherwise) for performance reasons is the last reason for doing so. Almost certainly, unless you take full advantage of sql server functionality and ensure you have an extremely efficient table and query design, performance will be slower.

Agree volume of data is a valid reason, as is data security.

See this link if you are considering upsizing
 

activemars

New member
Local time
Today, 15:22
Joined
May 19, 2022
Messages
2
Not having done it myself or perhaps not the best way of doing things but as a general question to you gurus :) , is it possible in access to use 2 or more back end files and separating the tables?
You can have as many backend as you like(need). Personally I had situations with having backend in more that 10 MS Access DB( reasoning was table size) and ability to compact and repair individual backend on demand without bringing whole system down. Now I migrate lot of stuff to SQL and I have MS Access DB connected to multiple SQL backend DB + few MS Access DB as well at the same time.
 

Users who are viewing this thread

Top Bottom