I utilise a vba program on an Excel workbook to transfer data to a temporay table in access.
The following is the code on the excel workbook. It is activated by a command button on the excel sheet. Data is transferred to a temporary file in an Access application, named "WorkitemsTemp".
Sub TransposeToAccess()
ActiveWorkbook.Save
Dim acc As New Access.Application
Range(Range("a1:c1"), Range("a1:c1").End(xlDown)).Select
ActiveWorkbook.Names.Add Name:="ghazla", RefersTo:=Selection
Dim filename As String
filename = Application.ActiveWorkbook.FullName
acc.OpenCurrentDatabase "C:\Payroll\JasminePayRoll_be.accdb"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "WorkItemsTemp", filename, True, "ghazla"
acc.CloseCurrentDatabase
acc.Quit
Set acc = Nothing
End Sub
A problem occurs sometimes, when this code does not manage to transfer the data to "WorkItemsTemp". When this occurs I realise that the reason why the code does not work is that table "WorkItemsTemp" is found to show 1 of 133456 records which are all empty. It appears to me to be saturated. What I do to circumvent this problem is that I copy the table's structure, delete the "saturated" WorkItemsTemp table and substitute it with the copied structure with the table of the same name. At this stage the records on the new table are 1/1.
To give one some more hints, data on WorkItemsTemp is intended to be transferred to another table called WorkItems. This is done by a command button on an access form on the application. The code that is attached to this button is the following.
Private Sub Command18_Click()
Dim ghadd As Integer
ghadd = DCount("*", "WorkItemstemp")
Dim LResponse As Integer
LResponse = MsgBox("You are about to append " & ghadd & " records from a temporary file. Would you like to continue ?", vbYesNo, ghadd)
If LResponse = vbYes Then
Dim StrSQL As String
StrSQL = "INSERT INTO WorkItems ( IDcardNo, Roster, meta )" _
& "SELECT WorkItemstemp.IDcardNo, WorkItemstemp.Roster, WorkItemstemp.meta " _
& " FROM WorkItemstemp;"
DoCmd.RunSQL StrSQL
CurrentDb.Execute "DELETE FROM WorkItemstemp", dbFailOnError
MsgBox ("You have successfully appended " & ghadd & " records to the roster!")
Else: LResponse = vbNo
LResponse = MsgBox(" Would you like to delete all records in the temporary file ?", vbYesNo, "to delete all records")
If LResponse = vbYes Then
CurrentDb.Execute "DELETE FROM WorkItemstemp", dbFailOnError
Else: LResponse = vbNo
Exit Sub
End If
End If
End Sub
I am presuming that the table "WorkItemsTemp" gets somehow saturated (which it should not, as there is no way I reach over 100000 records) and then the transfer from excel to the access table does not occur.
I wish someone can guide me as to how I can solve this obstacle. Many thanks for your help.
Louis Buhagiar.
The following is the code on the excel workbook. It is activated by a command button on the excel sheet. Data is transferred to a temporary file in an Access application, named "WorkitemsTemp".
Sub TransposeToAccess()
ActiveWorkbook.Save
Dim acc As New Access.Application
Range(Range("a1:c1"), Range("a1:c1").End(xlDown)).Select
ActiveWorkbook.Names.Add Name:="ghazla", RefersTo:=Selection
Dim filename As String
filename = Application.ActiveWorkbook.FullName
acc.OpenCurrentDatabase "C:\Payroll\JasminePayRoll_be.accdb"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "WorkItemsTemp", filename, True, "ghazla"
acc.CloseCurrentDatabase
acc.Quit
Set acc = Nothing
End Sub
A problem occurs sometimes, when this code does not manage to transfer the data to "WorkItemsTemp". When this occurs I realise that the reason why the code does not work is that table "WorkItemsTemp" is found to show 1 of 133456 records which are all empty. It appears to me to be saturated. What I do to circumvent this problem is that I copy the table's structure, delete the "saturated" WorkItemsTemp table and substitute it with the copied structure with the table of the same name. At this stage the records on the new table are 1/1.
To give one some more hints, data on WorkItemsTemp is intended to be transferred to another table called WorkItems. This is done by a command button on an access form on the application. The code that is attached to this button is the following.
Private Sub Command18_Click()
Dim ghadd As Integer
ghadd = DCount("*", "WorkItemstemp")
Dim LResponse As Integer
LResponse = MsgBox("You are about to append " & ghadd & " records from a temporary file. Would you like to continue ?", vbYesNo, ghadd)
If LResponse = vbYes Then
Dim StrSQL As String
StrSQL = "INSERT INTO WorkItems ( IDcardNo, Roster, meta )" _
& "SELECT WorkItemstemp.IDcardNo, WorkItemstemp.Roster, WorkItemstemp.meta " _
& " FROM WorkItemstemp;"
DoCmd.RunSQL StrSQL
CurrentDb.Execute "DELETE FROM WorkItemstemp", dbFailOnError
MsgBox ("You have successfully appended " & ghadd & " records to the roster!")
Else: LResponse = vbNo
LResponse = MsgBox(" Would you like to delete all records in the temporary file ?", vbYesNo, "to delete all records")
If LResponse = vbYes Then
CurrentDb.Execute "DELETE FROM WorkItemstemp", dbFailOnError
Else: LResponse = vbNo
Exit Sub
End If
End If
End Sub
I am presuming that the table "WorkItemsTemp" gets somehow saturated (which it should not, as there is no way I reach over 100000 records) and then the transfer from excel to the access table does not occur.
I wish someone can guide me as to how I can solve this obstacle. Many thanks for your help.
Louis Buhagiar.