UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Ms Access 2007 Linked To Sqlite 3 Tables , Relationship ?    
 
   
Sailor
post Jan 18 2018, 03:57 AM
Post#1



Posts: 95
Joined: 14-December 16



I have an access database with two linked tables that is found inside SQLite 3 database
the two tables have primary key field (dest)
inside the access database I can insert , update , delete on the two tables
when I tried to create a relationship (one to many) , the (Enforce referential integrity ,Cascade update ,Cascade delete) are all grayed
I cant check those options , Why ?


* The link is based on ODBC .
This post has been edited by Sailor: Jan 18 2018, 04:20 AM
Go to the top of the page
 
Sailor
post Jan 18 2018, 05:21 AM
Post#2



Posts: 95
Joined: 14-December 16



I did much Googling , and find some answers that are :
apparently cannot enforce integrity except between the same access databases
So , we can't make an integrity inside access while the tables are in the general db ?

but I can't make the integrity inside SQLite either .
Go to the top of the page
 
MadPiet
post Jan 18 2018, 05:35 AM
Post#3



Posts: 3,333
Joined: 27-February 09



Can you define the tables *outside* a GUI? In SQL Server, you can do...

CREATE TABLE MyTable (
....
CONSTRAINT fkOtherTable FOREIGN KEY REFERENCES OtherTable(OtherTablePK) ON DELETE CASCADE ON UPDATE CASCADE);
Go to the top of the page
 
Sailor
post Jan 18 2018, 05:43 AM
Post#4



Posts: 95
Joined: 14-December 16



I am working on SQLite 3 not SQL Server
are they the same sentences ?
This post has been edited by Sailor: Jan 18 2018, 05:46 AM
Go to the top of the page
 
MadPiet
post Jan 18 2018, 06:20 AM
Post#5



Posts: 3,333
Joined: 27-February 09



http://www.sqlitetutorial.net/sqlite-foreign-key/

Yes, I'm aware it's not SQL Server, but under the covers a lot of SQL is standard, so will work across different databases.
Go to the top of the page
 
Sailor
post Jan 18 2018, 07:22 AM
Post#6



Posts: 95
Joined: 14-December 16



While I need all operations on the two tables are done via my access db
is that would work ? ie , delete a record should also delete all related records ?
Go to the top of the page
 
argeedblu
post Jan 18 2018, 08:22 AM
Post#7


Remembered
Posts: 14,079
Joined: 26-September 02
From: Sudbury, Ontario, Canada


Sailor,

It is the backend (SQL-Lite in this case) that manages referential integrity. The frontend (your Access file) does not. Hence the greyed-out commands.

In addition to the reference MadPiet posted you may find this one www.sqlite.org/foreignkeys.html helpful

Glenn
Go to the top of the page
 
GroverParkGeorge
post Jan 18 2018, 09:22 AM
Post#8


UA Admin
Posts: 35,888
Joined: 20-June 02
From: Newcastle, WA


As Glenn pointed out, Tables can ONLY be managed from within their native RDBMS, not from any external application, unless you want to implement some sort of DDL to do that. In Access, perhaps you could do it through a pass-thru query on the SQLLite RDMS. On the other hand, direct DATA management, as opposed to DATA STRUCTURE management, tasks can be done via the interface. Here, for example, if you cannot enforce RI on the back end tables in SQLLite, you could write code that handles the cascading updates and deletes on the related tables.
This post has been edited by GroverParkGeorge: Jan 18 2018, 09:22 AM
Go to the top of the page
 
Sailor
post Jan 18 2018, 10:48 AM
Post#9



Posts: 95
Joined: 14-December 16



So we have no way to go except by doing some personal procedures
well guys , thank you all . hat_tip.gif
Go to the top of the page
 
Sailor
post Jan 18 2018, 12:37 PM
Post#10



Posts: 95
Joined: 14-December 16



One last Question please

the main reason to link to SQLite is that ms access is limited to about 2GB
is that mean I can get the benefit of the 140 TB large of SQLite when I follow that sort ?
This post has been edited by Sailor: Jan 18 2018, 12:40 PM
Go to the top of the page
 
GroverParkGeorge
post Jan 18 2018, 12:54 PM
Post#11


UA Admin
Posts: 35,888
Joined: 20-June 02
From: Newcastle, WA


There are many alternative RDBMSs, including SQL Server Express (free and up to 10 GB of Data), SQLLite, and others. All have their own limitations on data size.

So, whichever you choose, you can expect to take advantage of whatever they offer. Astonishingly, the SQLLite website says: " Every database consists of one or more "pages". Within a single database, every page is the same size, but different database can have page sizes that are powers of two between 512 and 65536, inclusive. The maximum size of a database file is 2147483646 pages. At the maximum page size of 65536 bytes, this translates into a maximum database size of approximately 1.4e+14 bytes (140 terabytes, or 128 tebibytes, or 140,000 gigabytes or 128,000 gibibytes). "

But it also warns:

"This particular upper bound is untested since the developers do not have access to hardware capable of reaching this limit. However, tests do verify that SQLite behaves correctly and sanely when a database reaches the maximum file size of the underlying filesystem (which is usually much less than the maximum theoretical database size) and when a database is unable to grow due to disk space exhaustion. "

I'd imagine the limits of your computer's file system will govern what is available to you.
Go to the top of the page
 
Sailor
post Jan 18 2018, 01:26 PM
Post#12



Posts: 95
Joined: 14-December 16



20 GB is very nice for me

thank you GroverParkGeorge .
Go to the top of the page
 
GroverParkGeorge
post Jan 19 2018, 09:20 AM
Post#13


UA Admin
Posts: 35,888
Joined: 20-June 02
From: Newcastle, WA


Well, if you need 20GB of storage, SQL Server Express at 10GB wouldn't be adequate. If SQLLite offers that much storage, it's a better choice for your needs.

Continued success with your project.
Go to the top of the page
 
sureshdasari
post Sep 15 2018, 11:12 AM
Post#14



Posts: 1
Joined: 15-September 18



I didn't thought SQLite will support 20 GB of data. In that case i guess you can prefer to use SQLite instead of SQL Server.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th October 2019 - 03:53 AM