VBA variable in SQL?

[alexbeatle]

Good day,

Can an SQL statement contain a VBA variable?
I'm trying this:

DoCmd.RunSQL "ALTER TABLE tbl_outlooktemp ADD COLUMN 'Categories'&[Count] text;"

I want to add columns to table with names Categories1, Categories2, ...etc.

Thank you

 

[RainLover]

The attached is something I used for a one off occasion.

If you search through the code I believe you will find something that will help.

This is untested and I guarantee nothing.

Have fun with it and if suitable please fell free to copy use etc.

 

[alexbeatle]

The attached is something I used for a one off occasion.

If you search through the code I believe you will find something that will help.

This is untested and I guarantee nothing.

Have fun with it and if suitable please fell free to copy use etc.

Thanks how can I view the VBA for it in Access 2010?

 

[alexbeatle]

Thanks how can I view the VBA for it in Access 2010?

Nevermind, got it open by first opening Access 2010 with its VBA editor and then openning the Utilities2003 database
Thanks

 

[Rx_]

To expand on other SQL using variables, the final SQL String is evaluated regardless of literal values or values that came from variables.
SQL Server provides 6 views that show up as linked tables in my Access DB.
The Descending field is always field number 3 but has a different name in each of the 6 views.
This single function can call all 6 views that have the same basic structure.
The variables that make the function call include the Table Name, the field used for Descending order, and the Primary Key for Wells.

By stepping through the code, the strSQL will hold a string value. This string value can be placed into an Access new Query window to display the result.

Some people prefer to build a query that contains parameters.
My preference is to build each query on demand as shown.

Be advised, one extra or missing space can make a difference.

Function HasAPDWithDate(LinkedTableViewName As String, DecendingField As String, ID_Wells As Integer) As RE_APDResults
   Dim strSQL
   Dim db As Database
   Dim rstAPD As Recordset
        On Error GoTo Err_MyProc
        Set db = CurrentDb()
        strSQL = "SELECT " & LinkedTableViewName & ".ID_Wells ,  " & LinkedTableViewName & " .* FROM " & LinkedTableViewName & _
                " WHERE (((" & LinkedTableViewName & ".ID_Wells)=" & ID_Wells & ")) ORDER BY " & LinkedTableViewName & "." & DecendingField & " DESC;"
        Set rstAPD = db.OpenRecordset(strSQL, 2, dbSeeChanges)
' rest of function not shown

 

[RainLover]

Nevermind, got it open by first opening Access 2010 with its VBA editor and then openning the Utilities2003 database
Thanks

Hope the demo helps.

Des