Using SQLite as backend (1 Viewer)

[elliotgr]

I am using SQLite as a backend as the speed/size is so much better. 400 Mbs down to 39 Mbs. Query time 14 seconds down to less than 1.

However what would be the best way to append data. Even though the tables are linked, a normal Append does not work, and a pass thru query does not work. Local data needs to be transferred to the SQLite backend, but a pass thru cannot deal with a local and remote table (apparently).
Current solution is to dump the data to the backend as a table, then execute an insert query on the other remote table, then drop the uneeded table.
Surely there is a better way?

 

[Pat Hartman]

I have never heard of SQLite. If it is a real RDBMS it should support updates. Access will not allow updates to a linked table unless the linked table has a primary key or unique index. Do your tables have unique indexes? If they do and you still can't update, then get rid of SQLite because it is a toy. You can use SQL Server Express instead and it is free as a download from MS. You are limited to 2gig of data but otherwise it is fully functional.

 

[jdraw]

I haven't used sqllite, but have heard of it. I just found this link via google
http://sqlite.awardspace.info/syntax/sqliteodbc.htm

It suggests that msAccess can front end sqllite and use append and update queries.
You may wish to review that info.

 

[elliotgr]

Thanks Pat/Jdraw

I am using SQLite because some of our Python apps use it.
Very easy and compact. No size limit. I had this trouble before with pass thru queries in that they bypass Access totally, and because they only act on the remote database, you cannot refer to a local table as it doesn't 'exist' according to the remote database.
I think any remote ODBC database would have the same issue?
Dumping data to them via a query or table is not a problen, but appending is the issue, as the INSERT would not recognise the local table.

 

[Galaxiom]

No back end is going to support front end tables in pass through queries.

Moreover any query that uses both local and back end tables will be relatively slow compared to having all tables in the back end.

BTW. Did you compare the back end sizes after compacting the Access backend? Also the speed depends very much on how the queries are constructed and the tables are indexed.

 

[elliotgr]

Ok, I will have to shift all queries to the back end as well, and create views etc.

The main issue is that our company does not allow Access databases on the servers due to the traffic overheads.

Compacting the Access database is done on a daily basis as the read/writes are extensive. However I think Access still keeps a lot of overheads in formatting etc.

Thanks
This is now closed.

 

[Pat Hartman]

Then don't use Access (actually we're talking about Jet or ACE here) as the BE. Move all the tables to SQLite and just use Access (now we are talking about Access the RAD tool) as your FE.