-
Feb 13th, 2020, 05:34 PM
#1
Thread Starter
Fanatic Member
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
-
Feb 13th, 2020, 06:13 PM
#2
Fanatic Member
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
-
Feb 13th, 2020, 08:20 PM
#3
Re: Convert .Mdb to sqlite3
we need sqlite.db export to csv,can you give me a sample,thank you。
and db to *.mdb
-
Feb 13th, 2020, 08:45 PM
#4
Thread Starter
Fanatic Member
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
-
Feb 13th, 2020, 08:48 PM
#5
Thread Starter
Fanatic Member
Re: Convert .Mdb to sqlite3
xiaoyao
using the tools that qvb6 has provided you can export to csv or import from csv
-
Feb 14th, 2020, 02:44 PM
#6
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>
-
Feb 14th, 2020, 02:49 PM
#7
Re: Convert .Mdb to sqlite3
Originally Posted by samer22
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
Originally Posted by wqweto
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
-
Feb 14th, 2020, 04:10 PM
#8
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.
-
Feb 14th, 2020, 05:10 PM
#9
Re: Convert .Mdb to sqlite3
Originally Posted by dilettante
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>
-
Feb 14th, 2020, 08:46 PM
#10
Re: Convert .Mdb to sqlite3
Originally Posted by wqweto
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
-
Feb 14th, 2020, 09:03 PM
#11
Re: Convert .Mdb to sqlite3
Originally Posted by dilettante
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
-
Feb 14th, 2020, 09:16 PM
#12
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.
-
Feb 14th, 2020, 10:17 PM
#13
Re: Convert .Mdb to sqlite3
Originally Posted by dilettante
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...
Originally Posted by dilettante
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
-
Feb 14th, 2020, 11:13 PM
#14
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.
-
Feb 15th, 2020, 04:39 AM
#15
Thread Starter
Fanatic Member
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
-
Feb 15th, 2020, 10:22 AM
#16
Re: Convert .Mdb to sqlite3
Originally Posted by samer22
... 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
-
Feb 15th, 2020, 12:23 PM
#17
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.
-
Feb 15th, 2020, 01:20 PM
#18
Thread Starter
Fanatic Member
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
-
Feb 15th, 2020, 01:44 PM
#19
Thread Starter
Fanatic Member
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
-
Feb 16th, 2020, 02:42 AM
#20
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.
-
Feb 16th, 2020, 12:54 PM
#21
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.
-
Feb 17th, 2020, 12:45 PM
#22
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|