Data Definition query with parameters

[MissTipps]

I'm a SQL Server developer by day, and struggling to tackle an Access issue which I would usually handle with a stored procedure.

I need to create a table on the fly (using VBA) and add the column names based on data from another table. I've created a Data Definition query and thought that I could get away with enclosing a variable name in square brackets and then use an ADO command object to execute the query with a parameter object to append the required column name. Not so!!

Is there a way that I can use a parameter in a Data Definition query, in the same way that I could use a parament in a stored procedure.

Please help before I lose all of my hair.

Thanks

 

[tlbroadbent]

Enclosing the name in brackets would indicate that the enclosed value was to be the name of the table or column. It would not be handled as a parameter.

VBA modules are essentially the stored proceures of the Access environment. You can pass parameters to modules and use the parameters to build a SQL DDL statement in a string variable. Then you can execute the statement.

Dim sSQL As String

' Assume sCol1, sDataType1, sCol2, etc are variables
' loaded from the other table or passed to the module.

sSQL = "Create Table " & sTblName & "(" & _
sCol1 & " " & sDataType1 & "," & _
sCol2 & " " & sDataType2 & "," & _
... ' More columns
&quot"

Conn.Execute(sSQL) ' alternate DoCmd.RunSQL(sSQL) Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[MissTipps]

Thanks ever so much Terry. Whenever the pressure is on I can trust myself to take a sledgehammer to crack a nut.