Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    London, United Kingdom
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    adox.catalog (2000)

    I am trying to update the sql of an access stored query using the following code:
    Public Function UpdateQuery(pstrQueryName As String, pstrSQL As String)
    Dim cat As New ADOX.Catalog

    Set cat.ActiveConnection = moCNN
    cat.Views(pstrQueryName).Command = pstrSQL

    End Function

    the code works fine, it just takes a long time to run the code.

    to open the connection I have
    Set moCNN = New ADODB.Connection
    strmocnn = "PROVIDER=MICROSOFT.JET.OLEDB.4.0;" & _
    "Data Source=" & strDataSource & ";" & _
    "Jet OLEDB:System database=" & strSystemDB & ";" & _
    "User ID=" & strUserID & ";" & _
    "PASSWORD=" & strPWD & ";"

    moCNN.Open strmocnn
    which again works fine. Is there anything I can do to make the query sql update run a little quicker?

    Any help would be much appreciated.

    Amanda

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: adox.catalog (2000)

    Amanda,

    The most important factors are probably checking security, network traffic and number of active users. I'd expect the actual updating of the SQL to take very little time.

    You could try using DAO instead of ADO/ADOX - it's usually a bit faster:

    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    Set dbs = DBEngine.OpenDatabase(strDataSource)
    Set qdf = dbs.QueryDefs(pstrQueryName)
    qdf.Sql = pstrSQL
    Set qdf = Nothing
    dbs.Close
    Set dbs = Nothing

  3. #3
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    London, United Kingdom
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: adox.catalog (2000)

    Thanks Hans

    That has helped

Posting Permissions

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