Running a query (w/ parameters) from Autocad

[Anonymous]

How can I run a query that resides in an Access db that requires parameters? More specifically, how can I send those parameters to the query?

Set rsinfo = dbinfo.OpenRecordset("danqryallsourcesforpart", dbOpenDynaset)

This works fine as long as the query doesn't require parameters. If I change the query such that it requires a parameter to function, I get an error "Too few parameters".

Thanks in advance!

[Anonymous]

Hope this helps. I'm certainly no expert on this
stuff, so I may not be able to explain this very well, but this works for
me. Rather than running a query, open the recordset using a SQL
statement that queries the Access tables directly. Then you can incorporate your
VBA variables directly into the SQL statement.

I don't know SQL very well,  so I
just create a query in Access that produces the records I want, then go to
the SQL view of the query and copy the SQL statement into the VBA module.
You may have to add brackets around the table and field names.

I use databases to construct layer snapshots, and
use code such as the following to read the database. Note the use of the
snapname string variable in constructing the SQL statement.

Public Function db_readsnap(snapname as
String) 

    Dim conn As
ADODB.Connection
    Dim rsLayers As
ADODB.Recordset
    Dim conString As String, selstr As
String

    Dim snapkey As String,
snaptitle As String
    conString = "File Name=" &
_
       
ThisDrawing.Application.Preferences.Files.WorkspacePath &
_
       
"\laycat.udl"
    Set conn = New
ADODB.Connection
    conn.Open conString
   
selstr = "SELECT [define-snapshots].[snapshot_id],[define-snapshots].[name],"
&
_
                 
"[define-snapshots].[description]," &
_
                 
"[define-snapshots].[scale],[Data-Display_configs].[config_name] " &
_
            
"FROM [define-snapshots] " &
_
            
"INNER JOIN [Data-Display_configs] " &
_
            
"ON [define-snapshots].[display_config_id] =
[Data-Display_configs].[display_config_id] " &
_
            
"WHERE [define-snapshots].[name] =  """ & snapname &
""""
    Set rsLayers = New
ADODB.Recordset
    With
rsLayers
        .Open selstr, conn,
_
            
adOpenForwardOnly, adLockReadOnly,
adCmdTableDirect
            
If Not .EOF
Then
               
Do Until
.EOF
                   
snapkey = !snapshot_id

       
            snaptitle =
!name                   

size=2>                  
 .MoveNext
               
Loop
       
.Close
        End
If
    End With
    conn.Close
End
Function

style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">

"dbrew0" <dfbrewer@att.net>
wrote in message
href="news:f12bac2.-1@WebX.maYIadrTaRb">news:f12bac2.-1@WebX.maYIadrTaRb...

How
can I run a query that resides in an Access db that requires parameters? More
specifically, how can I send those parameters to the query?

Set rsinfo = dbinfo.OpenRecordset("danqryallsourcesforpart", dbOpenDynaset)

This works fine as long as the query doesn't require parameters. If I
change the query such that it requires a parameter to function, I get an error
"Too few parameters".

Thanks in advance!

[Ed__Jobe]

The QueryDef object has a Parameters collection you can use. Here is a sample:

Public Sub ParameterTest()



    Dim dbs As Database

    Dim qdf As QueryDef

    Dim rst As Recordset



    ' Return Database object pointing to current database.

    Set dbs = CurrentDb

    'Open a QueryDef object.

    Set qdf = dbs.QueryDefs("MyQuery")

    ' Supply values for parameters.

    qdf.Parameters![Enter today's date] = #11/11/02#

    'Open recordset

    Set rst = qdf.OpenRecordset()    



End Sub

[Anonymous]

Thanks guys!

I'm more comfortable at this time with DAO solution, and was able to get it to work that way. I'll have to try the ADO & SQL solution as I know that is wave of the future (and present).

Thanks again!

[Ed__Jobe]

Actually, the code sample is DAO.