Use an Excel VBA Variable for a field name in an DAO SQL statement for Access

Tue, 23 Jun 2009 21:45:32 Z

As you can see below in the code the " & tblName & " will work fine but instead of using the BankName (which the user cannot change because it is in the code) I would like to do the same as I did with the Table name and use a variable that gets its name from an excel spreadsheet rather that an actual name hard coded in a VBA module. I would like the variable field name entered in the Excel Spreadsheet by the user just like the table name is filled in by the user.

The problem is getting the ADO SQL string ( cmd.CommandText = "ALTER TABLE " & tblName & " Add Column BankName Char(25)" ) to recognize that the value in the Variable is the field name.

Sub AddFieldToAccess()

Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim tblName as string     'User to input Table Name in cell C2
Dim fldName as string     'User to input Field Name in cell C3

tblName = ActiveSheet.Range("C2").Value        'Table Name
fldName = ActiveSheet.Range("C3").Value        'Field Name

'Open Access Database
Set cnn = New ADODB.Connection
    With cnn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Open MyConn
    End With

    Set cmd = New ADODB.Command
    Set cmd.ActiveConnection = cnn
    'create table

'I have already tried to use the same approach as the table Variable and no work

cmd.CommandText = "ALTER TABLE " & tblName & " Add Column " & fldName & " Char(25)"
    cmd.Execute , , adCmdText

'If I do this I end up with a field named BankName

cmd.CommandText = "ALTER TABLE " & tblName & " Add Column BankName Char(25)"
    cmd.Execute , , adCmdText

    Set cmd = Nothing
    Set cnn = Nothing
End Sub

Use an Excel VBA Variable for a field name in an DAO SQL statement for Access

Wed, 24 Jun 2009 07:20:21 Z

Hi,

This forum is for SQL Data Services, a Microsoft's Cloud database service offering. You have to raise this in a proper forum from forum home: http://social.msdn.microsoft.com/Forums/en-US/categories

Thanks
Kapil