Create a SQL Table from Excel VBA

Question

The problem is that I cannot get the table name that was entered into the variable, "tblName", to be used instead I get a correctly named database with a table named "tblName".

Is there some way to pick up the name in "tblName" or some way to change the name once it is created with a name th user enters?

Private Sub CreateDatabaseFromExcel()

    Dim dbConnectStr As String
    Dim Catalog As Object
    Dim cnt As ADODB.Connection
    Dim dbPath As String
    Dim tblName As String

    'Set database name in the Excel Sheet
    dbPath = ActiveSheet.Range("B1").Value 'Database Name
    tblName = ActiveSheet.Range("B2").Value 'Table Name

    dbConnectStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath & ";"

    'Create new database using name entered in Excel Cell ("B1")
    Set Catalog = CreateObject("ADOX.Catalog")
    Catalog.Create dbConnectStr
    Set Catalog = Nothing

    'Connect to database and insert a new table
    Set cnt = New ADODB.Connection
    With cnt
        .Open dbConnectStr
        .Execute "CREATE TABLE tblName ([BankName] text(50) WITH Compression, " & _
                 "[RTNumber] text(9) WITH Compression, " & _
                 "[AccountNumber] text(10) WITH Compression, " & _
                 "[Address] text(150) WITH Compression, " & _
                 "[City] text(50) WITH Compression, " & _
                 "[ProvinceState] text(2) WITH Compression, " & _
                 "[Postal] text(6) WITH Compression, " & _
                 "[AccountAmount] decimal(6))"
    End With
    Set cnt = Nothing

End Sub

Answer 1

Change this line:

.Execute "CREATE TABLE tblName ([BankName] text(50) WITH Compression, " & _

To this:

.Execute "CREATE TABLE " & tblName & " ([BankName] text(50) WITH Compression, " & _