Creating a SQL table from Excel VBA

Question

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

Is there a way to raise the name in "tblName" or somehow change the name after creating it with the name that the 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, " & _

For this:

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