[quote]I'm trying to link my IQ database to my Zotero database, which is a .sqlite file. I'm totally new to the world of SQL, but I've learned some of the basics over the last few days. I installed a sqlite-odbc driver, and I can now access my zotero.sqlite database from Microsoft Access. Now I'm trying to figure out how to write the appropriate sqlsynch.xml file in order to connect IQ to the ODBC data source. Can anyone give me any pointers (or point me to further information) on how to write the configuration file (connection string)? I have read the example in the help file, but I need a little more guidance.
UPDATE: Here's my current attempt at a test configuration file (designed to interact with the driver found here):
<ConnectionString>
Driver={SQLite3 ODBC Driver};
DSN=zsqlite_real;
Database=C:\Documents and Settings\Lucas\Application Data\Mozilla\Firefox\Profiles\rycv3g5o.default\zotero\zotero.sqlite;
</ConnectionString>
<FieldMap>
[itemid] iditem_import
[value] item
</FieldMap>
<Source>
select items.itemid, itemdatavalues.value
from items
left join itemdata
on items.itemid=itemdata.itemid
left join itemdatavalues
on itemdata.valueid=itemdatavalues.valueid
limit 5
</source>
<RecordIDField>iditem_import</RecordIDField>
[UPDATE2: I should add that I've checked the source query separately (from SQLite Manager in Firefox) and it works, so presumably the problem is in one of the other sections, most likely the connection string.]
The syntax of the connection string part is just based on what I could glean from looking around online, but I'm not sure if it's currect. Is it obvious where there errors are in this configuration file?
Alternatively, since i am able to access the sqlite database from Microsoft Access, is there a way to generate the connection string from within Access?
Thanks,
Lucas[/quote]
UPDATE: With some good old trial and error and fiddling around, I've got it working now. Cool! 
(At some point I'd be happy to give some further details on how I got it to work, but right now I need to go to bed!)
Comments