Results 1 to 22 of 22

Thread: Convert .Mdb to sqlite3

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Oct 2013
    Posts
    783

    Convert .Mdb to sqlite3

    Hello experts
    I used olaf's Demo "Nwind-Demo" to convert my acceess file to sqlite3
    Everything was alright except two things.
    1) The conversion doesn't take into account the relation between tables.
    After the conversion, the foreign key is missing .
    2) The dates are converted to long dates (2018-01-01 00:00:00). however I need short dates (2018-01-01)
    My question is it possible to change the dates by code from long format to short one.
    Another question: is it possible to add a foreign key by code?
    I'm using sqlite and VbrichClient5.
    thank you in advance

  2. #2
    Fanatic Member
    Join Date
    Feb 2019
    Posts
    706

    Re: Convert .Mdb to sqlite3

    There are GUI tools to manipulate SQLite DB's instead of doing it in code. Here is some options:

    https://www.sqlite-workbench.com/
    https://sqlitebrowser.org/
    https://sqlitestudio.pl/index.rvt

  3. #3
    PowerPoster
    Join Date
    Jan 2020
    Posts
    3,746

    Re: Convert .Mdb to sqlite3

    we need sqlite.db export to csv,can you give me a sample,thank you。
    and db to *.mdb

  4. #4

    Thread Starter
    Fanatic Member
    Join Date
    Oct 2013
    Posts
    783

    Re: Convert .Mdb to sqlite3

    qvb6
    thank you for the tools you provided
    I was able to add a foreign key but I couldn't figure out how to modify dates from long to short format
    Can anyone tell me howto do that with code.
    thank you

  5. #5

    Thread Starter
    Fanatic Member
    Join Date
    Oct 2013
    Posts
    783

    Re: Convert .Mdb to sqlite3

    xiaoyao
    using the tools that qvb6 has provided you can export to csv or import from csv

  6. #6
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,121

    Re: Convert .Mdb to sqlite3

    http://www.sqliteexpert.com/index.html -- the most capable sqlite admin/development tool I have found recently.

    sqlite does *not* have datetime columns per se. You are probably storing these as strings already so converting from long to short datetime format is a matter of string handling -- probably w/ substr and length built-in sqlite functions.

    cheers,
    </wqw>

  7. #7
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Convert .Mdb to sqlite3

    Quote Originally Posted by samer22 View Post
    qvb6
    thank you for the tools you provided
    I was able to add a foreign key but I couldn't figure out how to modify dates from long to short format
    Can anyone tell me howto do that with code.
    thank you
    Quote Originally Posted by wqweto View Post
    http://www.sqliteexpert.com/index.html -- the most capable sqlite admin/development tool I have found recently.

    sqlite does *not* have datetime columns per se. You are probably storing these as strings already so converting from long to short datetime format is a matter of string handling -- probably w/ substr and length built-in sqlite functions.

    cheers,
    </wqw>
    To piggyback on that... don't confuse the VALUE which is a datetime, with the FORMAT, which is for display... leave it as it is in the database, and only FORMAT the VALUE when you display it.,


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  8. #8
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: Convert .Mdb to sqlite3

    Another issue is that people often use a layer between their application and SQLite, and those layers often play reindeer games to try to map data types.

  9. #9
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,121

    Re: Convert .Mdb to sqlite3

    Quote Originally Posted by dilettante View Post
    Another issue is that people often use a layer between their application and SQLite, and those layers often play reindeer games to try to map data types.
    Does this reindeer's name start with a capital R? :-))

    cheers,
    </wqw>

  10. #10
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: Convert .Mdb to sqlite3

    Quote Originally Posted by wqweto View Post
    Does this reindeer's name start with a capital R? :-))
    Well, on one hand we have dilettante, who is known for spreading (uninformed) FUD about RC5,
    and on the other hand we have the "R"-wrappers author himself who has spent years with the SQLite-API and -documentation, who states:
    "There's no such thing as reindeers games, when it comes to Type-mapping of SQLite-Field-TypeDefs to VB-Types via the RC5-COM-wrapper".

    If you think dilettante is right with that nonsense-statement,
    would it be too much to ask, to give an example where you think the RC5-wrapper should behave differently?

    Olaf

  11. #11
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: Convert .Mdb to sqlite3

    Quote Originally Posted by dilettante View Post
    Another issue is that people often use a layer between their application and SQLite, and those layers often play reindeer games to try to map data types.
    Well, the non-SQLite-expert has spoken, I guess...

    FYI (although you never read any of these, I know)...

    The SQLite-engine has built-in Date-Functions of course...

    And these expect "Field-Input" in two formats:
    - either an Integer-Field-Value (stored as a Unix-Epoch)
    - or a Text-Field-Value (in ISO-DateString-Format)
    So, these two "lower-level-StorageClass" DateFormats (Integer and Text) are supported when it comes to Date-Field-passing into the builtin functions.

    And the RC5-COM-wrapper for SQLite has choosen only one of the above possible "Storage-Classes" to store Dates:
    - the ISO-Text-Format
    (because that's compatible with the built-in SQLite Date-Time-Functions, and also commonly used in other SQLite-Tools and -wrappers).

    The second question is, how those (low-level, as ISO-Date-String) stored SQLite-Dates are represented "on the other side of the wrapper",
    when we read DateValues out of Recordset-Fiels (or putting them into Rs-Fields or CommandObjects) via VB6/VBA or VBScript.

    If one does not want any RC5-Wrapper support with DateFields, then:
    - one has to define them at Create Table-Time as Text ... e.g.: MyISODateField Text

    Such a TextField-Type will not be interpreted or changed in any way by the RC5-Recordset-wrapper:
    - instead you will get your Rs("MyISODateField").Value out as a normal Text-String...
    - but you'll have to make sure yourself, that you store it later in SQLite as "ISO-Text-Format" (when setting the Rs-Value)
    - and use VBs CDate() Function, to convert such stored ISO-Texts into a normal VB-Date-Variable yourself

    If you want the cRecordset-Class to do all these "ISOTextDate-to-VBDate" conversions for you (under the covers),
    all you need is, to "give it a hint" (via your TableDefs Field-TypeNames):
    - SomeField Date (as well as SomeField DateTime) will ensure "long ISO-Format" at the SQLite-TextStorage-Level
    - SomeField ShortDate will ensure "short ISO-Format" (only "YYYY-MM-DD") at the SQLite-TextStorage-Level
    - SomeField Time will ensure "simple Time ISO-Format" (only "hh:mm:nn") at the SQLite-TextStorage-Level
    ...automatically performing the right mappings to normal VBDate-Variables when you access these Fields over Rs("SomeField").Value

    @samer22
    ["My question is it possible to change the dates by code from long format to short one"]

    Yes, but first let's take a look, why it comes to that situation (of always "long dates" being imported):

    Here is the code you can run, to convert an *.mdb to an SQLite-DBFile (e.g. with the ending *.db or *.db3):
    Code:
    Sub Import(MDBFile As String, SQLiteFile As String)
      'open the Source as an ADODB-Connection
      Dim CnnSrc As New ADODB.Connection
          CnnSrc.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & MDBFile
     
      'open the destination as an SQLite-Connection
      Dim CnnDst As vbRichClient5.cConnection
      If New_c.FSO.FileExists(SQLiteFile) Then New_c.FSO.DeleteFile SQLiteFile
      Set CnnDst = New_c.Connection(SQLiteFile, DBCreateNewFileDB)
     
      'and use the RC5-Converter for import (passing those two Cnn-Objects along)...
      With New_c.Converter(True)
        .ConvertDatabase CnnSrc, CnnDst
      End With
     
      'now we can test, what Field-Types the Converter has applied to the first imported Table
      Set CnnDst = New_c.Connection(SQLiteFile, DBOpenFromFile)
      Debug.Print Replace(CnnDst.DataBases(1).Tables(1).SQLForCreate, "[", vbCrLf & "[")
    End Sub
    In my SourceDB (the Access-generated *.mdb File), I've created a table "Test" with "mixed Fields" -
    among them (at the end of the list) 3 DateFields with different "Access-Format-SubTypes":
    - the field named DT (as a 'long, standard Date')
    - the field named D ('short Date')
    - the field named T ('as a Time-Field')

    But sadly, these "Access-Date-Subformats" are not transported correctly in the resulting ADO-Rs, which I use for imports into SQLite...
    Instead, what I see in the ADO-Rs-Field-Type for these 3 Date-Fields, is always the type "adDate = 7".

    So, that's the reason, why I have to import those Access-MDB-DateFields always in the "longest possible format" into SQLite (Date and Time as ISO-String - to not lose any information).

    This can be seen in the resulting SQLite-Table definition-string, which the above Code will report (in the last line):
    Code:
    CREATE TABLE
    [Test] (
    [ID] INTEGER PRIMARY KEY NOT NULL,
    [Txt] TEXT Collate NoCase,
    [Dbl] REAL Default 0,
    [Lng] INTEGER Default 0,
    [Bln] BIT NOT NULL Default 0,
    [DT] DATE,
    [D] DATE,
    [T] DATE)
    Above, the imported Date-Fields all come out with the TypeSpecifier DATE (which is - in my wrapper - a shortform for DateTime).

    As for "changing a the ColumnType of an SQLite-Table afterwards", this little helper-routine might do what you want:
    Code:
    Sub ConvertFieldTypeOn(Cnn As cConnection, TableName$, ColName$, NewType$, ConvExpr$)
      Dim Fields As cArrayList, Tbl As cTable, Col As cColumn
      Set Fields = New_c.ArrayList(vbString)
      Set Tbl = Cnn.DataBases(1).Tables(TableName)
      For Each Col In Tbl.Columns
        If UCase$(Col.Name) = UCase$(ColName) Then
           Fields.Add Col.NameInBrackets & " " & NewType & " " & Col.OriginalConstraint
        Else
           Fields.Add Col.NameInBrackets & " " & Col.ColumnType & " " & Col.OriginalConstraint
        End If
      Next
      If Len(Tbl.Constraint) Then Fields.Add Tbl.Constraint
     
      Cnn.Execute "Create Table [" & TableName & "_new](" & Fields.Join(", ") & ")" & IIf(InStr(UCase$(Tbl.SQLForCreate), "WITHOUT ROWID"), " WITHOUT ROWID", "")
      Cnn.Execute "Insert Into  [" & TableName & "_new] Select * From [" & TableName & "]"
      Cnn.Execute "Drop Table [" & TableName & "]"
      Cnn.Execute "Alter Table [" & TableName & "_new] Rename To [" & TableName & "]"
      Cnn.Execute "Update [" & TableName & "] Set [" & ColName & "]=" & ConvExpr
    End Sub
    usage of the above function then (e.g. for converting a "Date" (or "DateTime")-typed-Field to a "ShortDate" one:
    Code:
      ConvertFieldTypeOn CnnDst, "MyTbl", "MyLongDateField", "ShortDate", "Left$(MyLongDateField, 10)"

    HTH

    Olaf

  12. #12
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: Convert .Mdb to sqlite3

    Wow, your knee sure is jerkng pretty hard over there.

    I only meant that different database connector libraries may map data types in different ways. That's nothing specific to SQLite and we see it with everything from SQL Server to Text data files.

  13. #13
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: Convert .Mdb to sqlite3

    Quote Originally Posted by dilettante View Post
    Wow, your knee sure is jerkng pretty hard over there.
    Nope - rest assured, that my knee is just fine in the meantime...

    You're always "deliberately vague and suggestive" with your statements (especially when it comes to SQLite or RC5 or both...).

    You write them exactly in that way (first), to cause reactions like the one from wqweto (in #9) -
    though they remain "vague enough", to allow you "convenient back-paddling", as in your follow-up below...

    Quote Originally Posted by dilettante View Post
    I only meant that different database connector libraries may map data types in different ways. That's nothing specific to SQLite and we see it with everything from SQL Server to Text data files.
    Of course (<sigh>)...

    To not "further confuse my knee" - why not always write it up in a precise, non-misunderstandable manner (like in your above statement) in the future?

    Olaf

  14. #14
    PowerPoster
    Join Date
    Aug 2010
    Location
    Canada
    Posts
    2,414

    Re: Convert .Mdb to sqlite3

    When converting any file from one format to another you're almost always going to encounter issues that require human intervention to handle them appropriately. Automated tools/libraries make assumptions for you based on what the developer thinks you would want to happen, but these assumptions are usually based on converting only the most simple files. For anything even slightly complex, I think you will be best served by understanding the features and limitations of both file types, your data, and relationships therein and writing your own conversions routines. Even then you will likely encounter some issues that cannot be a 1:1 translation, requiring either breaking changes or app-level shims.

    This happens even at the application level. For example, as I've written new versions of my apps while trying to maintain as much backward compatibility as possible, I've had to migrate data stores from flat files written by the original DOS app, to whatever 16-bit VB3/4 was popularly using for data storage at the time -> VB5 DAO -> VB6 ADO -> VB6 SQLite DBs via vbRichClient5. I have a migration path from 35 year old files to the present day version. It wouldn't be fun in that you'd have to use the DOS->16-bitVB3/VB4->VB6 DAO/ADO->VB6Rc5/SQLite migration tools in a sequence but you could do it if required (although I've only had a customer do the full gamut once!). That said, human intervention was required to code each migration tool - I don't think any automated tool could ever be up to the job.

  15. #15

    Thread Starter
    Fanatic Member
    Join Date
    Oct 2013
    Posts
    783

    Re: Convert .Mdb to sqlite3

    Thank you Schmidt for your interest and help
    However I'm still having trouble o convrte my long format date
    This is what I did

    Code:
    Set cnn = New_c.Connection(App.Path & "\ProbData.db")
    ConvertFieldTypeOn cnn, "Const_Tbl", "Date_End", "ShortDate", "Left$(Date_End, 10)"
    I'm faced with the following error.
    No such column "Date_End"
    though I'm pretty sure the field "Date_End" is there.
    The error is thrown in this line
    Code:
    cnn.Execute "Update [" & TableName & "] Set [" & ColName & "]=" & ConvExpr
    Thank you

  16. #16
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: Convert .Mdb to sqlite3

    Quote Originally Posted by samer22 View Post
    ... I'm still having trouble o convrte my long format date
    This is what I did

    Code:
    Set cnn = New_c.Connection(App.Path & "\ProbData.db")
    ConvertFieldTypeOn cnn, "Const_Tbl", "Date_End", "ShortDate", "Left$(Date_End, 10)"
    I'm faced with the following error.
    No such column "Date_End"
    Then SQLite is probably right with its comment about "no such column"...
    Because I cannot reproduce your problem with the following (InMem-DB-based) Code
    (which tries to "mimick" your Const_Tbl, using the FieldName you gave above):

    Code:
    Option Explicit
    
    Private Sub Form_Load()
      Dim Cnn As cConnection
      Set Cnn = New_c.Connection(, DBCreateInMemory)
          Cnn.Execute "Create Table Const_Tbl(ID Integer Primary Key, Date_Start DATE, Date_End DATE)"
          
      With Cnn.OpenRecordset("Select * From Const_Tbl")
        .AddNew: !Date_Start = Now: !Date_End = Now + 1 'first record with "long-dates" (and a one-day-difference)
        .AddNew: 'add a second record, without specifying any Date-Values (to provoce Null-Values in these Fields)
        .AddNew: !Date_Start = Now + 2: !Date_End = Now + 4 'third record with a two-day-difference
        .UpdateBatch
      End With
      
      PrintRs "Before conversion:", Cnn.OpenRecordset("Select * From Const_Tbl")
      
      ConvertFieldTypeOn Cnn, "Const_Tbl", "Date_Start", "ShortDate", "Left$(Date_Start, 10)"
      ConvertFieldTypeOn Cnn, "Const_Tbl", "Date_End", "ShortDate", "Left$(Date_End, 10)"
     
      PrintRs "After conversion:", Cnn.OpenRecordset("Select * From Const_Tbl")
    End Sub
    
    Sub PrintRs(Comment As String, Rs As cRecordset)
      Debug.Print vbLf; Comment; " ... RecordCount:"; Rs.RecordCount
      Do Until Rs.EOF
        Debug.Print Rs!ID, Rs!Date_Start, Rs!Date_End
        Rs.MoveNext
      Loop
    End Sub
    
    Sub ConvertFieldTypeOn(Cnn As cConnection, TableName$, ColName$, NewType$, ConvExpr$)
      Dim Fields As cArrayList, Tbl As cTable, Col As cColumn
      Set Fields = New_c.ArrayList(vbString)
      Set Tbl = Cnn.DataBases(1).Tables(TableName)
      For Each Col In Tbl.Columns
        If UCase$(Col.Name) = UCase$(ColName) Then
           Fields.Add Col.NameInBrackets & " " & NewType & " " & Col.OriginalConstraint
        Else
           Fields.Add Col.NameInBrackets & " " & Col.ColumnType & " " & Col.OriginalConstraint
        End If
      Next
      If Len(Tbl.Constraint) Then Fields.Add Tbl.Constraint
     
      Cnn.Execute "Create Table [" & TableName & "_new](" & Fields.Join(", ") & ")" & IIf(InStr(UCase$(Tbl.SQLForCreate), "WITHOUT ROWID"), " WITHOUT ROWID", "")
      Cnn.Execute "Insert Into  [" & TableName & "_new] Select * From [" & TableName & "]"
      Cnn.Execute "Drop Table [" & TableName & "]"
      Cnn.Execute "Alter Table [" & TableName & "_new] Rename To [" & TableName & "]"
      Cnn.Execute "Update [" & TableName & "] Set [" & ColName & "]=" & ConvExpr
    End Sub
    It produces (without any error) the following output (the second record in table Const_Tbl was deliberately left at "Null-Dates"):
    Code:
    Before conversion: ... RecordCount: 3 
     1            15.02.2020 16:01:49         16.02.2020 16:01:49 
     2                          
     3            17.02.2020 16:01:49         19.02.2020 16:01:49 
    
    After conversion: ... RecordCount: 3 
     1            15.02.2020    16.02.2020 
     2                          
     3            17.02.2020    19.02.2020
    For questions like this, you're good advised, to provide info about the concrete Table-Schema
    (beforehand, already in your first post).

    You can use the following function, to easily get these Table-Schema-Infos:
    Code:
    Function GetTableSchemaFor(Cnn As cConnection, TableName As String) As String
      GetTableSchemaFor = Cnn.DataBases(1).Tables(TableName).SQLForCreate
      GetTableSchemaFor = Replace(Replace(GetTableSchemaFor, ",", "," & vbCrLf), vbCrLf & vbCrLf, vbCrLf)
      New_c.Clipboard.Clear 'let's also copy the result into the ClipBoard
      New_c.Clipboard.SetText GetTableSchemaFor
    End Function
    HTH

    Olaf

  17. #17
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: Convert .Mdb to sqlite3

    I'm not sure where my comment was vague in any way.

    One could just as easily assume that your own SQLite support does a better job mapping VB data types to SQLite data types than the various ODBC Drivers and OLEDB Providers available.

  18. #18

    Thread Starter
    Fanatic Member
    Join Date
    Oct 2013
    Posts
    783

    Re: Convert .Mdb to sqlite3

    Olaf
    I'm so grateful to you
    I've been playing with some sqlite tools, and the name of he field has slightly been changed.
    the field name ( Date_End) had been quoted and becomes 'Date_End '
    I didn" pay attention to that
    Thank you very much

  19. #19

    Thread Starter
    Fanatic Member
    Join Date
    Oct 2013
    Posts
    783

    Re: Convert .Mdb to sqlite3

    Olaf
    I'm so grateful to you
    I've been playing with some sqlite tools, and the name of the field has slightly been changed.
    the field name ( Date_End) has been quoted and becomes 'Date_End '
    I didn" pay attention to that
    Thank you very much

  20. #20
    PowerPoster
    Join Date
    Sep 2012
    Posts
    2,083

    Re: Convert .Mdb to sqlite3

    Say a off-topic nonsense:

    I used to plan to use RC5 to develop a commercial software similar to SqliteExpert. My software will contain a lot of very valuable new features not included in SqliteExpert. But because Windows desktop software is basically no market in China. So I abandoned the plan.
    Last edited by dreammanor; Feb 16th, 2020 at 06:17 AM.

  21. #21
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: Convert .Mdb to sqlite3

    Oh, there might be a market. It is probably just very small no matter where you go.

    Things have changed and desktop/laptop PC use and ownership seems to be contracting away from homes and individuals, and even in business few people need the full keyboard, screen, and power of a current PC.

    Programming tools seem to have an even smaller real market any more and database tools probably share a similar fate. The serious development seems to be in fewer hands at the same time lightweight scripting has become a more widespread skill than ever.

    Economies of scale seldom favor the little guy after a tipover point has been reached. Look at how many small game software houses quickly failed to make it on their own once that became a mainstream market.

  22. #22
    PowerPoster
    Join Date
    Sep 2012
    Posts
    2,083

    Re: Convert .Mdb to sqlite3

    Yes. When the industry is concentrated in a few very large companies, the industry will become less and less creative and less fun to work.

    In most cases, making money and working pleasure are contradictory. As desktop/laptop PCs become more and more marginalized, programming becomes less and less fun.

    I'm tired of developing bloated and bulky programs that are only used by a few large enterprises. I'd like to make small and interesting products, but these small products are difficult to bring you some benefit.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width