Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665

    Unanswered: Simple SQL Statment!!

    I want to set a variable in VBA equal to a select statment.
    I have been trying the docmd.runsql statment but it will not compile ie
    test = DoCmd.RunSQL ("select count(*) from dbo_DW_WQAS_ALL_TBL")

    how should i go about this?
    Jim

  2. #2
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238

    Re: Simple SQL Statment!!

    Originally posted by JDionne
    I want to set a variable in VBA equal to a select statment.
    I have been trying the docmd.runsql statment but it will not compile ie
    test = DoCmd.RunSQL ("select count(*) from dbo_DW_WQAS_ALL_TBL")

    how should i go about this?
    Jim
    A SQL statement is just a string variable. The output from a SQL statement is a recordset. You can use

    Dim strMySQL As String

    strMySQL = "SELECT * FROM MyTable"

    Docmd.RunSQL(strMySQL)

  3. #3
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: Simple SQL Statment!!

    Originally posted by JDionne
    I want to set a variable in VBA equal to a select statment.
    I have been trying the docmd.runsql statment but it will not compile ie
    test = DoCmd.RunSQL ("select count(*) from dbo_DW_WQAS_ALL_TBL")

    how should i go about this?
    Jim
    RunSQL is set up for action queries.

    From help files:

    The RunSQL method carries out the RunSQL action in Visual Basic.

    expression.RunSQL(SQLStatement, UseTransaction)

    expression Required. An expression that returns one of the objects in the Applies To list.

    SQLStatement Required Variant. A string expression that's a valid SQL statement for an action query or a data-definition query. It uses an INSERT INTO, DELETE, SELECT...INTO, UPDATE, CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX, or DROP INDEX statement. Include an IN clause if you want to access another database.

    Are you trying to establish a recordset in your variable or just the string?

    Gregg

  4. #4
    Join Date
    Oct 2003
    Posts
    66
    I do it like this...i think it is old way to do it.

    Dim SQL as string
    Dim RS as recordset

    SQL = "SELECT * FROM TableName"

    Set RS = currentdb.openrecordset(SQL)

  5. #5
    Join Date
    Mar 2003
    Location
    Bogota
    Posts
    67
    use DCount:

    test = DCount("*","dbo_DW_WQAS_ALL_TBL")

    look it up on help for more info

    There's also DMax, DFirst, DLookup... all useful

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •