Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Other Database Engines _ Stand Alone Db Options.

Posted by: JAchord Nov 4 2019, 12:35 PM

What are some good options to build a stand alone DB without access. I know SQL is the best for the back end but I would rather make something that didn't require a SQL server. Quickbooks desktop does not use an SQL server but i have not found what BE structure it uses.

On the front end what would be easiest to learn after years of using Access VB. Visual studio? I have also done some work in VB6 an c+ but that was quite a while ago. I am looking to start studying but not sure which way to go.

Posted by: DanielPineault Nov 4 2019, 12:41 PM

I don't know if this is still the case, but at one time, not so long ago, QB was running an Access database as the back-end. You'd actually be amazed the number of programs that use Access behind the scene and no one has a clue. I know of a major engineering software that everything is in Access.

To answer your question, you need to examine your product and target market. Is it mobile device, web, local desktop,... PC or MAC, ... determine your specs, then we can help point you to the right platform.

Posted by: theDBguy Nov 4 2019, 12:42 PM

Hi. If you're talking about creating a desktop application, then I think the easiest transition path from VBA is to use Visual Basic. If you don't want to use a database engine, then you'll have to create your own as well. Meaning, everything will be proprietary. You will develop both the BE and the FE to your own specification. I think that's what Quickbooks may have done back then. Now, they probably use an RDBMS as the BE for their "cloud" versions of Quickbooks.

Posted by: jleach Nov 4 2019, 12:51 PM


There really isn't much out there for "standalone" database software aside from Access.

SQL Server is an option for a database, as is MySQL and PostrgeSQL and Oracle. SQLite is a common database for standalone applications (e.g., not shared/concurrent access).

That's only the database itself though. The rest really depends on what you're after. Myself, if I had to a build a little standalone application these days, I'd go with Electron, but it really depends on your needs.

Hard to say any one language/platform is ideal.

Tell us a little more about what it is you want to do?

Posted by: kfield7 Nov 4 2019, 01:39 PM

Well there's OpenOffice Base. Last time I looked into this (a few years ago), the tools for developing a front end were not very good at all. It can work with other back ends though.

Posted by: AlbertKallal Nov 4 2019, 11:44 PM

These days, it not practical to roll your own database engine.

As for building a desktop application with something other than Access?

Well, first up, Access (the data engine) is still a very good choice to build a desktop application with.

If I was not going to use Access, then no question Visual Studio (VS) is the way the go for the desktop. And if you learn a few tricks, you find that data binding for forms is quite nice. So, you don’t have to say wire up editing of forms. So, I suggest you use the data set designer in VS.

So, you have this:

Now, if you drag the above into a VS form, you by default get a grid view, but just delete the grid. Now you can drag + drop fields onto the form. You get this:

Note the record navigation bar (with save) at the top. The above took ZERO code to make in .net! All I am saying that some of the features in .net will and can give Access a run for the money IF YOU learn how data binding in .net works. (It is a bit tricky at first, but using datasets and databinding abilities really is quite nice). So this ALSO means that you REALLY do want to use some kind of database here, since then just like Access, the .net forms can consume data, and do so without code.

And if you coded in VBA, then to shorten your learning curve, use So, once you learn a few “tricks” in terms of how data binding and data bound forms work in, you find you can build forms rather fast, and you spend very little time wiring up forms to work with data.

However, what database engine or system to use? As noted, it makes no sense to roll your own.

As a side note, QuickBooks uses Sybase (that data engine is the same one Microsoft purchased for SQL server). So, Microsoft SQL server is a fork of Sybase. To my knowledge, QuickBooks thus uses Sybase but since 2013, they allow Oracle, MySQL, Sybase and SQL server. So they now are quite flexible in this area.

However, Simply accounting at one time is the popular accounting package that used the Access database engine (JET). (Simply Accounting is now Sage Accouting).
So it was not QuickBooks as noted here that used Access/JET. However, the popular Simply accounting (now Sage) some years ago switched from using JET/ACE (the access database engine) to now using MySQL.

Both simply and QuickBooks accounting systems now use server based editions of some kind of SQL server. Even in single user mode and stand-alone installs these packages now use some kind of SQL server based system for their database engine.

So, the typical data engine choices for a desktop application are:

JET/ACE (the access data engine). If you use mdb file format, then you don’t have to install anything since JET is and has been installed as part of windows.

ACE – you can use this one, but you have to install it, or include the ACE connectivity compounds as part as your install. It still a nice choice. (Because then Excel, or word can pull data)

SQLite – this is VERY popular (open source). This is a non multi-user and file based database. It is similar to Access (JET) since it is file based – not service based. I started using this database for ONLY one reason: It is the near top choice and de facto choice for databases on Android phoned. So, if you build an android phone application, then the top used database is SQLite. So, if you desire is to transfer or sync data from your phone (or even transfer a whole database), the SQLite is a great choice.

In fact, there is an ODBC driver, and I have even used SQLite from Access. There are versions for Linux, Apple Mac, Android, windows and iOS (apple phones).

Because SQLite been implemented on so many platforms, I will say that while in the 90’s the Access database engine (JET) was VERY typical and popular used in accounting packages, VB6 applications that required a database, and many more.

Today that space is certainly owned by SQLite.

Today, quite much the “first choice” by developers for a file (non server) based system is SQLite. I would say this database is the JET of our time. Very popular. Much of this popularity is of course due to it being supported well on Android phones. The install files are just a few ‘dll’s, and in fact you don’t really “install” the files but just include a few rather VERY small .dll’s. Hands down this is the most hassle free deployment engine right now.

SQL Compact edition
Now this is a possible choice for desktop, because it is a FILE based version of SQL from Microsoft. This product started back in the windows CE days (when Microsoft was making portable hand held computers – and then phones based on this system). So, SQL Compact edition is VERY similar to JET and SQLite, in that this database has small memory use, small footprint, does NOT run as a service. Like SQLite, only some binary files in a folder is required. So, deployment is VERY easy. The other advantage is the data types and columns etc. are the same as SQL server. And the SQL syntax is 100% the same as SQL server. I have not kept up with the status of SQL CE, but it still would be a possible choice.

Next up:
SQL Express
SQL express is a “lighter” weight edition of SQL server, and is free. This choice is STILL a very good choice. In fact, I likely would use this option EVEN if I was building a windows stand-alone desktop version. There are several reasons for this, but one BIG reason is that if you THEN wanted your desktop application to become multi-user, then you could turn on the TC/IP ports of Express and then simply install your desktop application on other computers, and your application is now multi user. And also better is you could migrate your database to the cloud, and then again install your desktop application on any desktop with an internet connection, and again you are off to the races.

Note that SQLite, and SQL CE are NOT multi-user, but JET/ACE remains the ONLY popular “file based” engine that supports multi-user, and does so without a “server” or “service” running. So, Access databases can be multi-user, but with network issues these days, the sun is fast setting for using file based systems in mutli-user mode.

Now, I am a bit biased here, because for the last 6+ years, all my Access work, all my work, and all my work been using SQL server. So, it’s kind of like if the hammer fits, then I will use it.

So, say in the 1990’s, building a desktop applications quite much suggested you were going to use the Access database engine (JET). And a good many products also used the dBase format. (And specific Borland’s database engine (BDE). It’s still kicking around these days too, but I not seem much posts or activity in this regards.

However I have noticed that for desktop ONLY installs?
QuickBooks - Sybase SQL server, MySQL, SQL server, oracle.
Simply Accounting
(Now sage) - MySQL

Navision (Microsoft accounting – rivalry Great Plains)
SQL server

So we see a bit of a trend here of using the somewhat lighter versions of server based database systems for even SINGLE user desktop systmes.

If I wanted the smallest install, most simple? I would consider using SQLite – it is crazy small and the install is really not required.

But, anything beyond a file based system? I would go with SQL express, since there are versions in the cloud, and that is the direction of our industry. There are also versions now for Linux.

So, I can’t think of ANY desktop application that has to edit and report on data that does not use SOME form or SOME kind of database engine – they all do.

And, Access/JET/ACE is still a possible choice here too! And this is especially the case when JUST starting out, since it one less database product and system to learn. Same goes for using as compared to c#. 10-13 years ago, it is stunning how popular was. Even up to about 8 years ago.

These days, quite much most .net developers are adopting c# as opposed to So the adoption rate for is really low right now. I have been lucky, since all the work for customers in regards to supporting and working on .net programs? Well, they all just happened to be, including ones were I was not the original developer. So just like some Access application that “gets” into trouble, and they need a seasoned developer? Well I had the same thing happen to me in regards to .net.

Without question Visual Studio is the way to go. It opens up doors for web development. And again I am doing some really stunning work in this area. I should point out that the web is REALLY where things are right now. So a jump to learning .net and say dipping one’s toes in desktop development will get you comfortable with VS, and then you can parlay that into jumping on the web development bandwagon. So this ability of Visual studio to do desktop development and THEN jump to web development is perhaps the #1 reason to adopt Visual studio over other systems right now.

Writing code and writing VBA code is really very similar. (This is despite what the nay Sayers say). The learning curve for was such that I made a decision that I will learn c# later on, but I had to reach a point in which I could be productive as fast as possible to REACH a point in which commercial work was viable for me. So, allowed me to start coding near instant. If you try and learn .net and C# at the same time, the learning curve is too steep.

How do you eat an elephant?
Answer: One bite at a time!

So, .net can be overwhelming, but with my VBA skills, then jumping into was near instant.

In summary:
Yes, you use some kind of database system here, and yes even Quick Books does. Now BEFORE Quick Books used Sybase (server), I not sure what they used. Some suggest it was propriety, but I think it always used some kind of version of Sybase, but I really don’t know.

Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada

Posted by: JAchord Nov 5 2019, 12:11 PM

I have two issues with access. One is DB size limits. I have one front end and had to use three back ends to keep from going over the two gig limit. Just one of them is pushing 1.8 gigs after compacting. The other two are half gig each. Then the other issue is that access DB are not secure. Even with the encryption it is easy to get to. The passwords are stored as text and if you have the correct program you can extract it.

The company I work for has 29K employees and hates back office programs. It has Microsoft SQL license but it could take a year or two for IT to approve a new instance. I made a power apps app that they have been reviewing over a year now. I am fairly certain that no one knows how to program (coders yes, Programmers no) themselves so it just gets shelved. They hate back office programs because if someone leaves then no one understands how to fix issues when they popup. My boss the VP of the division is all for the program, so I am looking for alternatives. One thought was that i develop something standalone on my own time and sell it to the company with a contract to maintain it if i leave.

So after reading through all the information I think I will explore using and SQLcompact 4.0 looks to be the replacement for SQL compact ce. I hadn't found that one in my SQL research before so thanks again for the help. Probably not as fast and durable as a true SQL server setup but should work. Looks like i need to do some reading and experimentation.

Posted by: jleach Nov 5 2019, 12:18 PM

SQL Express is free and supports up to 10GB. The compact editions are usually for web stuff, but they're no less stable than the other MSSQL editions, just different features toggled. You can continue to use Access as a frontend.

It's a common scenario you describe, management not approving of back-office applications (for valid reasons, from their standpoint).

I think writing it in something other than Access doesn't necessarily resolve that. Writing a custom project and licensing it with support can be appealing to management for some reasons, but they typically still have the "what if he's not around anymore" questions. If anything, it's a slightly more formal approach to doing essentially the same thing.

My 2cents. Cheers,

Posted by: AlbertKallal Nov 5 2019, 07:08 PM

Nothing to add to Jacks fine post.

>>The compact editions are usually for web stuff,

Quite sure compact is a FILE based version. - its not server or service based. (so I don't think it used nor recommend for web stuff). It might have been used for maybe file uploads, but compact edition of SQL is not a server based edit. Near all web hosting systems include MySQL, or if windows based will give you a instance of SQL server edition to use. But the CE version is file based - it roots are from windows CE.

Not a huge deal - would love to be corrected on the CE edition being a choice for web stuff. (not aware of any web hosting having this as a option - in fact you still find a good number offering JET/ACE support --- but they are still file based and only for very light workloads).


Posted by: AlbertKallal Nov 5 2019, 07:14 PM

I will explore using and SQLcompact 4.0 looks to be the replacement for SQL compact ce.

Yes, it is a possible choice. Note that the ONE big reason I don't use SQL compact is it does not have a ODBC driver, and thus can't be used with Access. This is REALLY too bad since they are sync framework providers for SQL CE, and that would be SUPER for applications in which you migrate Access back end to SQL CE. It would be file based on your laptop, but you could then sync the data with SQL Azure - and all of the code and work is done for you. Just have to wrap up a COM objects that you call + use from Access and we sould have a FANTASTIC off line, on line, and sync to SQL server. But, without ODBC, then you can't use SQL Compact Edition from or with Access.

Funny how no ODBC driver is available for use with SQL CE - it would seem this choice thus prevents SQL CE from stepping on JET/ACE toes!
Other then no ODBC driver, SQL CE is great choice for desktop and .net.

Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada

Posted by: jleach Nov 6 2019, 05:32 AM

Albert - regarding CE, you may be correct, I don't do much more than brush shoulders with it from time to time, so I'm likely mistaken, or very least misinformed.

I wonder how CE compares to SQLite (which is a superb tool, where it fits the need). I should look into that...

Posted by: JAchord Nov 6 2019, 02:34 PM

Well one problem that I see with both CE and Express is that I can't mount the BE in a server folder and have multiple instances of the front end open like I currently do in access. At least that is what I have found in older post. Not sure if that is still true in current versions.

I could migrate the back ends to share point. The company does have a license for that. Not sure if that would be quicker than a server folder or not, having never used this option. Using the server folder over a VPN connection is almost useless since the lag is so great. I ended up writing a code to copy the backed to my laptop everyday at lunch if connected to the server and then using the table linker to switch to the local copy and opening the front end as read only so I can not accidentally make changes that are not recorded. A real pain in the rear, but at least i can use the data still. i do not have it set up this way for other users.

Posted by: AlbertKallal Nov 8 2019, 12:06 PM

I wonder how CE compares to SQLite

The main difference is that the SQL compact edition has much better support from Visual Studio, and is a Microsoft product designed to be SQL compatible with SQL server. So, no stored procedures exist, but the SQL syntax and functions is really going to be the same as SQL server.

So both SQLite and SQL compact can thus be deployed with just a few VERY small .dll's included in your project. If one is going to work with Android, then SQLite is the best choice.

As noted, if you recall project Huron for Access? Well, that was a .net sync provider for Access to SQL server. I really wish they released this/that code. This would allow use with GREAT ease to sync a Access database with any version of SQL server (including free editions). So one REALLY nice feature of SQL CE is that the .net sync framework has a sync provider already built. And this is not SQL replication.

This means that you don't need or are using SQL replication. With a sync provider, then you can bi-directional sync the SQL CE database with any server edition of SQL server. The huge disappointment of course is there is no ODBC driver for SQL CE. So, using from .net is fine (oleDB providers), but without ODBC drivers, then we can't use SQL CE for a back end with Access.

SQLite does have a ODBC driver, but then the .net support is not all that great (you can use the ODBC provider in .net I suppose). But no .net sync provider means SQLite is of little use with Access if one wanted to build a off-line/on-line mode for Access. And of course SQLite has more limitations in terms of the SQL syntax you can use against the database.

All in all, for a portable hassile free embeeded database for use with .net? SQL CE is the choice hands down But, if they could release a ODBC driver?

Well, then you migrate the Access back end to SQL server. You then pull it into SQL CE. You then link your Access FE to this SQL CE. It is smaller then JET/ACE, and has no deploy issues. You then grab the SQL CE sync provider for .net. At this point, you have a off-line, on-line Access database that can sync with SQL express, or even SQL Azure in the cloud, and all of the required bits and parts are pre-made and available to .net developers. The ONLY thing missing is the ODBC driver, and this means we can't use Access with SQL CE. As I stated, I suspect this is by intention to not step on the toes of JET/ACE.

Project Huron code was/is ready to go 10 years ago in 2009. I never really had a chance to bug/pressue/suggest/hint to the Access team to release this sync provider. I still wish they would!. Now that the sync framework is being depreciated, then little incentive exists for project Huron and a sync provider for Access. This would give Access folks a SharePoint like experience for off-line and on-line sync for Access.

Project Huron for Access:

i have time-stamped the above video to start at the correct place to save you time. You only need watch 2 minutes. Note the mention of Access. This is from PDC (developer's conference).


Posted by: JAchord Nov 12 2019, 11:35 AM

do CE or express allow multiaccess or should I just consider migrating to share point? I have seen some say that they do and some say they do not.

Posted by: theDBguy Nov 12 2019, 11:50 AM

QUOTE (JAchord)
do CE or express allow multiaccess or should I just consider migrating to share point? I have seen some say that they do and some say they do not.
No clue about CE, but SQL Express does. I would recommend SharePoint as a last option, after other available RDBMS.

Posted by: AlbertKallal Nov 12 2019, 08:26 PM

CE is like SQLite. The are file only based and NOT muti-user.

So for a file based (non server) choice and the need for mutli-user?

ACE/JET - the access database engine is the only choice. I suppose Fox Pro engine, or Borlands DBE engine (that works on dbase III file formats is a possible). Don't know if FoxPro, or borlands data engines are available any more.

of course SQL Express is full mutli-user, and it really has the most complete features, runs really fast, and is 100% compatible with the full blown version of SQL server. Max allowed File size is a reasonable 10 gigs. No question that SQL express is my number one choice here. It great for one laptop, or having 50 users at the same time hammer away at it all day. Really a fantastic product, and one that I near always use with Access as a front end. High recommend if you want to jump beyond the "simple" and "easy" deployment of a application that uses some file based system.

The file based systems are REALLY nice since they as a general rule require zero setup by the customer, and thus for deployment or commercial applications, then file based systems are a great choice.

However, once you go multi-user? Then for say 1-15 users, Access can't be beat.

Anything else after these "lower" deployment choices, then hands down SQL express is the choice.

Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada

Posted by: FrankRuperto Nov 13 2019, 01:35 AM

We have a very happy customer using Access frontends linked to a (pg) backend, which is also shared with a customer web app. This db is an industrial-strength multithreaded database server and its free open source. It didn't take long to get familiarized with its SQL syntax and features. Most db servers are basically the same. I have already worked with Informix, Oracle and PostgreSQL, but surprisingly just tinkered with SQL Server. Can someone confirm if SQL Server Express only does single-threaded processing and limited to one CPU core?

Posted by: AlbertKallal Nov 13 2019, 02:03 AM

SQL express is limited to one socket, and that socket can have up to 4 CPU's (4 logical processors reported).

So, the rating is somewhat confusing, as it current rating is this:
Limited to lesser of 1 Socket or 4 cores.

So, if you have 4 cores, in 4 sockets, it should use them all. Or if you have one CPU socket with 16 cores, then it will still only use 4 of them. So the way I read this is regardless of multiple sockets each with many cores, it going to use 4 max.

It will also limit memory usage to 1 gig of ram. Several clients are running in the 40-50 users range with this setup. It runs just fine.

However, in one instance, they may well jump from express to standard. That version is not free, but is rated for "Limited to lesser of 4 Sockets or 16 cores"
So, once again, that looks to be 16 max threads. and ram jumps to 64 gigs.

So, while by today's standards, the Express edition is limited, but to hardware and what we had a few years ago, it's rather nice.

I would say processing is not usually an issue, but the limits of 1 gig ram with lots of users will come into play here.

As noted, Express edition allows 10 gig max file sizes. Again, quite nice.

So, yes, one could think as express edition using up to 4 CPU's here. I don't know if this translates into max allowed threads.

Like my choice of using Hyper-V over say Virtual box, or VM-ware? Well, I use hyper-v on my laptop (it free for windows 10), and that translated into being able to use and feel comfortable with clients of mine running some really big iron huge servers. Those big servers ALSO use hyper-v on them. it is the same product from Microsoft.

So, its a transferable skill set. Same goes for SQL express. Customers of mine tend to run that system, so once again having learned SQL express means I am comfortable working on their full blown SQL server systems. I mean, VMware, or brand "x"of some SQL server like MySQL or PostgreSQL are great choices - but since the probably is high that my clients will be running SQL server, or hyper-V, then my choice is based on what choice results in the most market driven transferable skills for me. So some of the other choices might even be better, or even perhaps less cost, but what my customers are using quite much dictates what systems and skill sets I will learn. So, I am rather market driven to a point when I am faced with say 2 or 3 good choices. They are all good, but which one can i translate into sell-able skills to such customers will tend to pull me in that direction.

So, in fact all of the sites I worked on all had SQL server running. So, having great SQL server skills been a huge benefit to me.

Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada

Posted by: cheekybuddha Nov 13 2019, 03:34 AM

As I understand it (though I have not tested it!)



Posted by: jleach Nov 13 2019, 05:32 AM

I love SQLite, but generally only use it to back single-process applications.

There was one project we used it on where an application and a server both utilized the same sqlite db.

From their When To Use page (

SQLite supports an unlimited number of simultaneous readers, but it will only allow one writer at any instant in time. For many situations, this is not a problem. Writers queue up. Each application does its database work quickly and moves on, and no lock lasts for more than a few dozen milliseconds. But there are some applications that require more concurrency, and those applications may need to seek a different solution.

Posted by: AlbertKallal Nov 13 2019, 02:38 PM

Interesting. As noted I have SQLite, linked tables from Access, and am using SQLite on a Android application I am building.

So, great little database. As I stated, it really is the popular 'JET like database of our time. Really nice, and deployment does not even require a install.

However, I was not really aware that it allows multiple people (or process) to open at the same .

So, it a little more JET like then I realized.

Appreciate the heads up!


Posted by: JAchord Nov 19 2019, 04:23 PM

For some reason I was thinking SQLLite was an MS product. Guess I was confusing it with express. That would probably work 90% of users are read only anyway. Is this difficult to interface with access? I would assume that an MS product would be easier.