SQLITE and Access

[Mylton]

Goodnight
If my FE is access and the BE is SQLite, what would I need to know?
What is the advantage disadvantage?
It's possible?
If so, how do I make the connection?
Do I have to install ALL SQLote where it will run?

Thank you to anyone who can inform.
Thanks

 

[jdraw]

Mylton,

Google "sample msaccess front end with sqlite backend" and you will find some info. I am not a sqlite user so cannot offer direct experience. If sqlite supports ODBC, and I'm sure it does, then that's probably a big player in your setup. Let us know your progress.

 

[arnelgp]

many have tried, but failed.

 

[Mylton]

many have tried, but failed.

Resultado da tradução​

The idea would be to have a BE that supports more data and doesn't need to install everything like SQL. From what I read, SQLite wouldn't need it. It's because I don't have the knowledge that you have that I have this doubt. Could you explain... please

 

[arnelgp]

i never experimented on sqlite so i cannot answer you.
i also have tried before, but cannot find the correct odbc driver.
if you will google, you will see that many people also tried but never get any results.

 

[GPGeorge]

Goodnight
If my FE is access and the BE is SQLite, what would I need to know?
What is the advantage disadvantage?
It's possible?
If so, how do I make the connection?
Do I have to install ALL SQLote where it will run?

Thank you to anyone who can inform.
Thanks

Have you consulted the SQLLite website?

It includes this statement, which suggests you may not get the results you expect.

2. SQLite Is Embedded, Not Client-Server​

Whenever comparing SQLite to other SQL database engines like SQL Server, PostgreSQL, MySQL, or Oracle, it is important first of all to realize that SQLite is not intended as a replacement or competitor to any of those systems. SQLite is serverless. There is no separate server process that manages the database. An application interacts with the database engine using function calls, not by sending messages to a separate process or thread.

The fact that SQLite is embedded and serverless instead of being client/server is a feature, not a bug.

Client/server databases like MySQL, PostgreSQL, SQL Server, Oracle, and others are an important component of modern systems. These systems solve an important problem. But SQLite solves a different problem. Both SQLite and client/server databases have their role. Developers who are comparing SQLite against other SQL database engines need to clearly understand this distinction.

See the Appropriate Uses For SQLite document for additional information.

 

[Mylton]

​

i never experimented on sqlite so i cannot answer you.
i also have tried before, but cannot find the correct odbc driver.
if you will google, you will see that many people also tried but never get text chatgpt

Text chatgpt

When you link SQLite tables to MS Access using the Microsoft Access Database Engine (ACE), you will need to ensure that the same Open Database Connectivity (ODBC) driver is installed on all other PCs where you want to use the database. Here is a general guide on how to do this: 1. **Install Microsoft Access Database Engine (ACE)**: - Make sure Microsoft Access Database Engine is installed on all PCs where you want to use the linked SQLite database. 2. **Configure the ODBC Data Source**: - You will need to configure an ODBC data source for the SQLite database on each PC. - On Windows, you can do this by going to "Control Panel" > "Administrative Tools" > "Data Sources (ODBC)". - In the "System Data Sources" tab, click "Add" and choose the "SQLite3 ODBC Driver" driver. - Follow the instructions to configure the ODBC data source for the SQLite database file. 3. **Linking Tables**: - After configuring the ODBC data source, you can follow the same steps described previously to link SQLite database tables to MS Access on each PC. By following these steps, you will be able to use the linked SQLite database in MS Access on other PCs, as long as the ODBC driver is configured correctly. Be sure to include clear instructions for configuring the ODBC data source when distributing your application to other users. ..........
. Would the path be the same?

 

[arnelgp]

Text chatgpt

i am not sure, chatgpt, on most occasions, don't know the answer.
you need to Test and verify it's answer.
there is a suggestion on post #6 and another on post #2, so you can always google for real people's experiences.

 

[GPGeorge]

​

Text chatgpt

When you link SQLite tables to MS Access using the Microsoft Access Database Engine (ACE), you will need to ensure that the same Open Database Connectivity (ODBC) driver is installed on all other PCs where you want to use the database. Here is a general guide on how to do this: 1. **Install Microsoft Access Database Engine (ACE)**: - Make sure Microsoft Access Database Engine is installed on all PCs where you want to use the linked SQLite database. 2. **Configure the ODBC Data Source**: - You will need to configure an ODBC data source for the SQLite database on each PC. - On Windows, you can do this by going to "Control Panel" > "Administrative Tools" > "Data Sources (ODBC)". - In the "System Data Sources" tab, click "Add" and choose the "SQLite3 ODBC Driver" driver. - Follow the instructions to configure the ODBC data source for the SQLite database file. 3. **Linking Tables**: - After configuring the ODBC data source, you can follow the same steps described previously to link SQLite database tables to MS Access on each PC. By following these steps, you will be able to use the linked SQLite database in MS Access on other PCs, as long as the ODBC driver is configured correctly. Be sure to include clear instructions for configuring the ODBC data source when distributing your application to other users. ..........
. Would the path be the same?

Agreed, you should consider responses from ChatGPT with sufficient skepticism. They can be in the same general neighborhood as the truth, but they are not necessarily at the right address in that neighborhood.

Also, one of the best ways to find out if something works it "try it and see what happens".

 

[Pat Hartman]

The idea would be to have a BE that supports more data and doesn't need to install everything like SQL. From what I read, SQLite wouldn't need it.

Do you think you could run Word without installing it? Can you run Access without installing it or its Runtime version? Of course you need to install it AND you may also have to install the ODBC drivers it needs since it can't use any of the defaults installed with Access.

Software doesn't live in the either .

If you are looking for "free" then use SQL Server Express. It supports 10G. You have ONE install - on the server. If you don't use any data type introduced after ~ 2005, you can get away with using the ODBC driver that is installed as the default for SQL Server. Otherwise, you will need to install whatever ODBC driver you need on EVERY PC.

How much data are you expecting? ACE doesn't have row count limits. It is simply total size. I have ACE tables with more than a million rows. And if you are willing to code your own RI, then you can even use multiple BE's if you have multiple tables that approach the 2G max for Access. So, essentially, no single TABLE can exceed the 2g limit if you are using ACE as the database engine because you can use as many BE's as you need.

 

[DickyP]

I did run SQLLite Access a while back when I got fed up with the limitations (from my point of view) of the excellent - eg, free - Money Manager Ex package and wrote my own Access front end which did things the way I wanted (and would probably not suit others peoples needs). To 'save time' I initially used the SQLLite BE that came with MME using ODBC.

It worked pretty well with ODBC but was a pain to administer so I migrated to an Access BE. The biggest problem I had was the ODBC driver at the time didn't implement the UNION SQL clause so any Queries that used UNIONS had to be Pass-Through.

This advice/experience may now be inaccurate as it was using Access 2003 on a Windows 8.1 PC.

 

[Mylton]

good morning
I'm sorry for the delay. But he was busy at work.
I read carefully what everyone said and I really appreciate the tips, advice and support.
Thank you all


bom dia
Desculpe a demora. Mas ele estava ocupado no trabalho.
Li atentamente o que todos disseram e agradeço muito as dicas, conselhos e apoio.
Obrigado a todos

 

[Pat Hartman]

If you have the answer you need. Please mark it if you can or tell us in words and then mark the question as solved. Thanks.