VBA TIPS
  Making a MBD file then adding a data to the Access table

You can make a MDB file (MS Access file) using DAO. Also, you can add/ update the data in the MDB from Excel using ADO.

Here is an example. (Assume you have the following data in the Excel worksheet.)



 

?@


Example
Sub make_a_mdb_file_by_DAO() makes a MDB file.

Sub set_data_to_the_created_mdb_file_by_ADO() adds a data to the MDB file.

 Note: Need to tick references Microsoft DAO x.x Object Library and Microsoft ActiveX Data Objects x.x Library.




Option Explicit

'Change here to the appropreate name
Const sFilename As String = "sample.mdb"
Const sTblName As String = "MyTable"

'the following procedure would make a mdb file.
Sub make_a_mdb_file_by_DAO()
    Dim sMdbPath As String
    Dim dbMdb As Database
    Dim mTblDef As TableDef
    Dim Idx As DAO.Index

    sMdbPath = ThisWorkbook.Path & "\" & sFilename

    'If the MDB file already exists, KILL it.
    If Dir(sMdbPath) <> "" Then Kill sMdbPath

    'Make a MDB file
    Set dbMdb = CreateDatabase(sMdbPath, dbLangGeneral)

    'Make a TableDef
    Set mTblDef = dbMdb.CreateTableDef(sTblName)

    'Add fields
    With mTblDef
        .Fields.Append .CreateField("ID", dbLong, 4)    'If you don't need ID field, comment out this line
        .Fields.Append .CreateField("Contact", dbText)
        .Fields.Append .CreateField("Phone", dbText)
        .Fields.Append .CreateField("City", dbText)

    '    Setting AutoNumber ustring Attributes property
        mTblDef("ID").Attributes = dbAutoIncrField    'If you don't need ID field, comment out this line

    '    Make an index object
        Set Idx = .CreateIndex("ID")    'If you don't need ID field, comment out this line
        Idx.Fields.Append Idx.CreateField("ID", dbLong)    'If you don't need ID field, comment out this line

    '    Setting a primary key
        Idx.Primary = True
        .Indexes.Append Idx
    End With

    ' Save Table info to the database
    dbMdb.TableDefs.Append mTblDef
    dbMdb.Close
End Sub

'the following procedure would update data in the mdb file.
Sub set_data_to_the_created_mdb_file_by_ADO()
    Dim cnn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sql As String
    Dim sMdbPath As String
    Dim i As Long
    Dim buf As Variant

    Set cnn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    sMdbPath = ThisWorkbook.Path & "\" & sFilename

    cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
                         & "Data Source=" & sMdbPath
    cnn.Open

    'Delete a previous data fromthe table
    'if you do not want to delete the previous data, just comment out
    'the following 2 code lines.
    sql = "DELETE FROM " & sTblName
    cnn.Execute (sql)

    sql = "SELECT * FROM " & sTblName
    rs.Open sql, cnn, adOpenStatic, adLockOptimistic

    'Data range in the wks
    buf = Range([A2], [A65536].End(xlUp).Offset(, 3)).Value

    'Add and Update data table
    With rs
        For i = 2 To UBound(buf)
            .AddNew
            !Contact = buf(i, 1)
            !Phone = buf(i, 2)
            !City = buf(i, 3)
            .Update
        Next
    End With

    rs.Close
    Set rs = Nothing
End Sub

| HOME |
Copyright © cellmasters.net - colo's junk room All Right Reserved
Tips and Information about Microsoft Excel|Masaru Kaji aka Colo