Macro Running Sql Query

[cjohn]

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:

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 

[cjohn]

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.

[cjohn]

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.

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 

[ByTheCringe2]

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.

[Craig Ottley]

Click Here For More Info