Upgrade Access2000 to AccessProject cont...

Hi Guys,
How can I create a QueryDef in Access Project?

Because in my db2K I have a Advance Criteria Manager where you can defined your own criteria and Save for various used. Then I have
a number of Query linked to that QueryDef (ContactsID<>ContactsID) which is the source of my Reports, Filtering, and
Customized
Phonebook etc...

Any help is highly appreciated...

TIA,
George

PS. Sorry for flooding question about "Upgrade Access2000 to AccessProject", I just really need to finish this ASAP.

queries become views...

-----Original Message-----
From: George Oro [mailto:george@c...]
Sent: Wednesday, November 20, 2002 4:45 AM
To: Access
Subject: [access] QueryDef in Access Project


Upgrade Access2000 to AccessProject cont...

Hi Guys,
How can I create a QueryDef in Access Project?

Because in my db2K I have a Advance Criteria Manager where you can defined
your own criteria and Save for various used. Then I have
a number of Query linked to that QueryDef (ContactsID<>ContactsID) which is
the source of my Reports, Filtering, and Customized
Phonebook etc...

Any help is highly appreciated...

TIA,
George

PS. Sorry for flooding question about "Upgrade Access2000 to AccessProject",
I just really need to finish this ASAP.

...and here's a little function for creating views on the fly. Make sure your users have CREATE VIEW
permissions before using it.

Public Function CreateView(ByVal strViewName As String, strSQL As String) As String
' Code Header inserted by the Procedure Header Add-In
'=============================================================
' mdlADPRecord.CreateView
'-------------------------------------------------------------
' Purpose
' Author : Brian Skelton, 13-01-2002
' Notes : Create a adhoc view
'-------------------------------------------------------------
' Parameters
'-----------
' The proposed name for the view:strViewName (String)
' THe views SQL statement:strSQL (String)
'-------------------------------------------------------------
' Returns: THe views actual name (string)
'-------------------------------------------------------------
' Revision History
'-------------------------------------------------------------
' 13-01-2002 BDS:
'=============================================================
' End Code Header block
Dim cmd As ADODB.Command
Dim strCommand As String
Dim intCount As Integer
Dim intBreakLoop As Integer
Const ERR_VIEW_EXISTS = -2147217900
On Error GoTo errCreateView

    If Nz(strViewName, "") <> "" And Nz(strSQL, "") <> "" Then
        ' Create the command representing the view.
        Set cmd = New ADODB.Command
        cmd.ActiveConnection = CurrentProject.Connection
        strCommand = "CREATE VIEW " & strViewName & " AS " & strSQL
        cmd.CommandText = strCommand
        cmd.CommandType = adCmdText
        On Error Resume Next
        cmd.Execute
        'If there is an existing view of the same name
        If Err.Number = ERR_VIEW_EXISTS Then
            'Add a random number to the view name and try again (for 100 times)
            Do While Err.Number <> 0 And intBreakLoop < 100
                Err.Clear
                intBreakLoop = intBreakLoop + 1
                Randomize
                intCount = Int((1000) * Rnd + 1)
                strCommand = "CREATE VIEW " & strViewName & CStr(intCount) & " AS " & strSQL
                cmd.CommandText = strCommand
                cmd.CommandType = adCmdText
                cmd.Execute
            Loop
        End If
        
        'Select the database window and update it so Access can see the new VIew
        Application.Echo False
        DoCmd.SelectObject acServerView, , True
        SendKeys "{f5}", True
        'Hide the database window
        DoCmd.RunCommand acCmdWindowHide
        Application.Echo True
        'Return the name of the new view
        If intCount <> 0 Then
            CreateView = strViewName & CStr(intCount)
        Else
            CreateView = strViewName
        End If
    End If

    DoEvents
    
exitCreateView:
    Set cmd = Nothing
    Exit Function
    
errCreateView:
    MsgBox Err.Description
    Resume exitCreateView

End Function

And this one deletes a view:

Public Sub DeleteView(strViewName As String)
' Code Header inserted by the Procedure Header Add-In
'=============================================================
' mdlADPRecord.DeleteView
'-------------------------------------------------------------
' Purpose
' Author : Brian Skelton, 13-01-2002
' Notes : Delete the supplied view
'-------------------------------------------------------------
' Parameters
'-----------
' View name:strViewName (String)
'-------------------------------------------------------------
' Returns:
'-------------------------------------------------------------
' Revision History
'-------------------------------------------------------------
' 13-01-2002 BDS:
'=============================================================
' End Code Header block
Dim cmd As ADODB.Command
Dim strCommand As String
On Error GoTo errDeleteView

    ' Create the command to DROP the view from SQL Server.
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = CurrentProject.Connection
    strCommand = "DROP VIEW " & strViewName
    cmd.CommandText = strCommand
    cmd.CommandType = adCmdText
    On Error Resume Next
    cmd.Execute

exitDeleteView:
    Set cmd = Nothing
    Exit Sub
    
errDeleteView:
    MsgBox Err.Description
    Resume exitDeleteView

End Sub

Brian

>  from:    "Carnley, Dave" <dcarnley@a...>
>  date:    Wed, 20 Nov 2002 15:32:17
>  to:      access@p...
>  subject: Re: [access] QueryDef in Access Project
> 
> queries become views...
> 
> -----Original Message-----
> From: George Oro [mailto:george@c...]
> Sent: Wednesday, November 20, 2002 4:45 AM
> To: Access
> Subject: [access] QueryDef in Access Project
> 
> 
> Upgrade Access2000 to AccessProject cont...
> 
> Hi Guys,
> How can I create a QueryDef in Access Project?
> 
> Because in my db2K I have a Advance Criteria Manager where you can defined
> your own criteria and Save for various used. Then I have
> a number of Query linked to that QueryDef (ContactsID<>ContactsID) which is
> the source of my Reports, Filtering, and Customized
> Phonebook etc...
> 
> Any help is highly appreciated...
> 
> TIA,
> George
> 
> PS. Sorry for flooding question about "Upgrade Access2000 to AccessProject",
> I just really need to finish this ASAP.
> 
> 
> 
> 
> 
> 
>