Docmd runsql not updating
Run SQL "DELETE * FROM tbl QNImport" Call mod Excel. Worksheets(Work Sheet) Set obj Range = obj Worksheets. Path & "\" & Excel File Name, True, obj Worksheets. Run SQL str SQL str SQL = _ "INSERT INTO " & _ "tbl QNTasks ( QN, Dept, Tasked, Name, Task, [Est Comp], [Task Days Old], Age ) " & _ "SELECT " & _ "tbl QNImport. Another strange effect is that if the following code snippet is removed: Do Cmd. Path & "\" & Excel File Name Set obj Workbook = obj Excel. Open(str File Name) Set obj Worksheets = obj Workbook. Transfer Spreadsheet ac Import, ac Spreadsheet Type Excel9, Table Name, Current Project. Type , tbl QNImport.[QN Days Old] " & _ "FROM " & _ "tbl QNImport;" Do Cmd. Its almost like without stepping the code or allowing the update messages to appear, it seems to run "too fast" to allow tbl QN and tbl QHTasks to update. Close Set obj Access = Nothing Set obj Excel = Nothing Exit Sub Error Handler: Msg Box Err. But if I debug and step the code, (again, as is) everything works fine. An alternative is to rem out the Set Warnings, so all of the update messages appear, this also will successfully update all of the tables.Each query gathered the list of agnts for each of the 3 scenerios stated I used the following code to update the table: Private Sub Inactive Tags AEnd Date() Dim rs As Recordset Dim db As Database Dim str AGNTID As String Dim flds As DAO. Open Recordset("qry_Zero_A_Agnt_End Date Exist") Set flds = rs. Add NLSDesc = 'INACTIVE'" & _ "WHERE DTFImport Named. Set Warnings True End Sub Private Sub Inactive Tags DNo End Date() Dim rs As Recordset Dim db As Database Dim str AGNTID As String Dim flds As DAO. Open Recordset("qry_Zero_D_Agnt_End Date") Set flds = rs. Add NLSDesc = 'INACTIVE'" & _ "WHERE DTFImport Named. Set Warnings True End Sub Private Sub Inactive Tags DEnd Date() Dim rs As Recordset Dim db As Database Dim str AGNTID As String Dim flds As DAO. Open Recordset("qry_Zero_D_Agnt_End Date Exist") Set flds = rs. Add NLSDesc = 'INACTIVE'" & _ "WHERE DTFImport Named. Set Warnings True End Sub My issue is that there are 500,000 records that I am cycling thru with these sets of code. Hours on endand I have to do this for 24 month of historical data. Thanks Tuk Why are you looping through every record and doing the append?Append queries are intended to update multiple records in one go. If you have (updatable) queries that return the records you are interested in update them, the changes should be reflected in the main table.Run SQL "DELETE * FROM tbl QN" 'Delete all records before update str SQL = _ "INSERT INTO " & _ "tbl QN ( QN, Created, PGr, Buyer Name, Manager, Hull, PO, NNPN, Supplier, Engineer, Description, Type, [QN Days Old] ) " & _ "SELECT DISTINCT " & _ "tbl QNImport. Import Excel In Current Folder("tbl QNImport", "F4 Open QN Tasks (All Hulls).xls", "QNs by Hull") it works fine.
Import Excel In Current Folder("tbl QNImport", "F4 Open QN Tasks (All Hulls).xls", "QNs by Hull") 'Update tables Do Cmd.
Any chance you could give more details of the current tables? well i import a file("*.csv) called "Jun07" the goal is to import form Jun07 to Apr09. to condense the data i append to a second table tbl_Condensed_DTF_Named.
it contains: Status, Agnt End Date, Agnt Start Date, Agnt ID, Prod Code, Geo Loc, Type, Group, Region, CYMonth Volume (jun07), PYMonth Volume(Jun06), CYPrior Month Volume (May07). In this table i've added the following fields: Brand, Add NLSDesc, Desc Date, Vol Desc, Lost Date, New Date.
Dim msgbox Response Dim str SQL As String msgbox Response = Msg Box("Are you sure you want to update the database? Now understand, the Import Excel In Current Folder subroutine works with no problems. Work Sheet Dim str File Name As String Dim obj Range As Excel. Application") Set obj Access = Create Object("Access.
", vb Yes No, "Database Update") If msgbox Response = vb Yes Then Do Cmd. Set Warnings False 'Surpress the warning messages Do Cmd. Here's the code to it as well: Public Sub Import Excel In Current Folder(Table Name As String, Excel File Name As String, Work Sheet As String) Current Project. Properties("Jet OLEDB: Connection Control") = 2 'Avoid the database has been placed in a state problem On Error Go To Error Handler Dim obj Access As Object Dim obj Excel As Excel.
The groupings within the Add NLSDesc field are NEW, LOST, SAME, ZERO.