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
> Access To Sqlite Via Obdc - Stuck    
 
   
maddaze
post Aug 16 2013, 09:15 AM
Post#1



Posts: 13
Joined: 10-August 13



I have an Access 97 database that serves as a front-end, via ODBC and linked tables, to a MySQL database, running under Wiin7-64. (Yes, it does!) The database contains info about places of worship and pilgrimage in the part of France where I live. In addition, I have tens of thousands of photos of the sites in Photoshop Elements 9. The underlying database engine of PSE9 is SQLite, and insteresting data about the photos is there (my titles, which ones I like, etc.). I would like to link from Access to tables in the SQLite database as I do to the MySQL database.
My problem: I am unable to create an ODBC connection to the SQLite database. I have done multiple searches via Google, tried a variety of suggestions, and still no ODBC driver, neither in the 32bit or 64bit ODBC tools of Win7-64.
While I recognize this isn't a strictly Access problem, I'm hopeful a wise head here can tell me, step by step, how to create a D****D ODBC connection to SQLite.
Thanks,
Harvey in balmy Bordeaux
Go to the top of the page
 
BananaRepublic
post Aug 16 2013, 09:25 AM
Post#2


Dungeon Cleaner
Posts: 1,504
Joined: 16-June 07
From: Banana Republic


Just to assure you -- you do want 32-bit, not 64-bit driver, because it's the Access' bitness, not OS's bitness that determine which driver you want.
Just to be sure, have you seen this already?
Next, I'm sorry to report that I had similar experience trying to link to sqlite. The ODBC driver for sqlite seems to be an incomplete implementation. If I recall, I was able to link it using ADO, which mean you have to do it all in VBA and you can't use linked tables; only recordsets in VBA code. Mind you, that was 2 or 3 years ago. I hope that my experience no longer applies, though.
Finally, what if you used a third-party database conversion tool to move data?
Go to the top of the page
 
maddaze
post Aug 16 2013, 11:14 AM
Post#3



Posts: 13
Joined: 10-August 13



BananaRepublic (Costa Rica? Nicaragua?),
Thanks for getting back so quickly.
Oknow about 32-bit for Access 97 - I suspect the Photoshop Elements 9 SQLite database is also 32-bit.
The link you gave is broken, but I managed to find the page and no, I hadn't seen it - will look more closely after this post.
> Finally, what if you used a third-party database conversion tool to move data?
A possibility, but awkward. And I do like having a direct connection to the database. May have to settle for snapshots; however, I am certain from research that others have created ODBC connections to SQLite.
Harvey
Go to the top of the page
 
maddaze
post Aug 16 2013, 11:45 AM
Post#4



Posts: 13
Joined: 10-August 13



BananaRepublic,
onnectionstrings.com is an interesting site. Wish I'd known about it several years ago when I was tearing my hair out trying to connect PHP to MySQL.
Unfortunately nothing there about creating an ODBC connection to SQLite under Win7-64.
Harvey
Go to the top of the page
 
BananaRepublic
post Aug 16 2013, 12:00 PM
Post#5


Dungeon Cleaner
Posts: 1,504
Joined: 16-June 07
From: Banana Republic


While Win7-64 isn't listed, I believe the ODBC connection shown should work for linking to Access.
http://www.connectionstrings.com/sqlite3-ODBC-driver/
"DRIVER=SQLite3 ODBC Driver;Database=c:\mydb.db;LongNames=0;Timeout=1000;NoTXN=0;
SyncPragma=NORMAL;StepAPI=0;"
Note that for Access, you have to add "ODBC;" in front of the connection string.
Go to the top of the page
 
maddaze
post Aug 16 2013, 02:12 PM
Post#6



Posts: 13
Joined: 10-August 13



Hmmm...I'm not searching to create a connection to SQLite in VBA code. Instead I want to link to tables in the SPE9 database from the table tab of the Access database window and I've never had to enter a connection string to do that. Am I missing something?
Go to the top of the page
 
BananaRepublic
post Aug 16 2013, 03:31 PM
Post#7


Dungeon Cleaner
Posts: 1,504
Joined: 16-June 07
From: Banana Republic


I went and gave it a try myself; downloaded the latest binaries and all.
keep on getting the same errors I saw last time. The only instance I was successful connecting was if I used one of DSN that the ODBC installer creates which uses a pre-created sqlite.db (it has no tables and thus need to be created via command line).
But trying to do anything with any other database file results in some of error.
Looking at the ODBC driver documentation, it look like they tested for Excel 97 Query. Access is not mentioned. The Excel 97 Query would be using Jet/DAO 3.5, which is quite old and might had some differences between current version of DAO that they did not test for. That's an awfully narrow test set.
Go to the top of the page
 
maddaze
post Aug 17 2013, 08:48 AM
Post#8



Posts: 13
Joined: 10-August 13



Problem solved:
You recall I'm running on a Win7 - 64 bit machine. I had tried to run the install routine sqliteodbc.exe multiple times and it kept failing, so started looking around. I found two sqlite routines (sqliteodbc.dll and sqliteodbcu.dll) in the SysWOW64 folder. I renamed them and was able to run sqliteodbc.exe sucessfully. I then started Access 97, clicked on the New table button, selected Link Table and selected file type ODBC database. In the Select Data Source window that opened, I clicked on the New button and selected the SQLite3 ODBC driver I now found. It was downhill from there.
Now, when I look in the ODBC adminstrator apps, I see the ODBC connection I created within Access. I still don't see the SQLite ODBC driver when I click on the Add button. Curiously, it's visible from within Access, but not in the ODBC administrator. Maybe if I had to tried to create the SQLite connection from within Access early on, this conversation would not have occurred.
Go to the top of the page
 
BananaRepublic
post Aug 17 2013, 08:53 AM
Post#9


Dungeon Cleaner
Posts: 1,504
Joined: 16-June 07
From: Banana Republic


Right.
There are two odbcad32.
If you opened the default one on x64, you're dealing with x64 drivers.
To open the x32 version, you have to manually open "C:\Windows\SysWOW64\odbcad32.exe" to get the 32-bit and therefore the sqlite listed.
Glad you got it sorted out.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    15th December 2017 - 03:08 AM