Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Posts
    13

    Unanswered: SQL and parameters

    Hey,
    I'm using RunSQL to run a query.
    my question is how I can use parameters with this command,I mean
    the table name is a parameter.
    For example,
    SELECT TableName(as parameter).Filed1.....

    Where I can get the tables of the database in the vb code.
    est regards...

  2. #2
    Join Date
    Mar 2004
    Location
    Brighton, UK
    Posts
    25

    Re: SQL and parameters

    Are you saying that you've got a saved query with parameters and you want to call it using code? If so I think you'll need to use ADO to create a command and parameter object.

    If you want to construct a query using vb code, you need to create a SQL string, eg:

    Public Sub DoQuery(strTableName as string)

    strSQL = "Select * from " & strTableName
    docmd.runSQL strtSQL

    End Sub

    Hope this makes sense. If what you want to do is the first paragraph above, you need something like:


    Dim cmd As New ADODB.Command
    Dim prm As New ADODB.Parameter

    cmd.ActiveConnection = CurrentProject.Connection
    cmd.CommandText = "qryTest"
    cmd.CommandType = adCmdStoredProc
    prm.Type = adInteger
    prm.Value = 40004
    cmd.Parameters.Append prm
    cmd.Execute


    where qryTest is a saved query with a parameter set up for it .... I think .... !!!

  3. #3
    Join Date
    Apr 2004
    Posts
    13

    Re: SQL and parameters

    Originally posted by andysiant
    Are you saying that you've got a saved query with parameters and you want to call it using code? If so I think you'll need to use ADO to create a command and parameter object.

    If you want to construct a query using vb code, you need to create a SQL string, eg:

    Public Sub DoQuery(strTableName as string)

    strSQL = "Select * from " & strTableName
    docmd.runSQL strtSQL

    End Sub

    Hope this makes sense. If what you want to do is the first paragraph above, you need something like:


    Dim cmd As New ADODB.Command

    Thanks allot for your help,
    My question is how I can send the Table Name for the DoQuery function
    in the calling function (Public Sub DoQuery(strTableName as string))
    I mean where can I get the all the tables names of my database to send
    them as aparameter

    best regards...
    Dim prm As New ADODB.Parameter

    cmd.ActiveConnection = CurrentProject.Connection
    cmd.CommandText = "qryTest"
    cmd.CommandType = adCmdStoredProc
    prm.Type = adInteger
    prm.Value = 40004
    cmd.Parameters.Append prm
    cmd.Execute


    where qryTest is a saved query with a parameter set up for it .... I think .... !!!

  4. #4
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    DoCmd.RunSQL is used only for action Queries, such as INSERT INTO , SELECT ...... INTO or DELETE ...... FROM

  5. #5
    Join Date
    Mar 2004
    Location
    Brighton, UK
    Posts
    25
    If you want to get a list of all tables in your database, then you could use an ADO Catalog, eg:

    Dim cat As New ADOX.Catalog
    Dim tbl As ADOX.Table

    cat.ActiveConnection = CurrentProject.Connection
    For Each tbl In cat.Tables
    call MyFunction(tbl.Name)
    Next

Posting Permissions

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