migrate autonumber columns from access to sqlite

Question

I'm trying to migrate the database for an existing application from access to SQLite. The application uses Autonumbers to generate unique IDs in Access and some tables reference rows from other tables by these unique IDs.

What's a good way to migrate these and keep this functionality intact?

From what I've read, SQLite uses Auto indexing for this. How would I create the links between the tables? Do I have to search the other tables for the row with that unique ID and replace the reference with the SQL generated ID?

example: table 1, has a column linkedID with a row with the value {7F99297A-DE91-4BD6-9ED8-FC13D668CDA2}, which is linked to a row in table 2 with primaryKey {7F99297A-DE91-4BD6-9ED8-FC13D668CDA2}.

Answer 1

Well, there not really a automated way to do this.

but, what I do to migrate data?

I setup a linked table in Access. Double check if that linked table works (you need to install the odbc driver).

Assuming you have a working linked table?

Then you can do this to export the Access table in VBA to sqlite.

  Dim LocalTable    As String      ' name of local table link
   Dim ServerTable   As String      ' name of table on SQL Lite
   
   LocalTable = "Table1"
   ServerTable = "TableSLite"
   Dim strCon As String
   strCon = CurrentDb.TableDefs("Test1").Connect
   ' above is a way to steal and get a working connection from a valid
   ' working linked table (I hate connection strings  in code)
   Debug.Print strCon
   
   DoCmd.TransferDatabase acExport, "ODBC Database", strCon, acTable, LocalTable, ServerTable
   
   Debug.Print "done export of " & LocalTable

That will get you the table in sqlite. But, there are no DDL (data definition commands) in sqlite to THEN change that PK id from Access to a PK and autonumber.

However, assuming you say have "db browser"?

Then simple export the table(s) as per above.

Now, in db browrser, open up the table, and choose modify, and simple check the AI (auto increemnt, and then PK settings - in fact if you check box AI, then the PK useally selects for you. So, after I done the above export. (and you should consider close down Access - since you had/have linked tables).

So, in db browser, we now do this:

so, for just a few tables, the above is not really hard.

However, the above export (transfer) of data does not set the PK, and auto increment for you.

If you need to do this with code, and this is not a one time export/transfer, then I don't have a good solution.

Unfortantly, SqlLite does NOT allow a alter table command to set PK and set auto increment (if that was possbile, then after a export, you could execute the DDL command in sqlite (or send the command from your client software) to make this alteration.

I not sure if sql lite can spit out the "create table" command that exists for a given table (but, I think it can). So, you might export the schema, get the DDL command, modify that command, drop the table, re-run the create table command (with current PK and auto increment), and THEN use a export or append query in Access.

But, transfer of the table(s) in question can be done quite easy as per above, but the result(s) do not set nor include the PK setting(s) for you.

However, if this is one time export? Then export of tables - and even the dirty work of figuring out the correct data types to be used?

The above works well - but you have to open up the tables in a tool like say db browser, and then set PK and auto increment.

I do the above quite often for transfer of Access tables to sqlLite tables, but it does then require some extra steps to setup the PK and auto increment.

Another possbile way if this had to be done more then one time? I would export as per above, and then add the PK (and auto increment).

I would then grab say the 8 tables create commands from sqlLite, and save those create table commands in the client software.

then you execute the correct create table command, and then do a append query from Access. So, it really depends if this is a one time export, or this process of having to create the table(s) in sqlLite is to occur over and over.