Access temporary table saturates inexplicably and malfunctions. (1 Viewer)

buhal004

New member
Local time
Today, 22:21
Joined
Mar 15, 2020
Messages
18
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.
 

HiTechCoach

Well-known member
Local time
Today, 16:21
Joined
Mar 6, 2006
Messages
4,357
I see this happen when the Excel file is not properly cleared.

I would first try adding a WHERE clause to the SQL to filter out blank rows.
 

buhal004

New member
Local time
Today, 22:21
Joined
Mar 15, 2020
Messages
18
I see this happen when the Excel file is not properly cleared.

I would first try adding a WHERE clause to the SQL to filter out blank rows.


The excel file has a procedure to clear blank rows before the transfer. This is carried out each time. Even so I cannot understand how the access table saturates with blank records.
 

buhal004

New member
Local time
Today, 22:21
Joined
Mar 15, 2020
Messages
18
The excel file has a procedure to clear blank rows before the transfer. This is carried out each time. Even so I cannot understand how the access table saturates with blank records.

Could it be something with the part of the code “CurrentDB.execute” ?
 

isladogs

MVP / VIP
Local time
Today, 21:21
Joined
Jan 14, 2017
Messages
18,186
Instead of exporting your data from Excel, I suggest you link to the Excel file from Access then import the non blank rows to your temporary table.
That should give you complete control over the transfer.
 

HiTechCoach

Well-known member
Local time
Today, 16:21
Joined
Mar 6, 2006
Messages
4,357
Have you try simply adding a WHERE clause to the SQL to filter out blank rows.

WHERE WorkItemstemp.IDcardNo Is Not Null

Like this in Command18_Click()

Code:
StrSQL = "INSERT INTO WorkItems ( IDcardNo, Roster, meta )" _
& "SELECT WorkItemstemp.IDcardNo, WorkItemstemp.Roster, WorkItemstemp.meta " _
& " FROM WorkItemstemp WHERE WorkItemstemp.IDcardNo Is Not Null;"
 

buhal004

New member
Local time
Today, 22:21
Joined
Mar 15, 2020
Messages
18
Instead of exporting your data from Excel, I suggest you link to the Excel file from Access then import the non blank rows to your temporary table.
That should give you complete control over the transfer.

How do you link from access to excel ? Tks.
 

isladogs

MVP / VIP
Local time
Today, 21:21
Joined
Jan 14, 2017
Messages
18,186
How do you link from access to excel ? Tks.
It depends on your version but something like this : External Data...New Data Source...From File...Excel.
A wizard window appears.
Browse for your Excel file and select the third option....Link to the data source by creating a linked table.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 21:21
Joined
Feb 19, 2013
Messages
16,553
The excel file has a procedure to clear blank rows before the transfer. This is carried out each time.
suggest put that code in your transposetoaccess function

not tested but another option might be to set one of the fields in your destination table to required. Expectation being that records not populated will be ignored.
 

Users who are viewing this thread

Top Bottom