Create a SQLite database using ADODB.Connection

Question

I have a question that may be fairly simple to solve, but I can't find anything in the documentation nor in any website such as StackOverflow.

I'm importing information from a CSV to a SQLite3 database and to do so I use the following routine as to support my importation routine

  Sub runSQLiteQuery(path As String, strSQL As String)    

    Dim conn As Object, rst As Object

    Set conn = CreateObject("ADODB.Connection")
    Set rst = CreateObject("ADODB.Recordset")

    ' OPEN CONNECTION
    conn.Open "DRIVER=SQLite3 ODBC Driver;Database=" & path & ";"

    ' OPEN RECORDSET
    rst.Open strSQL, conn
    rst.Close
    ' FREE RESOURCES
    Set rst = Nothing: Set conn = Nothing

End Sub

My question is:

How do I create the database file in the first place?

I'm using a file created from SQLite3 command in cmd and importing the csv from that, but that's not a good solution for my Excel needs.

Thanks in advance!

Rui Rebelo de Andrade · Accepted Answer · 2018-03-30 10:55:01Z

4

I just found the way to do it.

The easiest way is to use the following code:

Sub runSQLiteQuery(path As String, strSQL As String)    

    Dim conn As Object, rst As Object

    Set conn = CreateObject("ADODB.Connection")
    Set rst = CreateObject("ADODB.Recordset")

    ' OPEN CONNECTION
    conn.Open "DRIVER=SQLite3 ODBC Driver;Database=" & path & ";"

    ' OPEN RECORDSET
    rst.Open strSQL, conn
    rst.Close
    ' FREE RESOURCES
    Set rst = Nothing: Set conn = Nothing

End Sub

Then, to create the database file we just need to use:

Sub startDb()
    Dim dbPath As String
    dbPath = Application.ActiveWorkbook.path

    ' Checks if Db exists. If not, creates
    If Dir(dbPath & "Db.s3db", vbDirectory) = "" Then Call runSQLiteQuery(dbPath & "\Db.s3db", "SELECT 1;")
End Sub

edited Mar 30, 2018 at 10:55

answered Mar 30, 2018 at 10:36

Rui Rebelo de Andrade

1011 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

help-info.de Over a year ago

Please have a look at Can I answer my own question? and come back two days later and check as answered if you have more than 15 reputation.

prairie cheng · Accepted Answer · 2020-08-27 06:55:31Z

1

'use excel 2016 vba    
Dim conn As Object
Set conn = CreateObject("ADODB.Connection")
newdb = "F:\sqlite3win64\test.db3"
conn.Open "DRIVER=SQLite3 ODBC Driver;Database=" & newdb

edited Aug 27, 2020 at 6:55

answered Aug 27, 2020 at 6:50

prairie cheng

112 bronze badges

1 Comment

xKobalt Over a year ago

Welcome to StackOverflow! While this answer may solve the OP's issues, it's recommended to provide an explanation about the written code to make your answer more understandable for the community and also to improve its quality

Collectives™ on Stack Overflow

Create a SQLite database using ADODB.Connection

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related