I have a rather odd problem. I am importing an Excel into an Access database and then updating 2 tables from it's data. I'm doing it by using a series of DoCmd.RunSQL statements as shown below:
Now here's the weird thing: If I run this as is, it will update the tblQNImport, but will NOT update tblQN and tblQNTasks. But if I debug and step the code, (again, as is) everything works fine. All tables update.
An alternative is to rem out the SetWarnings, so all of the update messages appear, this also will successfully update all of the tables.
Its almost like without stepping the code or allowing the update messages to appear, it seems to run "too fast" to allow tblQN and tblQHTasks to update.
Another strange effect is that if the following code snippet is removed:
DoCmd.RunSQL "DELETE * FROM tblQNImport"
Call modExcel.ImportExcelInCurrentFolder("tblQNImport", "F4 Open QN Tasks (All Hulls).xls", "QNs by Hull")
it works fine.
Now understand, the ImportExcelInCurrentFolder subroutine works with no problems.
Here's the code to it as well:
So why is this happening?
Code:
Dim msgboxResponse
Dim strSQL As String
msgboxResponse = MsgBox("Are you sure you want to update the database?", vbYesNo, "Database Update")
If msgboxResponse = vbYes Then
DoCmd.Hourglass True
DoCmd.SetWarnings False 'Surpress the warning messages
DoCmd.RunSQL "DELETE * FROM tblQNImport"
'This is a subroutine that imports an Excel worksheet into the database.
'The Excel file must be in the same folder as the database.
'ImportExcelInCurrentFolder(table name, Excel filename, Worksheet name)
Call modExcel.ImportExcelInCurrentFolder("tblQNImport", "F4 Open QN Tasks (All Hulls).xls", "QNs by Hull")
'Update tables
DoCmd.RunSQL "DELETE * FROM tblQN" 'Delete all records before update
strSQL = _
"INSERT INTO " & _
"tblQN ( QN, Created, PGr, BuyerName, Manager, Hull, PO, NNPN, Supplier, Engineer, Description, Type, [QN Days Old] ) " & _
"SELECT DISTINCT " & _
"tblQNImport.QN, tblQNImport.Created, tblQNImport.PGr, tblQNImport.BuyerName, tblQNImport.Manager, tblQNImport.Hull, " & _
"tblQNImport.PO, tblQNImport.Material, tblQNImport.Supplier, tblQNImport.Engineer, tblQNImport.Description, " & _
"tblQNImport.Type , tblQNImport.[QN Days Old] " & _
"FROM " & _
"tblQNImport;"
DoCmd.RunSQL strSQL
strSQL = _
"INSERT INTO " & _
"tblQNTasks ( QN, Dept, Tasked, Name, Task, [Est Comp], [Task Days Old], Age ) " & _
"SELECT " & _
"tblQNImport.QN, tblQNImport.Dept, tblQNImport.Tasked, tblQNImport.Name, tblQNImport.Task, tblQNImport.[EST COMP], " & _
"tblQNImport.[Task Days Old] , tblQNImport.Age " & _
"FROM " & _
"tblQNImport;"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True 'Turn the warning messages back on
DoCmd.Hourglass False
MsgBox "The database is now updated", vbOKOnly, "Database Updated"
End IfNow here's the weird thing: If I run this as is, it will update the tblQNImport, but will NOT update tblQN and tblQNTasks. But if I debug and step the code, (again, as is) everything works fine. All tables update.
An alternative is to rem out the SetWarnings, so all of the update messages appear, this also will successfully update all of the tables.
Its almost like without stepping the code or allowing the update messages to appear, it seems to run "too fast" to allow tblQN and tblQHTasks to update.
Another strange effect is that if the following code snippet is removed:
DoCmd.RunSQL "DELETE * FROM tblQNImport"
Call modExcel.ImportExcelInCurrentFolder("tblQNImport", "F4 Open QN Tasks (All Hulls).xls", "QNs by Hull")
it works fine.
Now understand, the ImportExcelInCurrentFolder subroutine works with no problems.
Here's the code to it as well:
Code:
Public Sub ImportExcelInCurrentFolder(TableName As String, ExcelFileName As String, WorkSheet As String)
CurrentProject.Connection.Properties("Jet OLEDB:Connection Control") = 2 'Avoid the database has been placed in a state problem
On Error GoTo ErrorHandler
Dim objAccess As Object
Dim objExcel As Excel.Application
Dim objWorkbook As Excel.Workbook
Dim objWorksheets As Excel.WorkSheet
Dim strFileName As String
Dim objRange As Excel.Range
Set objExcel = CreateObject("Excel.Application")
Set objAccess = CreateObject("Access.Application")
objAccess.OpenCurrentDatabase CurrentProject.Path & "\" & CurrentProject.Name
strFileName = CurrentProject.Path & "\" & ExcelFileName
Set objWorkbook = objExcel.Workbooks.Open(strFileName)
Set objWorksheets = objWorkbook.Worksheets(WorkSheet)
Set objRange = objWorksheets.UsedRange
objAccess.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, TableName, CurrentProject.Path & "\" & ExcelFileName, True, objWorksheets.Name & "!" & objRange.Address(False, False)
objWorkbook.Close
Set objAccess = Nothing
Set objExcel = Nothing
Exit Sub
ErrorHandler:
MsgBox Err.Description, vbOKOnly, "Error"
End SubSo why is this happening?
Last edited:
