I am new to VBA. I am trying to create a stored procedure with a parameter (StnNo) to replace the table number (in this case 9037) in the following query:



SELECT DateValue([Day] & "/ " & [Month] & "/" & [Yr]) AS [Date], [9037].* FROM 9037;



The reason for doing so is that I have a couple of hundred tables that I wish to run a series of linked queries on, and combine the output. Currently I have to change the table name in the above query each time prior to running the linked queries.



I have tried a number of variations with/without parenthesis on StnNo in the following code, all with the same result.




PHP Code:





 Sub CreateProcedure()

'Objective is to pass a table name to a query

    Dim Catalog As New ADOX.Catalog

    Set Connection = CurrentProject.Connection

    Dim Command As New ADODB.Command

    Set Command.ActiveConnection = Connection

    Command.CommandText = "PARAMETERS [StnNo} Text;" & "SELECT DateValue([Day] & ' / ' & [Month] & ' / ' & [Yr]) AS [Date], & 'StnNo' &.* FROM  & 'StnNo' & ; "

    Set Catalog.ActiveConnection = Connection

    Call Catalog.Procedures.Append("SourceTable", Command)

 'At this point get a run-time error "-2147217900(80040e14)' : Syntax error in PARAMETER clause

    Set Command = Nothing

    Set Connection = Nothing

End Sub 







Any help appreciated



Chris