change table name to variable

[Knife1]

Hi

I import a text file to an Access table with transfer text method with the table name being the parameter the user enters into the text box of a form.
How can I make the tablename into a variable that I can run a SQL alter table command on?




Private Sub ImportStats_Click()

DoCmd.TransferText acImportDelim, "stats_spec", _
Me.msgboxStats, Me.msgboxStatsImport, False

DoCmd.RunSQL "ALTER TABLE tablename ALTER COLUMN _
columnname TEXT(5)"






Thanks

[Knife1]

Hi actually I fixed the problem by changing the message box value to a variable before I import the table but I still don't know how to run a SQL command where the table name is a variable. Is it possible? I suppose I could use doCmd.Rename to rename the tablename variable to a string.



Private Sub ImportPatientStats_Click()

Dim tblstats As String

tblstats = msgboxStats

DoCmd.TransferText acImportDelim, "patient_stats_spec", _
tblstats, Me.msgboxPatientImport, False

DoCmd.RunSQL "ALTER TABLE tblvariable ALTER COLUMN prac TEXT(5)"

End Sub

[hansup]

You can substitute the variable's value for the variable's name when you build the SQL statement.

"ALTER TABLE " & tblvariable & " ALTER COLUMN prac TEXT(5)"
I would try something like this:

Private Sub ImportPatientStats_Click()
Dim tblstats As String
Dim strSql As String
Dim strMsg As String

On Error GoTo ErrorHandler

tblstats = msgboxStats

DoCmd.TransferText acImportDelim, "patient_stats_spec", _
tblstats, Me.msgboxPatientImport, False

'DoCmd.RunSQL "ALTER TABLE tblvariable ALTER COLUMN prac TEXT(5)"
strSql = "ALTER TABLE " & Me.msgboxStats & " ALTER COLUMN prac TEXT(5)"
Debug.Print "strSql: " & strSql
CurrentDb.Execute strSql, dbFailOnError

ExitHere:
On Error GoTo 0
Exit Sub

ErrorHandler:
strMsg = "Error " & Err.Number & " (" & Err.Description _
& ") in procedure ImportPatientStats_Click"
MsgBox strMsg
GoTo ExitHere

End Sub

[Knife1]

OK thanks, this is an option