Tạo File, đọc và ghi dữ liệu vào File Access và SQLite (1 người xem)

[Kiều Mạnh]

ý tưởng từ bài số 10 chủ đề sau

Remote Data Service - Dịch vụ dữ liệu từ xa Qua Internet

Tương tự như chủ đề sau nhưng Tôi tách ra làm 2 chuyên đề khác nhau cho tiện quản lý và hổ trợ ai đó sử dụng tránh sự nhầm lẫn trong hỏi và trả lời không đáng có...

www.giaiphapexcel.com

1/ Tôi chia sẻ miễm phí không giới hạn cho bất cứ ai có như cầu thì dùng còn không thì thôi không cần thiết

2/ Quá trình sử dụng có lỗi phát sinh vui lòng mô ta lỗi chi tiết nếu được tôi sẻ viết lại và điều chỉnh code

3/ File úp phía dưới sử dụng tạo File Access và SQLite xong đọc và ghi dữ liệu vào file đã tạo trước đó sử dụng thuần FireDAC trên Delphi
( Không áp dụng cho đọc và ghi dữ liệu file Excel )

1/ Code tạo File

Private Const DBsqlite As String = "C:\Database_Server\MyDatabase.sqlite"

Private Sub CreateNewDatabase_DBsqlite()
    Dim Result As Boolean
    Result = CreateNewDatabase(DBsqlite)
    If Result Then
        Debug.Print "Create New Database successfully."
    Else
        Debug.Print "Create New Database Failed"
    End If
End Sub

2/ Code tạo TableName

Private Sub CreateTable_DBsqlite()
    Dim sqlCreateTable As Variant 'String
    Dim Result As Boolean
    Dim Tablename As Boolean
   
    sqlCreateTable = "CREATE TABLE MyTableName (ID INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT, Age INTEGER);"
   
    Tablename = IsTableExists(DBsqlite, "MyTableName")
   
    If Not Tablename Then
        Result = ExecuteSQL(DBsqlite, sqlCreateTable)
       
        If Result Then
            Debug.Print "Table created successfully."
        Else
            Debug.Print "Failed to create table."
        End If
    End If
End Sub

3/ Code INSERT INTO

Private Sub Test_InsertInto_MyTableName()
    Dim sqlInsertData As String
    Dim Result As Boolean
   
    Rem Câu lenh SQL de chèn du lieu vào bang
    sqlInsertData = "INSERT INTO MyTableName (Name, Age) VALUES ('John Doe', 30);"
   
    Rem Goi hàm ExecuteSQL de thuc thi câu lenh SQL
    Result = ExecuteSQL(DBsqlite, sqlInsertData)
   
    Rem Kiem tra ket qua và hien thi thông báo
    If Result Then
        Debug.Print "Data inserted successfully."
    Else
        Debug.Print "Failed to insert data."
    End If
   
    Sheet1.Select
    Call GetDataFrom_MyTableName
End Sub

4/ Code lấy dữ liệu đã chèn trước đó

Private Sub GetDataFrom_MyTableName()
    Dim i As Long, j As Long
    Dim SQL As Variant
    Dim Data As Variant
    Rem Dim Data() As Variant là loi neu Data = Empty
    Dim base64Data As String

    SQL = "SELECT * FROM MyTableName"

    Rem Goi hàm DLL Delphi và nhan du lieu
    Data = LoadDataFromSQLToVariant(DBsqlite, SQL)
   
    If IsArray(Data) Then
        Cells.ClearContents
        Rem Gán du lieu lên Sheet cua Excel
        For i = LBound(Data, 1) To UBound(Data, 1)
            For j = LBound(Data, 2) To UBound(Data, 2)
                Rem Gán giá tri tu mang data vào ô tuong ung trên Sheet
                Sheet1.Cells(i + 1, j + 1).value = Data(i, j)
            Next j
        Next i
    End If
End Sub

5/ code xoá dữ liệu chèn trước đó

Private Sub DeleteDataFrom_MyTableName()
    Dim sqlDeleteData As String
    Dim Result As Boolean
   
    Rem Câu lenh SQL de xóa t?t c? d? li?u t? b?ng
    Rem sqlDeleteData = "DELETE FROM MyTableName WHERE Name = 'John Doe';"
   
    sqlDeleteData = "DELETE FROM MyTableName;"
   
    If IsTableExists(DBsqlite, "MyTableName") Then ''xu lý loi khi MyTableName ko có mà Delete
       
        Rem Goi hàm ExecuteSQL de thuc thi câu lenh SQL
        Result = ExecuteSQL(DBsqlite, sqlDeleteData)
       
        Rem Kiem tra ket qua và hien thi thông báo
        If Result Then
            Debug.Print "Data deleted successfully."
        Else
            Debug.Print "Failed to delete data."
        End If
    End If
End Sub

6/ code DROP TABLE MyTableName đã tạo trước đó

Private Sub DropTable_DataFromMyTableName()
    Dim sqlDeleteData As Variant 'String
    Dim Result As Boolean
   
    Rem Câu l?nh SQL d? xóa t?t c? d? li?u t? b?ng
    Rem sqlDeleteData = "DELETE FROM MyTableName WHERE Name = 'John Doe';"
    sqlDeleteData = "DROP TABLE MyTableName;"
   
    If IsTableExists(DBsqlite, "MyTableName") Then ''xu lý loi khi MyTableName ko có mà Delete
   
        Rem Goi hàm ExecuteSQL de thuc thi câu lenh SQL
        Result = ExecuteSQL(DBsqlite, sqlDeleteData)
       
        Rem Kiem tra ket qua và hien thi thông báo
        If Result Then
            Debug.Print "Data deleted successfully."
        Else
            Debug.Print "Failed to delete data."
        End If
    End If
End Sub

Áp dụng cho file Access tương tự như SQLite nhưng lưu ý có câu lệnh SQlite sử dụng chung cho Access tốt nhưng có câu lệnh sẻ lỗi làm đơ thoát Excel

Vì vậy trước khi thực hiện hãy tìm hiểu câu lệnh SQL cho Access và SQlite trước khi sử dụng

vui lòng không trích dẫn bài này vì tôi đang bận viết nhanh một chút úp cho ai đó tò mò thử ... rảnh xem lại bài viết lại cho chỉnh chu hơn

@A-T qua đây thử trước cái trên PC xem ... còn vẫn hàm đó cách sử dụng đó tôi thêm IP,Port vào là truy xuất qua Internet ầm ầm thôi

 

[Kiều Mạnh]

Bài trên qua tuần rảnh tôi chỉnh sửa thêm sau ..

Giờ rảnh một tẹo bà tám gợi ý cho ai đó sử dụng hiệu quả hơn trong công việc của Mình

1/ Khi ta sử dụng Excel lưu dữ liệu lên tầm trên 2 MB là khi truy xuất dữ liệu hay mở lên thôi cũng mệt vãi mồ hôi hột ra ..

2/ khi từ bỏ mục số 1 chuyển qua lưu vào file Access thì ta cảm giác như 10 na9m chưa tắm nay tắm mát sạch sẻ thoáng mát thơm tho nhưng khổ nổi file Access Ms chỉ hổ trợ tối đa là 2 GB dữ liệu ( nhưng thực tế chỉ đạt khoãng 1.7 GB dữ liệu gì đó còn lại là các file hệ thông của nó chi chi đó )

3/ khi ta chuyển mục số 2 sang lưu trên Data.SQlite thì nó hổ trợ trên 1 Tỷ GB có nghĩa ta phải có trên 1 Tỷ GB ổ cứng xong lưu dữ liệu hết đời ta xong chuyển cho đời con cháu may ra hết chăng ???!!!

Thực tiễn và ứng dụng

1/ Tạo 1 File Data.SQlite ta lưu dữ liệu thô vì nó không hổ trợ cấu hình mà do lập trình tự viết trình hổ trợ giao diện cho nó xong xử lý trên nền của nó

2/ Tạo Một file Access sử dụng tạo các mối quan hệ tính toán và khi cần ta viết hàm trong các Query cho nó thực thi khi dữ liệu thay đổi thì nó lập tức tính toán cho ta

Ví Dụ:
1 / Tạo 2 bảng dữ liệu là DataNhap và DataXuat vào file Data.SQlite xong ta lưu dữ liệu vào đó

2/ trên File Access ta tạo một bảng tạm keo NhapXuatTon xong từ Excel là lưu dữ liệu thô vào DataNhap và DataXuat xong tính toán +-*/ xong lưu vào NhapXuatTon

vậy từ NhapXuatTon ta căn cứ vào đó tạo các mối quan hệ tiếp tính toán trên File Access
======> rất đơn giản ngon bổ rẻ lưu dữ liệu vài đời chưa hết

tạm vậy đi rảnh bà tám sau

 

[Kiều Mạnh]

Ngày qua rảnh hết trò mới để chơi cho vui vẻ tôi thử viết cái na ná như trình điều kiển
SQLite ODBC Driver khi ta cài sử dụng ADODB từ VBA để truy xuất dữ liệu SQlite ( đại ý thế )

Từ Windows hay VBA khi ta muốn truy xuất dữ liệu SQLite thì có vài cách sau:

1/ cài trình điều kiểm link sau sử dụng cho 32 hay 64 bit tuỳ ai đó ( xong từ VBA ta sử dụng ADODB truy xuất dữ liệu )

SQLite ODBC Driver

2/ Tự viết lấy hàm sử dụng truy xuất dữ liệu SQLite ( truy xuất trực tiếp file SQLite )

3/ Tự viết lấy như mục số 1

Vọc chơi một ngày chuyển thể thành công một hàm sử dụng ADODB từ VBA truy xuất dữ liệu File SQLite trả về Recordset As New Recordset

để sử dụng thuần ADODB cho SQlite trên VBA khi cần thiết ... Hàm API đơn giản như sau ... thong thả fix các kiểu xong thêm vào DLL bài số 1 sau

Declare PtrSafe Function GetRecordFromSQLite Lib "SQLiteWrapper.dll" _
            (ByRef DBPath As Variant, ByRef SQLQuery As Variant) As Variant ''Object

1/ Sử dụng phức thức GetRows của ADODB truy xuất dữ liệu file SQLite

Sub GetRows_FromSQLite()
    Dim DBPath As String
    Dim SQLQuery As String
    Rem Dim Recordset As Object
    Dim Recordset As New Recordset
    Dim data As Variant
    Dim RowCount As Long
    Dim ColCount As Long
    Dim i As Long
    Dim j As Long
  
    DBPath = "C:\Database_Server\MyDatabase.sqlite"
    SQLQuery = "SELECT * FROM MyTableName"


    ' G?i hàm DLL d? nh?n d? li?u
    Rem Set Recordset = GetSQLiteData(DBPath, SQLQuery) ''OK
    Set Recordset = GetRecordFromSQLite(DBPath, SQLQuery)
  
    Cells.ClearContents
    If Not Recordset Is Nothing Then
        ' S? d?ng GetRows d? chuy?n d?i d? li?u t? Recordset thành m?ng
        data = Recordset.GetRows
        ' Xác d?nh s? hàng và s? c?t trong m?ng
        RowCount = UBound(data, 2) + 1
        Debug.Print "RowCount " & RowCount
      
        ColCount = UBound(data, 1) + 1
        Debug.Print "ColCount " & ColCount
      
        ' Ðat tiêu de cot trong Excel
        For j = 0 To ColCount - 1
            Cells(1, j + 1).Value = Recordset.Fields(j).Name
        Next j
      
        ' Ðat du lieu tu mang vào bang tính Excel
        For i = 0 To RowCount - 1
            For j = 0 To ColCount - 1
                Cells(i + 2, j + 1).Value = data(j, i)
            Next j
        Next i
      
        Debug.Print "Data loaded successfully"
    Else
        Debug.Print "Failed to load recordset"
    End If
End Sub

2/ Sử dụng phức thức CopyFromRecordset Recordset của ADODB truy xuất dữ liệu file SQLite

Sub GetRecordset_FromSQLite()
    Dim i As Integer
    Dim DBPath As String
    Dim SQLQuery As String
    Dim Recordset As Object
    Dim conn As ADODB.Connection
  
    DBPath = "C:\Database_Server\MyDatabase.sqlite"
    SQLQuery = "SELECT * FROM MyTableName"
  
    Rem Set Recordset = GetSQLiteData(DBPath, SQLQuery)
    Set Recordset = GetRecordFromSQLite(DBPath, SQLQuery)
  
    Cells.ClearContents
  
    If Not Recordset Is Nothing Then
      ' Process the recordset here
        Debug.Print "Recordset loaded successfully"
  
        For i = 1 To Recordset.Fields.Count
            Cells(1, i).Value = Recordset.Fields(i - 1).Name
            Debug.Print Recordset.Fields(i - 1).Name
        Next i
    
        Range("A2").CopyFromRecordset Recordset
    Else
      MsgBox "Failed to load recordset"
    End If
End Sub

3/ Sử dụng phức thức Recordset.MoveNext của ADODB truy xuất dữ liệu file SQLite

Sub GetRecordset2_FromSQLite()
  Dim DBPath As String
  Dim SQLQuery As String
  Dim Recordset As New Recordset ' As Object
  Dim conn As ADODB.Connection

  DBPath = "C:\Database_Server\MyDatabase.sqlite"
  SQLQuery = "SELECT * FROM MyTableName"

  Rem Set Recordset = GetSQLiteData(DBPath, SQLQuery)
  Set Recordset = GetRecordFromSQLite(DBPath, SQLQuery)
 
  If Not Recordset Is Nothing Then
      ' Process the recordset here
      Debug.Print "Recordset loaded successfully"
    
        Cells.ClearContents
      ' Example: Display the data in Excel
      Dim i As Integer
      For i = 1 To Recordset.Fields.Count
        Cells(1, i).Value = Recordset.Fields(i - 1).Name
        Debug.Print Recordset.Fields(i - 1).Name
      Next i
  
      Dim Row As Long
      Row = 2
      Do Until Recordset.EOF
        For i = 1 To Recordset.Fields.Count
            Cells(Row, i).Value = Recordset.Fields(i - 1).Value
        Next i
        Row = Row + 1
        Recordset.MoveNext
      Loop
      Else
      MsgBox "Failed to load recordset"
  End If
End Sub

Cũng không nghĩ là nó đơn giản đến thế chỉ có khoãng trên 20 dòng code thôi ...

còn lại mọi cái Delphi viết cho rồi chỉ xem nguồn xong lôi ra sắp xếp viết lại thành một hàm nhỏ sử dụng cho ADODB truy xuất dữ liệu SQLite
khi cần thiết để gợi nhớ một chút ADODB trên Windows hay thói quen sử dụng ADODB

Còn phần ghi dữ liệu thì sử dụng các hàm file bài số 1 nên cũng không cần thiết viết lại làm gì cho nhọc xác ra

 

[Kiều Mạnh]

Như mô tả bài số 3 ... tây nó viết được tôi cũng mô phỏng bắt trước làm xem chơi cho vui thôi

và cũng chưa ai viết sử dụng ADODB lấy dữ liệu SQLite mà không phải cài đặt cả nên tôi viết cũng chơi cho có thôi ...

gọi nhớ một chút cách sử dụng ADODB . xuất hai hàm API sau

Declare PtrSafe Function GetRsFromSQLite Lib "FireMySQL64.dll" _
            (ByRef dbPath As Variant, ByRef sqlQuery As Variant) As Variant ''Object
   
 Rem ##########
 Declare PtrSafe Function GetRsFromSQLiteEx Lib "FireMySQL64.dll" _
            (ByRef dbPath As Variant, ByRef sqlQuery As Variant) As Variant ''Objec

Thực ra chỉ cần Hàm GetRsFromSQLite là đủ rồi nhưng tôi muốn lưu trữ lại cách viết nên viết thêm hàm GetRsFromSQLiteEx Mở rông như hàm GetRsFromSQLite và có thêm thêm chức năng như ( INSERT INTO`, `DELETE`, `CREATE TABLE`, `DROP TABLE) trong cùng một hàm .. có nghĩa sử dụng hàm này đọc và ghi dữ liệu vào file SQLite OK sạch

Lưu ý:
Hàm sử dụng thuần SQL theo tiêu chuẩn của SQLite
vì vậy nhiều câu lệnh SQL sử dụng chung với Access được
nhưng có câu lệnh SQL sẻ lỗi Vì vậy hãy đọc kỹ hướng dẫn sử dụng trước khi dùng ..

File úp phía dưới như file bài số 1 chỉ bổ sung thêm hai hàm trên

 

[Kiều Mạnh]

1/ Bạn nào rảnh hãy thử sử dụng thuần ADODB INSERT INTO 100.000 dòng vào File Access

2/ xong sử dụng hàm tôi viết cũng INSERT INTO 100.000 dòng vào File Access

3/ Với cấu trúc dữ liệu như nhau xong do thời gian xem tình tình sao tôi chưa thử

Trên máy tôi cấu trúc INSERT INTO 100.000 dòng chạy ok với code sau

Private Sub Test_InsertInto_MyTableName()
    Dim SQLBatch As String
    Dim i As Long
    Dim startTime As Single
    Dim endTime As Single
    Dim elapsedTime As Single
    Dim Result As Variant
    SQLBatch = ""
  
    If Not IsTableExists(DBsqlite, "MyTableName") Then
        Debug.Print "MyTableName not found"
        Exit Sub ' x? lý l?i n?u b?ng không t?n t?i
    End If
  
    Rem Measure start time
    startTime = Timer
  
    Rem ############ chen mot dong SQL
    Rem SQLBatch = "INSERT INTO MyTableName (Name, Age) VALUES ('John Doe', 30);"
  
    For i = 1 To 100000
        SQLBatch = SQLBatch & "INSERT INTO MyTableName (Name, Age) VALUES ('John Doe " & i & "', 30);"
    Next i
    Rem Debug.Print SQLBatch
  
    Result = GetRsFromSQLiteEx(DBsqlite, SQLBatch)
  
    Rem Kiem tra ket qua
    If IsNull(Result) Then
        Debug.Print "Data inserted successfully!"
    Else
        Debug.Print "An error occurred while inserting data."
    End If
  
    Rem Measure end time
    endTime = Timer
  
    Rem Calculate elapsed time
    elapsedTime = endTime - startTime
  
    Rem Output the elapsed time in seconds
    Debug.Print vbLf & "Elapsed Time: " & elapsedTime & " seconds."
End Sub

hàm GetRsFromSQLiteEx và ExecuteSQL nhận tham số SQL vào hàm từ 1 dòng lệnh hay một lô lệnh SQL điều sử dụng tốt

Và tuân thủ theo cấu trúc của nó là OK Ví dụ 100.000 dòng SQL

For i = 1 To 100000
        SQLBatch = SQLBatch & "INSERT INTO MyTableName (Name, Age) VALUES ('John Doe " & i & "', 30);"
Next i
Rem Debug.Print SQLBatch

dựa vào đó tuỳ chỉnh theo ý mục đích sử dụng của ai đó