Help with linking A5v11 to SQLite

[Graham Wickens]

I have an SQLite Database that I use for my aviation data, I had it talking to A5V8 and A5V10 (from a setup years ago). My Old PC crashed and died, so had to buy a new one. I have of course forgotten how to do it and what ODBC drivers I need, any suggestions welcomed before the new PC takes a flying course!!
the old code that worked was:

'Date Created: 26-Jan-2007 05:48:47 PM
'Last Updated: 29-Aug-2007 07:01:42 AM
dim Conn as ole::adodb.connection
dim ConnString as c = "Provider=MSDASQL.1;Persist Security Info=False;Extended Properties=\"Driver={SQLite ODBC Driver};Database=c:\My Databases\BaseStation\basestation.sqb;StepAPI=0;SyncPragma=NORMAL;NoTXN=0;Timeout=2000;LongNames=0;NoCreat=0\""
conn.open(ConnString)
dim sqlupdate as c ="Update Aircraft Set Interested = -1 , UserString1 = ? , UserString2 = ? , UserString3 = ? , UserString4 = ? Where Registration = ?"
dim cmd as ole::adodb.command
cmd.ActiveConnection = conn
cmd.CommandText = sqlupdate
dim ra as n = 0
tbl = table.open("Sbs",file_rw_shared)
query.filter = "Interested = '-1' .and. isnotblank('Modes')"
query.order = "ModeS"
qry = tbl.query_create()
count = qry.records_get()
tbl.fetch_first()
WHILE .not. tbl.fetch_eof()
    cmd.Parameters[1] = trim(tbl.As)
    cmd.Parameters[2] = trim(tbl.Where)
    cmd.Parameters[3] = tbl.When
    cmd.Parameters[4] = trim(tbl.Reg)
    cmd.Parameters[5] = tbl.unique
    cmd.execute(ra)
    statusbar.set_text(rtrim(tbl.Reg)+" Updated")
    tbl.fetch_next()
END WHILE
qry.drop()
tbl.close()
conn.close()
ui_msg_box("BaseStation Sqb Update",ltrim(Str(Count))+" Records Updated",UI_OK)

I've downloaded and installed an "SQLite ODBC Driver" but I get error message

Conn.open(Connstring)
OLE Automation Error. Source is Microsoft OLE DB Provider for ODBC Drivers
File is encrypted or is not a database

a problem with the ODBC drivers maybe?

thanks

[jkwrpc]

Re: Help with linking A5v11 to SQLite

I don't know if this is the cause but you may want to bypass the OLEDB for ODBC setup. Also if you bought a new PC it may be 64bit and for some reason I am thinking the ODBC driver for SQLIte is 32bit. If this is true and you haven't already done so you would need to set the ODBC connection up in the 32 bit ODBC Administrator (located in the SYSWow 64 folder).

I am coming to like SQLite more and more. So if you find a solution please share it.

[Graham Wickens]

Re: Help with linking A5v11 to SQLite

thanks John,

I now have it working, I had installed both 32 bit and 64 bit ODBC drivers in the "Program Files" folder, I copied them to the "SYSWow 64" folder and rerun my test, which worked.