Thanks for your reply Holmes. But dont know how to compact and repair without exiting the application in VBA. Normally the compact and repair is used while exiting the application . How to apply it for a running code?

rs1.Open "SELECT Count(1)FROM (SELECT DISTINCT mt1.TestCaseName, mt1.TestScriptName, " & _ "mt1.FinalStatus FROM (Metrics_TestCases " & _ "AS mt1 INNER JOIN MaxScriptQuery as mt2 ON( mt1.TestCaseName = mt2.TestCaseName And " & _ mt1.TestScriptName = mt2.TestScriptName And mt1.ReleaseVersion = mt2.ReleaseVersion and " & _ "mt1.TestScriptID = mt2.maxTestScriptID) ) INNER JOIN Release_chart as tr ON mt1.ReleaseVersion = tr.ReleaseVersion) " & _ " ", oConnection, adOpenStatic, adLockReadOnly This is the query i used to get some data from access table. MaxScriptQuery is a inner query i used to join . MaxScriptQuery - SELECT DISTINCT TestCaseName, TestScriptName, ReleaseVersion, MAX(TestScriptID) AS MaxTestScriptID FROM Metrics_TestCases GROUP BY TestCaseName, TestScriptName, ReleaseVersion; This query worked fine some week before. But to my surprise now the same query with same set of data is taking too much time. I dont know why the query is suddenly slowed up. CPU usage is also very high while running this query

Hello friends, I having two table, one table havin only one fields. eg : Table A release rel1 rel2 rel3 I want to select datas from other table. table B release case ID rel1 a 1 rel1 b 1 rel2 a 2 rel3 c 3 I want to select the cases and ID from table B where release is all the datas present in TAble A. I can build the query to get case and ID. Actually i need the query for another purpose. I used below query SELECT cases, ID FROM TAble B WHERE release IN(Select release from TAble B) this query is very slow for my purpose The query should link both the table Please help me to opimise the query Regards Jishith

Hi vica dianto, This Query also takng much time.. but much faster than what i posted. I want to insert these result in an access table also. Is the insertion creating the touble or the query itself? Hi abcurl, "Status = 'PASS'".. Status can be fail or pass, but if two similar Cases having different status I want the lastest status, ie Case having Laresgt ID. One more thing i want the result to be inserted in an access table also Thanks alot for the replies Jishith

Hello friends, I need a optimized query for below condition CaseName ScriptName ID Status Version --------------------------- TC1 TS1 1 PASS R1 TC1 TS1 2 FAIL R1 TC1 TS1 3 PASS R1 TC2 TS1 1 PASS R1 TC2 TS1 1 PASS R1 TC3 TS1 1 PASS R1 TC1 TS2 4 PASS R2 TC1 TS3 5 PASS R2 Want to get distinct casename, ScriptName,status,Max(ID),version Result I need is TC1 TS1 3 PASS R1 TC2 TS1 1 PASS R1 TC3 TS1 1 PASS R1 TC1 TS2 4 PASS R2 TC1 TS3 5 PASS R2 When i tried using the below queries, both query taking hell lot of time(seems it may stuck). Table contains atleast 7 lakhs(700 thousands) of records. SELECT DISTINCT CaseName,ScriptName,ID,Status,Version FROM Metrics AS t1 WHERE ID = (SELECT MAX(ID) FROM Metrics WHERE CaseName = t1.CaseName AND ScriptName = t1.ScriptName AND Version = t1.Version)" SELECT CaseName,ScriptName,ID,Status,Version FROM Metrics WHERE ID IN (SELECT MAX(ID) from Metrics GROUP BY CaseName,ScriptName,Version)" Thanks in Advance Jishith

Hello friends, I want to copy a file from one folder to another. I am using File object.copy to copy the file from one folder to another. How can i make sure that the copy is success or not? Thanks and regards Jishith

Hello Friends, I am in trouble while loading data to SQL Server. i am using bulkcopy code to load datas. I dont have much knowledge with SQL While loading, if error occurs (in xtreme cases) loading wont be perfect(ie I having four tables to be loaded. error may occur while loading the fourth table. for me its like corrupting the tables. because for tables are interconnected) So if error occurs i want to roll back other three tables to last state. The below code is i used for loading and these function is calling four times for updating four tables Can Rollback property be applied to the below code?? Please do help Or any other way to prevent the tables from getting corrupted ------------------------------------------ Public Function rb_BCP(ByVal Server As String, ByVal Database As String, _ ByVal Table As String, ByVal FileName As String) Dim objServer As SQLDMO.SQLServer Dim objBCP As SQLDMO.BulkCopy Dim objDB As SQLDMO.Database On Error GoTo ErrorHandler Set objServer = New SQLDMO.SQLServer Set objBCP = New SQLDMO.BulkCopy objServer.LoginSecure = True objServer.EnableBcp = True objServer.QuotedIdentifier = True objServer.Connect (Server) Set objDB = objServer.Databases(Database) With objBCP .UseExistingConnection = True .DataFilePath = FileName .RowDelimiter = vbCrLf .IncludeIdentityValues = True .ServerBCPKeepNulls = True .DataFileType = SQLDMODataFile_SpecialDelimitedChar .ColumnDelimiter = "|" .IncludeIdentityValues = True .ImportRowsPerBatch = 1000000 'Below speeds things up 'but does not log the bulk copy operation 'comment out if this is not what you .UseBulkCopyOption = True End With objDB.Tables(Table).ImportData objBCP Set objBCP = Nothing objServer.Disconnect Set objServer = Nothing Exit Function ErrorHandler: MsgBox "Error " & Err.Number & vbCrLf & Err.Description End Function jishith

Hello Friends, I am in trouble while loading data to SQL Server. i am using bulkcopy code to load datas. I dont have much knowledge with SQL If error occurs, the loading may not complete(ie i having four tables to be loaded. error may occur while loading the fourth table. for me its like corrupting the table So if error occurs i want to roll back to last state. The code i used is With these code can i use rollback property ?? Please do help Public Function rb_BCP(ByVal Server As String, ByVal Database As String, _ ByVal Table As String, ByVal FileName As String) Dim objServer As SQLDMO.SQLServer Dim objBCP As SQLDMO.BulkCopy Dim objDB As SQLDMO.Database On Error GoTo ErrorHandler Set objServer = New SQLDMO.SQLServer Set objBCP = New SQLDMO.BulkCopy objServer.LoginSecure = True objServer.EnableBcp = True objServer.QuotedIdentifier = True objServer.Connect (Server) Set objDB = objServer.Databases(Database) With objBCP .UseExistingConnection = True .DataFilePath = FileName .RowDelimiter = vbCrLf .IncludeIdentityValues = True .ServerBCPKeepNulls = True .DataFileType = SQLDMODataFile_SpecialDelimitedChar .ColumnDelimiter = "|" .IncludeIdentityValues = True .ImportRowsPerBatch = 1000000 'Below speeds things up 'but does not log the bulk copy operation 'comment out if this is not what you .UseBulkCopyOption = True End With objDB.Tables(Table).ImportData objBCP Set objBCP = Nothing objServer.Disconnect Set objServer = Nothing Exit Function ErrorHandler: MsgBox "Error " & Err.Number & vbCrLf & Err.Description End Function jishith

Hello , I am loading some data to SQL server using my MS Access application. Sometimes loading may hangup with some errors. So partial loading may happens. So what i thinking is that if any error occurs, I way to get restore bac the server to old state(means before loading state) . So i can try loading data again How can i do that

The code works always fine with single steppings. When at full running , the problem occurs.. If i putting any break point inside the code, 80% times run properly... And i am not using On Error Resume Next in this function.

Hello friends, I am struck with a bug in my code and find it difficult to debug. In my MS access application, I am creating some reports using SQL queries to fetch data from Access Table and SQL table. Some time the code works fine, creating the report fine. Some time its not creating the report fully, skipping some queries and conditions. If I try to run the code using 'Step in', the report always creating as expected. if it run otherwise report fails. I tried using with delays, then its working fine. But I have to use min 5 sec delay for that. So its effecting the performance. I dont have much experience in VBA and SQL queries. IS it due to some error in my code or something else? Thanks and regards Jishith

Ya . Its in MS Access. In properties CmbFromDate.RowSource type = Table/Query CmbFromDate.RowSource = "Query Created On" The query is getting for the dates from Access Table The combo box i used for the user to select the date. After quitting the application the .mdb file showing huge size . The previous query data is still in the combo Box

ComboBox1.Datasource is not showing in my access. In ComboBox1 properties, rowsource type is Query/table and row source is my query. its not value list

Hello friends, In my MS Access, one of my form having a combobox whose row source is a query data. How can i delete all items from comboBox Thanks and regards Jishith

Thanks for the reply.

Hello, i am creating my report table in word doc using VBA. When my application hangs and trying to open the word doc again, it says, document is locked by user or some time popup comes asking want to open as ready only. How can i close the word doc before opening it again. Thanks and regards Jishith

Thanks eddy, i resolved the error...

Hello all, Can any body helps me to find a code for getting files from subfolders or files inside that subfolder. i know code to get files from folders and subfolders. But if many folders are there, my code fails i.e. folder/subfolder/subfolder/... i am not sure how many subfolders are there inside a folder i need the code for getting file from folder/subfolder/subfolder/subfolder.. i am using these code for getting files inside subfolder For Each fld In path.SubFolders For Each fil In fso.GetFolder(fld.path).Files If Right(fil, 3) = "txt" Then fileNameArray1(fileCounts) = fil fileCounts = fileCounts+1 End If Next Next please try give the code for getting files inside folder/subfolder/subfolder/subfolder/.. Thanks and regards Jishith

Hi, Thanks for replying. but.. 1) Checking the file folder size with 0 is not correct, here the folder is empty 2) I manually creating the folder, so no right issues 3) I can delete that folder, whn i am running my application for the next time. Only the first instance its showing permission denied. First i thought some oject is not releasing. But i making all the object i used in the function to nothing. Still it showing permission denied. If there are four folders, its deleting three folders and when its come to the last one, the permission is denied. How i make sure the folder is not in use? Or how to release the in use folder? Jishith

I cant able to delete a subfolder inside a folder. What i going is, opening a file from subfolder, reading it and closing the file and copying it to other folder and then deleting that file. Same i doing for all files in that subfolder. then checking that subfolder size if its size is zero, deleting it.. this is the code i used to delete the folder path = "c:\newfolder" For Each fld In path.SubFolders If fld.Size = 0 Then fld.Delete : here i am getting permission denied, error 70 End If Next fld I am closing all the FileSystemObjects. jishith