Snasui Excel Forum

Code: Select all

Private Sub CreateDbFile()
    Dim objAccess As Access.Application, aCn As ADODB.Connection _
            , strCnString As String
    strPath = ThisWorkbook.Path & "\dbstk"
    'check if the database folder is existed
    'if it not, create it
    If Len(Dir(strPath, vbDirectory + vbHidden)) = 0 Then
        Call MkDbFolder
    End If
    
    'check if actual database file is available
    'if not, create Access database and some tables
    strPath = strPath & "\" & Format(Now(), "[$- ]yyyy")
    If Len(Dir(strPath & "*")) = 0 Then
        Set objAccess = New Access.Application
        Call objAccess.NewCurrentDatabase(strPath)
        objAccess.Quit
        
        strCnString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" _
            & strPath & ".accdb;"
        Set aCn = New ADODB.Connection
        aCn.Open strCnString
        aCn.Execute "CREATE TABLE Transaction (" _
            & "FirstName CHAR, LastName CHAR)"
        aCn.Close
    End If
    Set objAccess = Nothing
    strPath = ""
End Sub

Code: Select all

Private Sub CreateDbFile()
    Dim objAccess As Access.Application, aCn As ADODB.Connection _
            , strCnString As String
	'.........
        With aCn
            .Open strCnString
            .Execute "CREATE TABLE TSDetail (" & _
                "DateDoc datetime, DocNo varchar, " & _
                "WhoCode mediumint)"
            .Close
        End With
    '.........
End Sub