Announcement

Collapse
No announcement yet.

Macro Running Sql Query

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Macro Running Sql Query

    I want to write some VBA that retrieves data from a lotus notes database into excel. I will then have some other code that manipulates the data for the user. I have managed to do this with MS Query manually. However when I start to put into VBA my problems start.

    As I do not know Sql I use the macro record function. This worksso far so good. The problems seem to start when I re-run the macro and try to save the workbook. Excel just sits there with the CPU running at 99%. I left the pc for an hour and it was still in the same condition with the status bar showing save.

    Any ideas as it is very frustrating.

    I have pasted the macro recorded below:
    Code:
    Sub Macro4()
    '
    ' Macro4 Macro
    ' Macro recorded 02/04/2007 by cjohn
    '
    
    '
        With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
            "ODBC;DSN=premax;Database=Premax\se000273.nsf;Server=local;UserName=John Cross/bsp;EncryptMaxSubquery=20;MaxStmtLen=4096;MaxRels=20;M" _
            ), Array( _
            "axVarcharLen=1024;KeepTempIdx=1;MaxLongVarcharLen=1024;ShowImplicitFlds=0;MapSpecialChars=1;ThreadTimeout=60;" _
            )), Destination:=Range("E1"))
            .CommandText = Array( _
            "SELECT ""7___Special___c___Import_Export___e___Fault___Event___Action_1"".DocIDNo, ""7___Special___c___Import_Export___e___Fault___Event___Action_1"".Action, ""7___Special___c___Import_Export___e___Fault___Event___Action_1"".Action_AddI" _
            , _
            "nfo_ViewDspl, ""7___Special___c___Import_Export___e___Fault___Event___Action_1"".Cause, ""7___Special___c___Import_Export___e___Fault___Event___Action_1"".CreatingDate, ""7___Special___c___Import_Export___e___Fault___Event___Action_1""." _
            , _
            "CustDocRef, ""7___Special___c___Import_Export___e___Fault___Event___Action_1"".Customer  FROM ""7___Special___c___Import_Export___e___Fault___Event___Action"" ""7___Special___c___Import_Export___e___Fault___Event___Action_1""  WHERE (""7" _
            , _
            "___Special___c___Import_Export___e___Fault___Event___Action_1"".CreatingDate Between '2007-02-04 14:47:01' And '2007-03-05 07:14:37')  ORDER BY ""7___Special___c___Import_Export___e___Fault___Event___Action_1"".DocIDNo" _
            )
            .Name = "Query from premax_range"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .SourceConnectionFile = _
            "C:\Documents and Settings\cjohn\Desktop\Faults development\Query from premax_range.dqy"
            .refresh BackgroundQuery:=False
        End With
    End Sub
    Last edited by ByTheCringe2; April 2nd, 2007, 16:50.

  • #2
    Re: Macro Running Sql Query

    I have done some more testing. It does not seem to be the macro but rather if I save the file with the query parameters; save query definition selected.

    I do not know why excel can not cope with this. I am retrieving 1700 records but surely this is not a lot.

    Does any one know of a way round this like to be able to turn off the "save query definition" before save.

    Comment


    • #3
      Re: Macro Running Sql Query

      I have found a way of getting the save to work by deleting the query but there must be a better way of doing this as to refresh the data I will have to reload the query. If anyone has any better ideas please let me know.

      Code:
      Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
      Dim QT_count As Integer
      
      
      Sheet2.Select
      QT_count = ActiveSheet.QueryTables.count
          If QT_count > 0 Then
             ActiveSheet.QueryTables(QT_count).Delete
          
      End If
      
      
      
      End Sub
      Last edited by ByTheCringe2; April 2nd, 2007, 16:51.

      Comment


      • #4
        Re: Macro Running Sql Query

        Welcome to the forum. However please read the rules and use code tags for VBA. I have added them for you this time, but normally the posts would be deleted.

        Moving this to the SQL forum.
        .

        Comment


        • #5
          Re: Macro Running Sql Query

          Click Here For More Info

          Comment

          Working...
          X