Use ADO to create querydef 1

[mfd777]

Hi All,

Can't find the answer for this one but it is probably staring me in the face!

We are re-writing an application from Access 97 to Access 2003. The front end is access and the back end is SQL Server.

In the '97 version we create pass-though queries as the recordsource for our forms, use DAO, as follows:

Declarations:
Dim mysql as string
Dim dbs as database
Dim qdfname as querydef

mysql="select * from customers where cust_id=12345"

dbs.QueryDefs.Delete "Customers_qry"

set qdfname=dbs.CreateQueryDef("Customers_qry")
qdfname.sql=mysql
qdf.Connect="my connection string goes here"
qdfname.ReturnRecords=True
qdfname.close
dbs.close


Therefore on a customers form, the record source is a pass-through query running on the server with a where clause of the current customer id.

We are using ADO in Access 2003 so I am looking for a way to either re-create queries as above, or set the recordsource of a form dynamically using SQL generated in ADO (which may return more than one record).

Also, if it is possible to re-create querydefs, is it possible to create using a OLEDB connection rather than ODBC and also a connecvtion that does not require a DSN

Thanks in advance for any advice.

 

[Waud]

Hi

To recreate the procedure using ADO (& ADOX) something along the lines of the code shown below should work.

I'm still using Access(97) at work so haven't been able to test this but I think it should be OK.

Sub CreateProcedure()
    Dim cxnDB As New ADODB.Connection
    Dim cmd As New ADODB.Command
    Dim cat As New ADOX.Catalog

    'open connection
    cxnDB.Open "ConnectionString"
    
    'Open catalog
    Set cat.ActiveConnection = cxnDB
    
    'Delete existing procedure
    cat.Procedures.Delete strProcedureName
    
    'Create new procedure
    cmd.CommandText = strSQLStatement
    cat.Procedures.Append "ProcedureName", cmd
    
    
    'Clean up
    cxnDB.Close
    Set cat = Nothing
    Set cmd = Nothing
    Set cxnDB = Nothing
    
            
End Sub

Instead of deleting and re-createing the query you may be better of using a paramertized command and change the parameters depending on the "cust_id".

Hope this helps

Waud

 

[p27br]

have you considered using an ADP ?

 

[mfd777]

Thanks for the advice guys. I was trying to avoid creating SQL Server objects such as views and Stored Procedures. Perhaps a paramaterised stored procedure is the way to go. This should negate any inefficiencies used by using ODBC rather than OLDDB?